"""
Reset database for fresh setup wizard testing.

KEEPS (global system data):
  - accounting_codes        (IFRS chart of accounts)
  - roles                   (system roles)
  - permissions             (system permissions)
  - role_permissions        (role-permission links)
  - unit_of_measures        (measurement units)
  - item_type_definitions   (product type config)

CLEARS (all company/tenant data):
  - companies, branches, users
  - All business transactions (sales, purchases, invoices, etc.)
  - All financial data (journal entries, bank accounts, etc.)
  - All operational data (products, customers, suppliers, etc.)

After reset: /api/v1/companies/check-setup returns {"setup_required": true}
"""

import sys
import os
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from app.core.database import engine
from sqlalchemy import text


# Tables to KEEP (system-level, not company-specific)
KEEP_TABLES = {
    'accounting_codes',
    'accounting_code_dimension_requirements',
    'accounting_code_dimension_template_items',
    'accounting_code_dimension_templates',
    'accounting_dimensions',
    'accounting_dimension_values',
    'roles',
    'permissions',
    'role_permissions',
    'unit_of_measures',
    'item_type_definitions',
}

# Order matters for FK constraints — most dependent first
CLEAR_ORDER = [
    # Audit / logs first (no FK deps to business data)
    'user_audit_logs', 'user_sessions', 'token_blacklist',
    'activity_logs', 'entity_access_logs', 'permission_change_logs',
    'notification_users', 'notifications',
    'data_integrity_issues', 'system_errors', 'system_fixes', 'system_health_checks',
    'approval_logs',

    # Journal / accounting (depend on branches, companies)
    'journal_sale_audit', 'journal_transactions', 'journal_entries',
    'accounting_entries', 'accounting_code_instances',
    'opening_balances', 'ledgers',
    'vat_reconciliation_items', 'vat_payments', 'vat_reconciliations',

    # Financial reports
    'financial_reports', 'inventory_reports', 'sales_reports',
    'operational_reports', 'reports', 'report_templates',
    'report_schedules', 'report_exports',
    'period_close_checklists', 'period_end_statements',
    'fiscal_periods', 'fiscal_years',
    'performance_metrics',

    # Banking
    'reconciliation_items', 'bank_reconciliations',
    'bank_transfer_allocations', 'bank_transfers',
    'bank_transactions', 'bank_accounts',
    'beneficiaries',

    # POS & Cash
    'pos_shift_reconciliations', 'pos_sessions',
    'cash_submissions', 'float_allocations',

    # Sales & Invoices
    'invoice_item_serials', 'invoice_items', 'invoices',
    'payments', 'receipts', 'recurring_invoices', 'recurring_payments',
    'sale_items', 'sales',
    'credit_note_items', 'credit_notes', 'refund_transactions',
    'quotation_items', 'quotations',

    # Purchases & Procurement
    'purchase_payments', 'purchase_items', 'purchases',
    'purchase_order_items', 'purchase_orders',
    'supplier_quote_items', 'supplier_quotes',
    'rfq_invites', 'rfqs',
    'procurement_awards',
    'procurement_requisition_items', 'procurement_requisitions',
    'supplier_evaluation_milestones', 'supplier_evaluation_tickets',
    'supplier_performance',
    'landed_cost_items', 'landed_costs',

    # Inventory & Products
    'serial_numbers',
    'product_traceability_log', 'product_batch_registry',
    'invoice_item_serials',
    'inventory_allocation_movements', 'inventory_allocation_requests',
    'branch_inventory_allocations', 'branch_stock_snapshots',
    'headquarters_inventory',
    'inventory_transactions', 'inventory_adjustments',
    'cogs_entries', 'cogs_allocations',
    'product_supplier_catalog',
    'product_assemblies',
    'products',

    # Manufacturing
    'production_quality_checks', 'production_order_status_history',
    'production_overhead_entries', 'production_labor_entries',
    'production_material_consumptions', 'production_orders',
    'manufacturing_variance', 'manufacturing_cost_standards',
    'overhead_cost_entries', 'labor_cost_entries', 'material_cost_entries',
    'manufacturing_costs',

    # Job Cards
    'job_card_notes', 'job_card_labors', 'job_card_materials', 'job_cards',

    # Customers, Suppliers
    'customers', 'suppliers',

    # Assets
    'asset_images', 'asset_depreciation', 'asset_maintenance', 'assets',
    'asset_category_configs',

    # Budgeting
    'budget_requests', 'budget_transactions', 'budget_user_access',
    'budget_allocations', 'budgets',

    # Billing
    'billable_items', 'billing_cycles',

    # Workflows
    'workflow_notifications', 'workflow_actions', 'workflow_instances',
    'workflow_transitions', 'workflow_states', 'workflow_definitions',

    # Dimensions (company-specific values)
    'accounting_dimension_assignments',

    # Misc
    'import_jobs', 'excel_templates', 'backups', 'backup_schedules',
    'app_settings',
    'dimension_templates',

    # Users & permissions (company-scoped)
    'user_permissions',
    'users',

    # Licensing (CNP purchase tokens & on-prem nodes)
    'deployment_nodes',
    'license_keys',

    # Core tenant tables (last)
    'branches',
    'companies',
]


def reset(dry_run: bool = False):
    cleared = 0
    skipped = 0
    errors = []

    # ── First: delete company-specific accounting codes (branch_id IS NOT NULL)
    # These must go before branches are deleted to avoid FK cascade conflicts.
    try:
        with engine.connect() as conn:
            exists = conn.execute(text(
                "SELECT 1 FROM information_schema.tables WHERE table_name='accounting_codes'"
            )).first()
            if exists:
                if not dry_run:
                    result = conn.execute(text(
                        "DELETE FROM accounting_codes WHERE branch_id IS NOT NULL"
                    ))
                    conn.commit()
                print(f'  {"DRY   " if dry_run else "CLEAR "} accounting_codes (company-specific, branch_id IS NOT NULL)')
    except Exception as e:
        print(f'  ERROR  accounting_codes company-specific: {str(e).split(chr(10))[0][:80]}')

    for table in CLEAR_ORDER:
        if table in KEEP_TABLES:
            print(f'  KEEP   {table}')
            skipped += 1
            continue
        try:
            with engine.connect() as conn:
                # Check table exists
                exists = conn.execute(text(
                    "SELECT 1 FROM information_schema.tables "
                    "WHERE table_name = :t AND table_schema = 'public'"
                ), {"t": table}).first()
                if not exists:
                    print(f'  SKIP   {table}  (table not found)')
                    continue
                if not dry_run:
                    conn.execute(text(f'DELETE FROM "{table}"'))
                    conn.commit()
            print(f'  {"DRY   " if dry_run else "CLEAR "} {table}')
            cleared += 1
        except Exception as e:
            err = str(e).split('\n')[0][:80]
            print(f'  ERROR  {table}  ({err})')
            errors.append(table)

    print()
    print(f'{"[DRY RUN] " if dry_run else ""}Cleared: {cleared}  |  Kept: {skipped}  |  Errors: {len(errors)}')
    if errors:
        print(f'Tables with errors (non-fatal): {errors}')

    # Verify
    with engine.connect() as conn:
        co_count = conn.execute(text("SELECT COUNT(*) FROM companies")).scalar()
        user_count = conn.execute(text("SELECT COUNT(*) FROM users")).scalar()
        ac_count = conn.execute(text("SELECT COUNT(*) FROM accounting_codes")).scalar()
        role_count = conn.execute(text("SELECT COUNT(*) FROM roles")).scalar()
        uom_count = conn.execute(text("SELECT COUNT(*) FROM unit_of_measures")).scalar()
        lic_count = 0
        settings_count = 0
        try:
            lic_count = conn.execute(text("SELECT COUNT(*) FROM license_keys")).scalar()
        except Exception:
            pass
        try:
            settings_count = conn.execute(text("SELECT COUNT(*) FROM app_settings")).scalar()
        except Exception:
            pass

    print()
    print('Post-reset verification:')
    print(f'  Companies:        {co_count}  (should be 0)')
    print(f'  Users:            {user_count}  (should be 0)')
    print(f'  License keys:     {lic_count}  (should be 0)')
    print(f'  App settings:     {settings_count}  (should be 0)')
    print(f'  Accounting codes: {ac_count}  (system COA retained)')
    print(f'  Roles:            {role_count}  (should be > 0)')
    print(f'  Units of measure: {uom_count}  (should be > 0)')
    print()
    if co_count == 0 and user_count == 0:
        print('[OK] Database reset complete. Setup wizard will show on next visit.')
        print('  Run the app: uvicorn app.main:app --port 8010 --reload')
        print('  Open: http://localhost:8010/static/company-setup.html')
    else:
        print('WARNING: Some data may not have been cleared. Check errors above.')


if __name__ == '__main__':
    import argparse
    parser = argparse.ArgumentParser(description='Reset DB for fresh setup wizard test')
    parser.add_argument('--dry-run', action='store_true', help='Preview only, no changes')
    parser.add_argument('--yes', '-y', action='store_true', help='Skip confirmation prompt')
    args = parser.parse_args()

    if not args.dry_run and not args.yes:
        confirm = input('\nThis will DELETE all company data. Type YES to confirm: ')
        if confirm.strip() != 'YES':
            print('Aborted.')
            sys.exit(0)
        print()

    reset(dry_run=args.dry_run)
