#!/usr/bin/env python3
"""
Database diagnostic — checks both old Grazuri tables and new Django tables.
Run on server: python check_data.py
"""
import os
import sys

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')

try:
    from dotenv import load_dotenv
    load_dotenv(os.path.join(os.path.dirname(os.path.abspath(__file__)), '.env'), override=True)
except ImportError:
    pass

import django
django.setup()

from django.db import connection
from loans.models import Loan, LoanApplication
from users.models import CustomUser

print(f"\n{'='*60}")
print(f"  Database Diagnostic")
print(f"{'='*60}")

# ── Django (new) tables ───────────────────────────────────────
print(f"\n{'─'*60}")
print("  NEW DJANGO TABLES (what the dashboard reads)")
print(f"{'─'*60}")

django_checks = [
    ("loans",                  lambda: Loan.objects.count()),
    ("loans (status=active)",  lambda: Loan.objects.filter(status='active').count()),
    ("loans (status=paid)",    lambda: Loan.objects.filter(status='paid').count()),
    ("loan_applications",      lambda: LoanApplication.objects.count()),
    ("loan_applications (pending)", lambda: LoanApplication.objects.filter(status='pending').count()),
    ("users (all)",            lambda: CustomUser.objects.count()),
    ("users (role=borrower)",  lambda: CustomUser.objects.filter(role='borrower').count()),
    ("users (role=admin)",     lambda: CustomUser.objects.filter(role='admin').count()),
    ("users (is_active=True)", lambda: CustomUser.objects.filter(is_active=True).count()),
]

for label, fn in django_checks:
    try:
        count = fn()
        flag = "  ← EMPTY" if count == 0 else ""
        print(f"  {label:<40} {count:>8}{flag}")
    except Exception as e:
        print(f"  {label:<40} ERROR: {e}")

# ── Old Grazuri tables ────────────────────────────────────────
print(f"\n{'─'*60}")
print("  OLD GRAZURI TABLES (where the real data lives)")
print(f"{'─'*60}")

old_tables = [
    ("loan_info",          "SELECT COUNT(*) FROM loan_info"),
    ("loan_info (active)", "SELECT COUNT(*) FROM loan_info WHERE status NOT IN ('', 'Pending') OR disbursement_status NOT IN ('PENDING_DISB','PENDING_DOC_REVIEW','')"),
    ("borrowers",          "SELECT COUNT(*) FROM borrowers"),
    ("borrowers (active)", "SELECT COUNT(*) FROM borrowers WHERE status='Active'"),
    ("payments",           "SELECT COUNT(*) FROM payments"),
    ("repayments",         "SELECT COUNT(*) FROM repayments"),
    ("loan_applications",  "SELECT COUNT(*) FROM loan_applications"),
    ("loan_products",      "SELECT COUNT(*) FROM loan_products"),
    ("users (old)",        "SELECT COUNT(*) FROM `user`"),
    ("users (new)",        "SELECT COUNT(*) FROM `users`"),
]

with connection.cursor() as c:
    for label, sql in old_tables:
        try:
            c.execute(sql)
            count = c.fetchone()[0]
            flag = "  ← EMPTY" if count == 0 else ""
            print(f"  {label:<40} {count:>8}{flag}")
        except Exception as e:
            print(f"  {label:<40} MISSING/ERROR: {e}")

# ── Status value samples ──────────────────────────────────────
print(f"\n{'─'*60}")
print("  STATUS VALUES IN OLD TABLES (to understand the data)")
print(f"{'─'*60}")

status_queries = [
    ("loan_info.status",              "SELECT status, COUNT(*) FROM loan_info GROUP BY status ORDER BY COUNT(*) DESC LIMIT 10"),
    ("loan_info.disbursement_status", "SELECT disbursement_status, COUNT(*) FROM loan_info GROUP BY disbursement_status ORDER BY COUNT(*) DESC LIMIT 10"),
    ("borrowers.status",              "SELECT status, COUNT(*) FROM borrowers GROUP BY status ORDER BY COUNT(*) DESC LIMIT 10"),
    ("loans.status (new)",            "SELECT status, COUNT(*) FROM loans GROUP BY status ORDER BY COUNT(*) DESC LIMIT 10"),
    ("users.status (new)",            "SELECT status, COUNT(*) FROM users GROUP BY status ORDER BY COUNT(*) DESC LIMIT 10"),
    ("users.role (new)",              "SELECT role, COUNT(*) FROM users GROUP BY role ORDER BY COUNT(*) DESC LIMIT 10"),
]

with connection.cursor() as c:
    for label, sql in status_queries:
        print(f"\n  {label}:")
        try:
            c.execute(sql)
            rows = c.fetchall()
            if rows:
                for val, cnt in rows:
                    print(f"    '{val}' → {cnt} rows")
            else:
                print(f"    (no rows)")
        except Exception as e:
            print(f"    ERROR: {e}")

# ── Key columns check ─────────────────────────────────────────
print(f"\n{'─'*60}")
print("  KEY COLUMN NAMES (to plan the migration mapping)")
print(f"{'─'*60}")

col_queries = [
    ("loan_info columns",  "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='loan_info' ORDER BY ORDINAL_POSITION"),
    ("borrowers columns",  "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='borrowers' ORDER BY ORDINAL_POSITION"),
    ("loans columns",      "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='loans' ORDER BY ORDINAL_POSITION"),
    ("users columns",      "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' ORDER BY ORDINAL_POSITION"),
]

with connection.cursor() as c:
    for label, sql in col_queries:
        print(f"\n  {label}:")
        try:
            c.execute(sql)
            cols = [r[0] for r in c.fetchall()]
            # Print in rows of 4
            for i in range(0, len(cols), 4):
                print(f"    {', '.join(cols[i:i+4])}")
        except Exception as e:
            print(f"    ERROR: {e}")

print(f"\n{'='*60}")
print("  Diagnostic complete. Paste this output to decide next steps.")
print(f"{'='*60}\n")