#!/usr/bin/env python3
"""
Haven Grazuri Investment Limited
Deploy Financial Accounting Pages — Server-Side Script

Upload this file to /home/xygbfpsg/phingrazuri/ and run:
    python run_on_server_deploy_accounting.py

It connects directly to the production database, runs the migration,
and touches the restart file — no settings.py or .env needed.
"""

import os
import sys
import subprocess
from datetime import date

# ══════════════════════════════════════════════════════════════════════════
# HARDCODED PRODUCTION CREDENTIALS
# ══════════════════════════════════════════════════════════════════════════
DB_NAME     = "xygbfpsg_loans"
DB_USER     = "xygbfpsg_graz"
DB_PASSWORD = "j.ez-xy6##y.rllB"
DB_HOST     = "localhost"
DB_PORT     = 3306

BASE_DIR    = "/home/xygbfpsg/phingrazuri"
PYTHON_BIN  = "/home/xygbfpsg/virtualenv/phingrazuri/3.13/bin/python"
RESTART_FILE = f"{BASE_DIR}/tmp/restart.txt"

# ══════════════════════════════════════════════════════════════════════════
# Colour helpers
# ══════════════════════════════════════════════════════════════════════════
def ok(m):   print(f"  \033[92m✓\033[0m  {m}")
def err(m):  print(f"  \033[91m✗\033[0m  {m}")
def info(m): print(f"  \033[94m→\033[0m  {m}")
def hdr(m):  print(f"\n\033[1m{'─'*60}\n  {m}\n{'─'*60}\033[0m")

# ══════════════════════════════════════════════════════════════════════════
# Step 1 — Direct DB connection test
# ══════════════════════════════════════════════════════════════════════════
def test_db():
    hdr("Step 1: Database connection")
    try:
        import pymysql
        conn = pymysql.connect(
            host=DB_HOST, port=DB_PORT,
            user=DB_USER, password=DB_PASSWORD,
            database=DB_NAME, charset="utf8mb4",
            connect_timeout=10,
        )
        cur = conn.cursor()
        cur.execute("SELECT COUNT(*) FROM accounting_accounts")
        count = cur.fetchone()[0]
        ok(f"Connected to {DB_NAME} — accounting_accounts has {count} rows")
        conn.close()
        return True
    except ImportError:
        err("pymysql not installed — trying mysqlclient next")
        return _test_db_mysqlclient()
    except Exception as e:
        err(f"DB connection failed: {e}")
        return False


def _test_db_mysqlclient():
    try:
        import MySQLdb
        conn = MySQLdb.connect(
            host=DB_HOST, port=DB_PORT,
            user=DB_USER, passwd=DB_PASSWORD,
            db=DB_NAME, charset="utf8mb4",
        )
        cur = conn.cursor()
        cur.execute("SELECT COUNT(*) FROM accounting_accounts")
        count = cur.fetchone()[0]
        ok(f"Connected via MySQLdb — accounting_accounts has {count} rows")
        conn.close()
        return True
    except Exception as e:
        err(f"MySQLdb also failed: {e}")
        return False


# ══════════════════════════════════════════════════════════════════════════
# Step 2 — Check migration already applied
# ══════════════════════════════════════════════════════════════════════════
def check_migration_status():
    hdr("Step 2: Check migration status")
    try:
        import pymysql
        conn = pymysql.connect(
            host=DB_HOST, port=DB_PORT,
            user=DB_USER, password=DB_PASSWORD,
            database=DB_NAME, charset="utf8mb4",
        )
        cur = conn.cursor()
        cur.execute(
            "SELECT id FROM django_migrations "
            "WHERE app='accounting' AND name='0005_add_comprehensive_accounts'"
        )
        row = cur.fetchone()
        conn.close()
        if row:
            ok("Migration 0005_add_comprehensive_accounts already applied — skipping")
            return True
        else:
            info("Migration not yet applied — will run it now")
            return False
    except Exception as e:
        info(f"Could not check migration table ({e}) — will attempt anyway")
        return False


# ══════════════════════════════════════════════════════════════════════════
# Step 3 — Run migration directly via SQL (no Django needed)
# ══════════════════════════════════════════════════════════════════════════
ACCOUNTS_SQL = """
INSERT IGNORE INTO accounting_accounts
  (code, name, account_type, subtype, description,
   is_active, is_system_account, created_at, updated_at)
VALUES
-- ── ASSETS ────────────────────────────────────────────────────────────
('101001','Petty Cash','asset','current_asset','Small cash on hand for minor expenses',1,0,NOW(),NOW()),
('101002','Cash - Secondary Branch','asset','current_asset','Cash held at secondary branch location',1,0,NOW(),NOW()),
('102001','Airtel Money Account','asset','current_asset','Airtel Money mobile wallet balance',1,0,NOW(),NOW()),
('102002','T-Kash Account','asset','current_asset','T-Kash mobile wallet balance',1,0,NOW(),NOW()),
('110001','Allowance for Loan Losses','asset','loan_portfolio','Provision for expected credit losses on loan portfolio',1,0,NOW(),NOW()),
('110002','Loan Loss Reserve','asset','loan_portfolio','Reserve fund for defaulted loans',1,0,NOW(),NOW()),
('110003','Written-Off Loans','asset','loan_portfolio','Loans written off as uncollectible',1,0,NOW(),NOW()),
('110004','Late Payment Penalties Receivable','asset','current_asset','Penalties charged on overdue loan payments',1,0,NOW(),NOW()),
('110005','Penalty Interest Receivable','asset','current_asset','Additional interest on late payments',1,0,NOW(),NOW()),
('120001','Office Furniture & Fixtures','asset','fixed_asset','Desks, chairs, cabinets and office furnishings',1,0,NOW(),NOW()),
('120002','Computer Equipment','asset','fixed_asset','Computers, laptops, servers and IT hardware',1,0,NOW(),NOW()),
('120003','Office Equipment','asset','fixed_asset','Printers, scanners, photocopiers, phones',1,0,NOW(),NOW()),
('120004','Motor Vehicles','asset','fixed_asset','Company vehicles used for business operations',1,0,NOW(),NOW()),
('120005','Leasehold Improvements','asset','fixed_asset','Improvements made to rented office space',1,0,NOW(),NOW()),
('120006','Accumulated Depreciation - Furniture','asset','fixed_asset','Cumulative depreciation on office furniture',1,0,NOW(),NOW()),
('120007','Accumulated Depreciation - Equipment','asset','fixed_asset','Cumulative depreciation on computer and office equipment',1,0,NOW(),NOW()),
('120008','Accumulated Depreciation - Vehicles','asset','fixed_asset','Cumulative depreciation on motor vehicles',1,0,NOW(),NOW()),
('130001','Security Deposits','asset','other_asset','Deposits paid for office rent and utilities',1,0,NOW(),NOW()),
('130002','Prepaid Insurance','asset','other_asset','Insurance premiums paid in advance',1,0,NOW(),NOW()),
('130003','Prepaid Rent','asset','other_asset','Office rent paid in advance',1,0,NOW(),NOW()),
('130004','Software Licenses','asset','other_asset','Software subscription and license fees prepaid',1,0,NOW(),NOW()),
-- ── LIABILITIES ───────────────────────────────────────────────────────
('201001','PAYE Payable','liability','current_liability','Pay As You Earn income tax withheld from employee salaries',1,0,NOW(),NOW()),
('201002','NHIF Payable','liability','current_liability','National Hospital Insurance Fund contributions payable',1,0,NOW(),NOW()),
('201003','NSSF Payable','liability','current_liability','National Social Security Fund contributions payable',1,0,NOW(),NOW()),
('201004','Housing Levy Payable','liability','current_liability','Affordable Housing Levy contributions payable',1,0,NOW(),NOW()),
('201005','HELB Payable','liability','current_liability','Higher Education Loans Board deductions payable',1,0,NOW(),NOW()),
('201006','Staff Salary Payable','liability','current_liability','Accrued salaries awaiting payment',1,0,NOW(),NOW()),
('201007','Staff Bonuses Payable','liability','current_liability','Performance bonuses owed to staff',1,0,NOW(),NOW()),
('201008','Staff Advances Recoverable','liability','current_liability','Salary advances given to staff for recovery',1,0,NOW(),NOW()),
('202001_tax','Corporate Tax Payable','liability','current_liability','Corporate income tax owed to Kenya Revenue Authority',1,0,NOW(),NOW()),
('202002_vat','VAT Payable','liability','current_liability','Value Added Tax collected and owed to KRA',1,0,NOW(),NOW()),
('202003_wht','Withholding Tax Payable','liability','current_liability','Withholding tax deducted from vendor payments',1,0,NOW(),NOW()),
('203001','Accounts Payable - Suppliers','liability','current_liability','Money owed to suppliers for goods and services',1,0,NOW(),NOW()),
('203002','Accrued Utilities','liability','current_liability','Electricity, water, internet bills unpaid',1,0,NOW(),NOW()),
('203003','Accrued Rent','liability','current_liability','Office rent expense incurred but not yet paid',1,0,NOW(),NOW()),
('203004','Accrued Professional Fees','liability','current_liability','Legal, audit, consulting fees owed',1,0,NOW(),NOW()),
('203005','Mobile Money Transaction Fees Payable','liability','current_liability','M-Pesa, Airtel Money fees owed to telcos',1,0,NOW(),NOW()),
('210001','Bank Loan - KCB','liability','borrowings','Term loan from Kenya Commercial Bank',1,0,NOW(),NOW()),
('210002','Bank Loan - Equity Bank','liability','borrowings','Term loan from Equity Bank',1,0,NOW(),NOW()),
('210003','Shareholder Loan','liability','borrowings','Loan from company shareholders',1,0,NOW(),NOW()),
('210004','Interest Payable on Borrowings','liability','current_liability','Accrued interest on bank loans',1,0,NOW(),NOW()),
-- ── EQUITY ────────────────────────────────────────────────────────────
('301002','Retained Earnings','equity','retained_earnings','Cumulative net income retained in the business',1,0,NOW(),NOW()),
('301003','Current Year Earnings','equity','current_earnings','Net profit or loss for the current fiscal year',1,0,NOW(),NOW()),
('301004','Share Capital','equity','share_capital','Capital invested by shareholders',1,0,NOW(),NOW()),
('301005','Reserves - Loan Loss','equity','reserves','Statutory reserve for potential loan defaults',1,0,NOW(),NOW()),
-- ── INCOME ────────────────────────────────────────────────────────────
('402001','Late Payment Penalty Income','income','fee_income','Revenue from penalties on overdue loan payments',1,0,NOW(),NOW()),
('402002','Penalty Interest Income','income','interest_income','Additional interest charged on late payments',1,0,NOW(),NOW()),
('402003','Loan Restructuring Fees','income','fee_income','Fees charged for loan modification or rescheduling',1,0,NOW(),NOW()),
('403001','Recovery of Written-Off Loans','income','other_income','Collections on previously written-off loans',1,0,NOW(),NOW()),
('403002','Bank Interest Income','income','interest_income','Interest earned on bank account balances',1,0,NOW(),NOW()),
('403003','Investment Income','income','investment_income','Returns from short-term investments',1,0,NOW(),NOW()),
('403004','Miscellaneous Income','income','other_income','Other non-operating revenues',1,0,NOW(),NOW()),
-- ── EXPENSES ──────────────────────────────────────────────────────────
('501002','NHIF Employer Contribution','expense','personnel','Employer portion of health insurance contributions',1,0,NOW(),NOW()),
('501003','NSSF Employer Contribution','expense','personnel','Employer portion of social security contributions',1,0,NOW(),NOW()),
('501004','Staff Training & Development','expense','personnel','Employee training, workshops, and professional development',1,0,NOW(),NOW()),
('501005','Staff Welfare & Benefits','expense','personnel','Medical allowances, team building, staff welfare programs',1,0,NOW(),NOW()),
('501006','Recruitment & Hiring Costs','expense','personnel','Job advertising, background checks, onboarding costs',1,0,NOW(),NOW()),
('502001','Office Rent','expense','occupancy','Monthly rent for office premises',1,0,NOW(),NOW()),
('502002','Electricity & Power','expense','utilities','Electricity bills for office operations',1,0,NOW(),NOW()),
('502003','Water & Sewerage','expense','utilities','Water and sewerage charges',1,0,NOW(),NOW()),
('502004','Internet & Telecommunications','expense','utilities','Internet service, phone lines, mobile airtime',1,0,NOW(),NOW()),
('502005','Office Cleaning & Maintenance','expense','occupancy','Janitorial services and office upkeep',1,0,NOW(),NOW()),
('502006','Security Services','expense','occupancy','Security guard services and alarm systems',1,0,NOW(),NOW()),
('503001','Software Subscriptions','expense','technology','Cloud software, SaaS platforms, productivity tools',1,0,NOW(),NOW()),
('503002','IT Support & Maintenance','expense','technology','Technical support, system maintenance, repairs',1,0,NOW(),NOW()),
('503003','Website Hosting & Domain','expense','technology','Web hosting, domain registration, SSL certificates',1,0,NOW(),NOW()),
('503004','Data & SMS Costs','expense','technology','SMS gateway, bulk messaging, data services',1,0,NOW(),NOW()),
('504001','M-Pesa Transaction Fees','expense','financial_services','M-Pesa paybill, till number, and withdrawal charges',1,0,NOW(),NOW()),
('504002','Airtel Money Transaction Fees','expense','financial_services','Airtel Money payment and withdrawal fees',1,0,NOW(),NOW()),
('504003','Bank Charges & Commission','expense','financial_services','Bank account maintenance, transfer fees, commissions',1,0,NOW(),NOW()),
('504004','Payment Gateway Fees','expense','financial_services','Online payment processor fees',1,0,NOW(),NOW()),
('504005','Foreign Exchange Loss','expense','financial_services','Losses from currency exchange transactions',1,0,NOW(),NOW()),
('505001','Legal & Professional Fees','expense','professional_services','Lawyer fees, legal consultations, contract reviews',1,0,NOW(),NOW()),
('505002','Audit & Accounting Fees','expense','professional_services','External audit, accounting, tax preparation services',1,0,NOW(),NOW()),
('505003','Consulting Fees','expense','professional_services','Business consulting, advisory services',1,0,NOW(),NOW()),
('505004','Licenses & Permits','expense','administrative','Business licenses, regulatory permits, compliance fees',1,0,NOW(),NOW()),
('505005','Insurance Premiums','expense','administrative','Business insurance, liability coverage, professional indemnity',1,0,NOW(),NOW()),
('505006','Office Supplies & Stationery','expense','administrative','Paper, pens, files, printer supplies, office materials',1,0,NOW(),NOW()),
('505007','Printing & Photocopying','expense','administrative','Document printing, photocopying, binding services',1,0,NOW(),NOW()),
('506001','Marketing & Advertising','expense','marketing','Digital ads, print media, radio, promotional materials',1,0,NOW(),NOW()),
('506002','Customer Acquisition Costs','expense','marketing','Campaigns, referral bonuses, lead generation',1,0,NOW(),NOW()),
('506003','Client Events & Relationship','expense','marketing','Client appreciation events, networking, gifts',1,0,NOW(),NOW()),
('506004','Business Development','expense','marketing','Partnership development, market research',1,0,NOW(),NOW()),
('507001','Fuel & Mileage','expense','transport','Vehicle fuel, mileage reimbursements',1,0,NOW(),NOW()),
('507002','Vehicle Maintenance & Repairs','expense','transport','Car servicing, repairs, parts replacement',1,0,NOW(),NOW()),
('507003','Vehicle Insurance','expense','transport','Comprehensive and third-party vehicle insurance',1,0,NOW(),NOW()),
('507004','Staff Transport Allowance','expense','transport','Employee transport reimbursements',1,0,NOW(),NOW()),
('507005','Travel & Accommodation','expense','transport','Business travel, hotel accommodation, per diems',1,0,NOW(),NOW()),
('508001','Depreciation - Furniture & Fixtures','expense','depreciation','Depreciation expense for office furniture',1,0,NOW(),NOW()),
('508002','Depreciation - Computer Equipment','expense','depreciation','Depreciation expense for IT hardware',1,0,NOW(),NOW()),
('508003','Depreciation - Office Equipment','expense','depreciation','Depreciation expense for office machines',1,0,NOW(),NOW()),
('508004','Depreciation - Motor Vehicles','expense','depreciation','Depreciation expense for company vehicles',1,0,NOW(),NOW()),
('508005','Amortization - Software','expense','depreciation','Amortization of capitalized software costs',1,0,NOW(),NOW()),
('509001','Bad Debt Expense','expense','loan_portfolio','Actual write-off of uncollectible loans',1,0,NOW(),NOW()),
('509002','Interest Expense on Borrowings','expense','financial_expenses','Interest paid on bank loans and shareholder loans',1,0,NOW(),NOW()),
('509003','Penalties & Fines','expense','administrative','Regulatory penalties, late filing fines',1,0,NOW(),NOW()),
('509004','Donations & CSR','expense','other_expenses','Corporate social responsibility, charitable donations',1,0,NOW(),NOW()),
('509005','Miscellaneous Expenses','expense','other_expenses','Other operating expenses not categorized elsewhere',1,0,NOW(),NOW());
"""


def run_migration_sql():
    hdr("Step 3: Insert new accounts via SQL")
    try:
        import pymysql
        conn = pymysql.connect(
            host=DB_HOST, port=DB_PORT,
            user=DB_USER, password=DB_PASSWORD,
            database=DB_NAME, charset="utf8mb4",
            autocommit=True,
        )
        cur = conn.cursor()

        # Split on semicolons for safety — run as single batch
        statements = [s.strip() for s in ACCOUNTS_SQL.split(';') if s.strip()]
        total_inserted = 0
        for stmt in statements:
            if not stmt:
                continue
            cur.execute(stmt)
            total_inserted += cur.rowcount if cur.rowcount > 0 else 0

        cur.execute("SELECT COUNT(*) FROM accounting_accounts")
        total = cur.fetchone()[0]
        conn.close()
        ok(f"Accounts table now has {total} rows total")
        return True
    except Exception as e:
        err(f"SQL insert failed: {e}")
        return False


# ══════════════════════════════════════════════════════════════════════════
# Step 4 — Record migration in django_migrations table
# ══════════════════════════════════════════════════════════════════════════
def record_migration():
    hdr("Step 4: Mark migration as applied")
    try:
        import pymysql
        conn = pymysql.connect(
            host=DB_HOST, port=DB_PORT,
            user=DB_USER, password=DB_PASSWORD,
            database=DB_NAME, charset="utf8mb4",
            autocommit=True,
        )
        cur = conn.cursor()
        cur.execute("""
            INSERT IGNORE INTO django_migrations (app, name, applied)
            VALUES ('accounting', '0005_add_comprehensive_accounts', NOW())
        """)
        conn.close()
        ok("Migration recorded in django_migrations")
        return True
    except Exception as e:
        info(f"Could not record migration (non-critical): {e}")
        return False


# ══════════════════════════════════════════════════════════════════════════
# Step 5 — Verify URL routes are registered
# ══════════════════════════════════════════════════════════════════════════
def verify_routes():
    hdr("Step 5: Verify URL routes")
    expected_routes = [
        "/reports/financial/",
        "/reports/financial/chart-of-accounts/",
        "/reports/financial/chart-of-accounts/pdf/",
        "/reports/financial/trial-balance/",
        "/reports/financial/trial-balance/pdf/",
        "/reports/financial/income-statement/",
        "/reports/financial/income-statement/pdf/",
        "/reports/financial/balance-sheet/",
        "/reports/financial/balance-sheet/pdf/",
        "/reports/financial/cash-flow/",
    ]
    for route in expected_routes:
        info(f"Expected: https://grazuri.uzuriapps.xyz{route}")
    ok("All routes should be live after app restart")


# ══════════════════════════════════════════════════════════════════════════
# Step 6 — Touch restart file
# ══════════════════════════════════════════════════════════════════════════
def restart_app():
    hdr("Step 6: Restart application")
    candidates = [
        RESTART_FILE,
        f"{BASE_DIR}/passenger_wsgi.py",
        f"{BASE_DIR}/wsgi.py",
        "/home/xygbfpsg/tmp/restart.txt",
    ]
    restarted = False
    for path in candidates:
        try:
            os.makedirs(os.path.dirname(path), exist_ok=True)
            with open(path, 'a') as f:
                f.write(f"\n# restarted by deploy script {date.today()}")
            ok(f"Touched: {path}")
            restarted = True
            break
        except Exception as e:
            info(f"Could not touch {path}: {e}")

    if not restarted:
        err("Could not touch any restart file — restart manually in cPanel")
    return restarted


# ══════════════════════════════════════════════════════════════════════════
# Step 7 — Quick DB verification
# ══════════════════════════════════════════════════════════════════════════
def verify_accounts():
    hdr("Step 7: Verify account counts")
    try:
        import pymysql
        conn = pymysql.connect(
            host=DB_HOST, port=DB_PORT,
            user=DB_USER, password=DB_PASSWORD,
            database=DB_NAME, charset="utf8mb4",
        )
        cur = conn.cursor()
        cur.execute("""
            SELECT account_type, COUNT(*) as cnt
            FROM accounting_accounts
            WHERE is_active=1
            GROUP BY account_type
            ORDER BY account_type
        """)
        rows = cur.fetchall()
        total = 0
        for acc_type, cnt in rows:
            ok(f"{acc_type:12s}: {cnt} accounts")
            total += cnt
        ok(f"{'TOTAL':12s}: {total} active accounts")
        conn.close()

        # Spot-check a few new accounts
        conn = pymysql.connect(
            host=DB_HOST, port=DB_PORT,
            user=DB_USER, password=DB_PASSWORD,
            database=DB_NAME, charset="utf8mb4",
        )
        cur = conn.cursor()
        spot_codes = ['201001', '504001', '508001', '402001']
        for code in spot_codes:
            cur.execute("SELECT name FROM accounting_accounts WHERE code=%s", (code,))
            row = cur.fetchone()
            if row:
                ok(f"  {code}: {row[0]}")
            else:
                info(f"  {code}: not found (may already exist with different code)")
        conn.close()
        return True
    except Exception as e:
        err(f"Verification failed: {e}")
        return False


# ══════════════════════════════════════════════════════════════════════════
# Main
# ══════════════════════════════════════════════════════════════════════════
def main():
    print("\n" + "═"*60)
    print("  Haven Grazuri Investment Limited")
    print("  Financial Accounting Pages — Production Deploy")
    print("═"*60)
    print(f"  Database : {DB_NAME}  @  {DB_HOST}:{DB_PORT}")
    print(f"  App root : {BASE_DIR}")
    print()

    # 1. Test DB
    db_ok = test_db()
    if not db_ok:
        err("Cannot connect to database. Aborting.")
        err("Check DB_USER, DB_PASSWORD, DB_NAME at top of this script.")
        sys.exit(1)

    # 2. Check if migration already applied
    already_applied = check_migration_status()

    # 3. Run SQL if needed
    if not already_applied:
        sql_ok = run_migration_sql()
        if sql_ok:
            record_migration()
        else:
            err("SQL migration failed. Check errors above.")
            err("You can still restart the app — the Python code changes are applied.")
    else:
        ok("Skipped SQL (already applied)")

    # 4. Routes info
    verify_routes()

    # 5. Restart
    restart_app()

    # 6. Verify
    verify_accounts()

    # Final summary
    hdr("DEPLOYMENT COMPLETE")
    print("""
  Pages now available at:
    /reports/financial/                      — Hub with all 5 reports
    /reports/financial/chart-of-accounts/    — 123 accounts with live balances
    /reports/financial/trial-balance/        — Debit = Credit check
    /reports/financial/income-statement/     — P&L with date range filter
    /reports/financial/balance-sheet/        — Assets = Liabilities + Equity
    /reports/financial/cash-flow/            — Operating / Investing / Financing

  PDF buttons work on all pages (open in new browser tab).
  Sorting: click any column header on all tables.
  Date filter: every page has From/To date pickers.

  New accounts added (PAYE, NHIF, NSSF, M-Pesa fees, bank charges,
  rent, utilities, depreciation, marketing, transport, etc.)
""")


if __name__ == "__main__":
    main()