from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.orm import Session
from sqlalchemy import text, inspect
from ..database import get_db
from ..dependencies import get_current_user
from ..models import User

router = APIRouter()

# Tables that can be cleared by SUPER_ADMIN
CLEARABLE = {
    'daily_shifts', 'pump_readings', 'cash_denominations',
    'shift_collection_cycles', 'cash_handovers', 'shift_other_collections',
    'tank_dip_readings', 'allowances', 'leave_requests', 'leave_balances',
    'audit_log', 'invoices', 'invoice_line_items', 'bulk_sales',
    'credit_sales', 'credit_payments', 'credit_customers', 'customer_vehicles',
    'stock_deliveries', 'stock_adjustments', 'expenses', 'notifications',
    'notification_preferences', 'shift_handover_notes', 'transaction_logs',
}

# Tables that are protected (config / auth data)
PROTECTED = {
    'users', 'system_settings', 'pumps', 'fuel_rates',
    'user_permissions', 'role_page_permissions', 'staff',
}

TABLE_LABELS = {
    'daily_shifts':             ('Shifts',              'Daily shift records'),
    'pump_readings':            ('Pump Readings',       'Meter readings per pump per shift'),
    'cash_denominations':       ('Cash Denominations',  'Cash bag denomination breakdowns'),
    'shift_collection_cycles':  ('Collection Cycles',   'Cash collection cycles per shift'),
    'cash_handovers':           ('Cash Handovers',      'Cash handover records'),
    'shift_other_collections':  ('Other Collections',   'Card / credit / other collections'),
    'tank_dip_readings':        ('Tank Dip Readings',   'Daily tank dip measurements'),
    'allowances':               ('Allowances',          'Staff allowance records'),
    'leave_requests':           ('Leave Requests',      'Staff leave applications'),
    'leave_balances':           ('Leave Balances',      'Annual leave balance ledger'),
    'audit_log':                ('Audit Log',           'User action audit trail'),
    'invoices':                 ('Invoices',            'Billing invoices'),
    'invoice_line_items':       ('Invoice Line Items',  'Invoice line item details'),
    'bulk_sales':               ('Bulk Sales',          'Bulk fuel sales records'),
    'credit_sales':             ('Credit Sales',        'Credit sale transactions'),
    'credit_payments':          ('Credit Payments',     'Payments against credit accounts'),
    'credit_customers':         ('Credit Customers',    'Credit customer accounts'),
    'customer_vehicles':        ('Customer Vehicles',   'Vehicles linked to customers'),
    'stock_deliveries':         ('Stock Deliveries',    'Fuel delivery records'),
    'stock_adjustments':        ('Stock Adjustments',   'Manual stock adjustment entries'),
    'expenses':                 ('Expenses',            'Station expense records'),
    'notifications':            ('Notifications',       'System notification messages'),
    'notification_preferences': ('Notif. Preferences',  'User notification settings'),
    'shift_handover_notes':     ('Handover Notes',      'Shift handover notes'),
    'transaction_logs':         ('Transaction Logs',    'API request logs'),
    # Protected
    'users':                    ('Users',               'System user accounts'),
    'staff':                    ('Staff',               'Staff member profiles'),
    'pumps':                    ('Pumps',               'Pump configuration'),
    'fuel_rates':               ('Fuel Rates',          'Fuel price history'),
    'system_settings':          ('Settings',            'System configuration'),
    'user_permissions':         ('User Permissions',    'Per-user module permissions'),
    'role_page_permissions':    ('Role Permissions',    'Role-level page access'),
}


def _require_super_admin(user: User):
    if user.role not in ('SUPER_ADMIN', 'OWNER'):
        raise HTTPException(403, "SUPER_ADMIN or OWNER only")


@router.get("/database/tables")
def list_tables(db: Session = Depends(get_db), user=Depends(get_current_user)):
    _require_super_admin(user)
    result = []
    all_tables = sorted(CLEARABLE | PROTECTED)
    for tbl in all_tables:
        try:
            row = db.execute(text(f"SELECT COUNT(*) FROM `{tbl}`")).scalar()
            count = int(row)
        except Exception:
            count = -1
        label, desc = TABLE_LABELS.get(tbl, (tbl, ''))
        result.append({
            "table":      tbl,
            "label":      label,
            "description": desc,
            "row_count":  count,
            "clearable":  tbl in CLEARABLE,
            "protected":  tbl in PROTECTED,
        })
    return result


@router.delete("/database/tables/{table_name}", status_code=200)
def clear_table(
    table_name: str,
    db: Session = Depends(get_db),
    user=Depends(get_current_user),
):
    _require_super_admin(user)
    if table_name not in CLEARABLE:
        raise HTTPException(400, f"Table '{table_name}' is protected and cannot be cleared")
    try:
        db.execute(text("SET FOREIGN_KEY_CHECKS=0"))
        db.execute(text(f"TRUNCATE TABLE `{table_name}`"))
        db.execute(text("SET FOREIGN_KEY_CHECKS=1"))
        db.commit()
        return {"table": table_name, "cleared": True}
    except Exception as e:
        db.rollback()
        raise HTTPException(500, f"Failed to clear table: {e}")


@router.post("/db/run-expand-migration")
def run_expand_migration(
    db: Session = Depends(get_db),
    user=Depends(get_current_user),
):
    """SUPER_ADMIN or OWNER: create missing tables from the expand_system migration."""
    if user.role not in ('SUPER_ADMIN', 'OWNER'):
        raise HTTPException(403, "SUPER_ADMIN or OWNER only")
    statements = [
        """CREATE TABLE IF NOT EXISTS invoices (
          id             INT AUTO_INCREMENT PRIMARY KEY,
          invoice_no     VARCHAR(30) UNIQUE NOT NULL,
          invoice_type   ENUM('CASH_RECEIPT','CREDIT_INVOICE','VAT_INVOICE') NOT NULL,
          customer_id    INT,
          shift_id       INT,
          invoice_date   DATE NOT NULL,
          due_date       DATE,
          subtotal       DECIMAL(12,2) NOT NULL DEFAULT 0,
          vat_rate       DECIMAL(5,2)  NOT NULL DEFAULT 0,
          vat_amount     DECIMAL(12,2) NOT NULL DEFAULT 0,
          total          DECIMAL(12,2) NOT NULL DEFAULT 0,
          status         ENUM('DRAFT','ISSUED','PAID','CANCELLED') NOT NULL DEFAULT 'DRAFT',
          payment_method ENUM('CASH','BANK_TRANSFER','CHEQUE','CREDIT') DEFAULT 'CASH',
          customer_vat_no VARCHAR(50),
          notes          TEXT,
          created_by     INT,
          created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at     DATETIME ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (customer_id) REFERENCES credit_customers(id) ON DELETE SET NULL,
          FOREIGN KEY (shift_id)    REFERENCES daily_shifts(id)     ON DELETE SET NULL,
          FOREIGN KEY (created_by)  REFERENCES users(id)            ON DELETE SET NULL
        )""",
        """CREATE TABLE IF NOT EXISTS invoice_line_items (
          id          INT AUTO_INCREMENT PRIMARY KEY,
          invoice_id  INT NOT NULL,
          fuel_type   ENUM('LP92','EURO3','LAD','LADXM'),
          description VARCHAR(200) NOT NULL,
          quantity    DECIMAL(10,3) NOT NULL DEFAULT 0,
          unit_rate   DECIMAL(8,2)  NOT NULL DEFAULT 0,
          amount      DECIMAL(12,2) NOT NULL DEFAULT 0,
          FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
        )""",
        """CREATE TABLE IF NOT EXISTS bulk_sales (
          id               INT AUTO_INCREMENT PRIMARY KEY,
          sale_date        DATE NOT NULL,
          fuel_type        ENUM('LP92','EURO3','LAD','LADXM') NOT NULL,
          liters           DECIMAL(10,2) NOT NULL,
          custom_rate      DECIMAL(8,2)  NOT NULL,
          standard_rate    DECIMAL(8,2)  NOT NULL,
          amount           DECIMAL(12,2) NOT NULL,
          rate_variance_pct DECIMAL(5,2),
          customer_id      INT,
          vehicle_no       VARCHAR(20),
          shift_id         INT,
          notes            TEXT,
          requires_approval BOOLEAN DEFAULT FALSE,
          approved_by      INT,
          approved_at      DATETIME,
          created_by       INT,
          created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (customer_id) REFERENCES credit_customers(id) ON DELETE SET NULL,
          FOREIGN KEY (shift_id)    REFERENCES daily_shifts(id)     ON DELETE SET NULL,
          FOREIGN KEY (approved_by) REFERENCES users(id)            ON DELETE SET NULL,
          FOREIGN KEY (created_by)  REFERENCES users(id)            ON DELETE SET NULL
        )""",
        """CREATE TABLE IF NOT EXISTS notifications (
          id           INT AUTO_INCREMENT PRIMARY KEY,
          user_id      INT NOT NULL,
          type         VARCHAR(50) NOT NULL,
          title        VARCHAR(200) NOT NULL,
          message      TEXT,
          is_read      BOOLEAN DEFAULT FALSE,
          related_id   INT,
          related_type VARCHAR(50),
          created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )""",
        """CREATE TABLE IF NOT EXISTS expenses (
          id           INT AUTO_INCREMENT PRIMARY KEY,
          expense_date DATE NOT NULL,
          category     VARCHAR(100) NOT NULL,
          description  TEXT,
          amount       DECIMAL(10,2) NOT NULL,
          shift_id     INT,
          staff_id     INT,
          receipt_no   VARCHAR(50),
          approved_by  INT,
          created_by   INT,
          created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (shift_id)    REFERENCES daily_shifts(id) ON DELETE SET NULL,
          FOREIGN KEY (staff_id)    REFERENCES staff(id)        ON DELETE SET NULL,
          FOREIGN KEY (approved_by) REFERENCES users(id)        ON DELETE SET NULL,
          FOREIGN KEY (created_by)  REFERENCES users(id)        ON DELETE SET NULL
        )""",
        """INSERT IGNORE INTO system_settings (setting_key, setting_value, description)
           VALUES
           ('invoice_prefix',   'INV',  'Prefix for invoice numbers'),
           ('invoice_next_seq', '1',    'Next invoice sequence number')""",
    ]

    # Phase-2 column additions (safe to re-run — errors caught per statement)
    phase2 = [
        "ALTER TABLE cash_handovers ADD COLUMN card_visa  DECIMAL(12,2) NOT NULL DEFAULT 0",
        "ALTER TABLE cash_handovers ADD COLUMN card_amex  DECIMAL(12,2) NOT NULL DEFAULT 0",
        "ALTER TABLE cash_handovers ADD COLUMN card_touch DECIMAL(12,2) NOT NULL DEFAULT 0",
        "ALTER TABLE credit_sales   ADD COLUMN pumper_id  INT",
        "ALTER TABLE credit_sales   ADD CONSTRAINT fk_cs_pumper FOREIGN KEY (pumper_id) REFERENCES staff(id) ON DELETE SET NULL",
        # Backfill: move old card_total into card_visa for rows that were created before the split
        "UPDATE cash_handovers SET card_visa = card_total WHERE card_visa = 0 AND card_total > 0",
    ]
    statements = statements + phase2
    results = []
    for stmt in statements:
        try:
            db.execute(text(stmt))
            results.append("OK")
        except Exception as e:
            results.append(f"ERR: {e}")
    db.commit()
    return {"results": results}