"""
Fix loan total_amount values directly via MySQL — no Django setup needed.

Reads DB credentials from .env in the same directory. Runs non-interactively.

TWO CATEGORIES of mismatched loans exist in this database:

  A) Legacy imported loans (GRZ-000002 to ~GRZ-000203):
     interest_amount is a tiny flat KES value (KES 15-30) regardless of the
     principal size. The stored total_amount was the actual repayment amount
     from the old PHP system. These are CORRECT as imported — do NOT touch them.

  B) New-system loans (e.g. GRZ-000235, GRZ-000237):
     interest_amount is a proper percentage calculation (e.g. 20% of KES 10,000
     = KES 2,000), but total_amount was saved as only the interest+fee portion,
     missing the principal. These are BROKEN and need fixing.

Distinction: if interest_amount >= 1% of principal, it's a percentage-based loan
(new system). Legacy flat-fee loans have interest_amount << 1% of principal.

Usage:
    cd /home/xygbfpsg/phingrazuri
    python fix_loan_totals_direct.py
"""
import os
import sys


# ---------------------------------------------------------------------------
# Load .env from the same directory as this script
# ---------------------------------------------------------------------------
def load_env(path):
    env = {}
    if not os.path.exists(path):
        print(f"WARNING: .env not found at {path}, using defaults")
        return env
    with open(path) as f:
        for line in f:
            line = line.strip()
            if not line or line.startswith('#') or '=' not in line:
                continue
            key, _, value = line.partition('=')
            env[key.strip()] = value.strip()
    return env


SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
env = load_env(os.path.join(SCRIPT_DIR, '.env'))

DB_HOST     = env.get('DB_HOST',     'localhost')
DB_PORT     = int(env.get('DB_PORT', '3306'))
DB_NAME     = env.get('DB_NAME',     'xygbfpsg_loans')
DB_USER     = env.get('DB_USER',     'xygbfpsg_graz')
DB_PASSWORD = env.get('DB_PASSWORD', '')

print("=" * 80)
print("FIX LOAN TOTAL_AMOUNT — DIRECT MySQL (non-interactive)")
print("=" * 80)
print(f"Connecting to {DB_USER}@{DB_HOST}:{DB_PORT}/{DB_NAME} ...")

try:
    import pymysql
except ImportError:
    print("ERROR: pymysql not installed. Run: pip install pymysql")
    sys.exit(1)

try:
    conn = pymysql.connect(
        host=DB_HOST,
        port=DB_PORT,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME,
        charset='utf8mb4',
    )
    print("Connected OK\n")
except pymysql.err.OperationalError as e:
    print(f"ERROR: Could not connect: {e}")
    sys.exit(1)

cursor = conn.cursor()

# ---------------------------------------------------------------------------
# Preview: all mismatched loans (for information only)
# ---------------------------------------------------------------------------
cursor.execute("""
    SELECT
        loan_number,
        principal_amount,
        interest_amount,
        processing_fee,
        total_amount,
        ROUND(interest_amount / principal_amount * 100, 4) AS interest_pct
    FROM loans
    WHERE is_deleted = 0
      AND ABS(total_amount - (principal_amount + interest_amount + processing_fee)) > 0.005
      AND principal_amount > 0
    ORDER BY loan_number
""")
all_bad = cursor.fetchall()
print(f"Total mismatched loans: {len(all_bad)}")

# Split into legacy (flat fee) vs new-system (percentage-based)
legacy = [r for r in all_bad if float(r[5]) < 1.0]   # interest < 1% of principal
fixable = [r for r in all_bad if float(r[5]) >= 1.0]  # interest >= 1% of principal

print(f"  Legacy imported loans (flat KES interest, SKIPPING): {len(legacy)}")
print(f"  New-system loans with broken total (WILL FIX):       {len(fixable)}\n")

# ---------------------------------------------------------------------------
# Show what will be fixed
# ---------------------------------------------------------------------------
if fixable:
    print(f"{'Loan':<15} {'Principal':>12} {'Interest':>12} {'Fee':>10} {'Stored Total':>14} {'Correct Total':>14} {'Diff':>10}")
    print("-" * 95)
    for row in fixable:
        loan_number, principal, interest, fee, stored, pct = row
        correct = float(principal) + float(interest) + float(fee)
        diff = correct - float(stored)
        print(f"{loan_number:<15} {float(principal):>12.2f} {float(interest):>12.2f} "
              f"{float(fee):>10.2f} {float(stored):>14.2f} {correct:>14.2f} {diff:>10.2f}")
    print()

    # ---------------------------------------------------------------------------
    # Apply fix — targets only percentage-based loans (interest >= 1% of principal)
    # ---------------------------------------------------------------------------
    print(f"Fixing {len(fixable)} loans ...")
    cursor.execute("""
        UPDATE loans
        SET total_amount = principal_amount + interest_amount + processing_fee
        WHERE is_deleted = 0
          AND ABS(total_amount - (principal_amount + interest_amount + processing_fee)) > 0.005
          AND principal_amount > 0
          AND (interest_amount / principal_amount * 100) >= 1
    """)
    conn.commit()
    rows_updated = cursor.rowcount
    print(f"OK: Fixed {rows_updated} loans\n")
else:
    print("No new-system loans need fixing.\n")

# ---------------------------------------------------------------------------
# Verify: remaining mismatches for percentage-based loans should be zero
# ---------------------------------------------------------------------------
cursor.execute("""
    SELECT COUNT(*) FROM loans
    WHERE is_deleted = 0
      AND ABS(total_amount - (principal_amount + interest_amount + processing_fee)) > 0.005
      AND principal_amount > 0
      AND (interest_amount / principal_amount * 100) >= 1
""")
remaining = cursor.fetchone()[0]

cursor.execute("SELECT COUNT(*) FROM loans WHERE is_deleted = 0")
total = cursor.fetchone()[0]

print("Verification:")
print(f"  Total active loans             : {total}")
print(f"  New-system loans still broken  : {remaining}")
if remaining == 0:
    print("  OK — all percentage-based loans now have correct total_amount")
else:
    print(f"  WARNING — {remaining} loans still have wrong totals, investigate manually")

cursor.close()
conn.close()
print("\nDone.")