"""
Fixes three production issues:
1. Receipt number collision — production receipts table has FK constraint,
   our locally-created one doesn't match. Fix: drop and recreate with correct schema.
2. SMS failure — check what error Africa's Talking is returning.
3. Process all unprocessed real IPN payments.

Upload to /home/xygbfpsg/phingrazuri/ and run: python fix_production_issues.py
"""
import sys, pymysql, json, uuid
from datetime import datetime

DB = dict(host='localhost', user='xygbfpsg_graz',
          password="+MvX9&%PV']]pW}", database='xygbfpsg_loans',
          port=3306, charset='utf8mb4', autocommit=True)

try:
    conn = pymysql.connect(**DB)
    cur  = conn.cursor()
    print("✅ Connected to production DB")
except Exception as e:
    print(f"❌ {e}"); sys.exit(1)

def q(sql, p=None):
    cur.execute(sql, p or ()); return cur.fetchall()

def run(sql, p=None):
    try: cur.execute(sql, p or ()); return True
    except Exception as e: return str(e)

# ── FIX 1: Receipt table — drop FK constraint causing duplicate key errors ────
print("\n── FIX 1: Fix receipts table FK constraint ───────────────────────────────")

# Check if the FK constraint exists
fk = q("""
    SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='receipts'
    AND CONSTRAINT_TYPE='FOREIGN KEY'
""")
if fk:
    for f in fk:
        r = run(f"ALTER TABLE `receipts` DROP FOREIGN KEY `{f[0]}`")
        print(f"   {'✅' if r is True else '⚠️ '} Dropped FK: {f[0]}")
else:
    print("   ✅ No FK constraints on receipts table")

# Also check if receipts table has correct columns
cols = {r[0] for r in q("SHOW COLUMNS FROM receipts")}
needed = {'amount_paid', 'payment_method', 'payment_date', 'previous_balance', 'new_balance'}
missing = needed - cols
for col in missing:
    defns = {
        'amount_paid':      'DECIMAL(12,2) NOT NULL DEFAULT 0.00',
        'payment_method':   "VARCHAR(20) NOT NULL DEFAULT 'cash'",
        'payment_date':     'DATETIME(6) NOT NULL DEFAULT NOW()',
        'previous_balance': 'DECIMAL(12,2) NOT NULL DEFAULT 0.00',
        'new_balance':      'DECIMAL(12,2) NOT NULL DEFAULT 0.00',
    }
    r = run(f"ALTER TABLE `receipts` ADD COLUMN `{col}` {defns[col]}")
    print(f"   {'✅' if r is True else '⚠️ '} Added column {col}: {'' if r is True else r}")

if not missing and not fk:
    print("   ✅ receipts table is correct")

# ── FIX 2: Check SMS failure reason ──────────────────────────────────────────
print("\n── FIX 2: Check SMS failure reason ──────────────────────────────────────")
rows = q("""
    SELECT sms_type, status, error_message, at_response, recipients, created_at
    FROM sms_logs WHERE status='failed' ORDER BY created_at DESC LIMIT 3
""")
for r in rows:
    print(f"   [{r[0]}] {r[1]} | {r[5]}")
    if r[2]:
        print(f"   Error: {r[2][:200]}")
    if r[3]:
        resp = r[3] if isinstance(r[3], dict) else json.loads(r[3])
        print(f"   AT Response: {str(resp)[:200]}")

# ── FIX 3: Process all unprocessed real IPN payments ─────────────────────────
print("\n── FIX 3: Process unprocessed IPN payments ──────────────────────────────")

# Get all IPN logs that don't have a matching repayment
ipn_rows = q("""
    SELECT id, raw_data, created_at FROM sasapay_ipn_logs
    ORDER BY created_at DESC LIMIT 20
""")

import os, django
os.environ['DB_NAME']     = 'xygbfpsg_loans'
os.environ['DB_USER']     = 'xygbfpsg_graz'
os.environ['DB_PASSWORD'] = "+MvX9&%PV']]pW}"
os.environ['DB_HOST']     = 'localhost'
os.environ['DB_PORT']     = '3306'
os.environ['SITE_URL']    = 'https://grazuri.uzuriapps.xyz'
os.environ['AT_USERNAME'] = 'Uzuriapps'
os.environ['AT_API_KEY']  = 'atsk_e810a145ccd3a1089769cd17887da70ae76124ff044fa0ab949429e6b388954e6db55009'
os.environ['SMS_SENDER_ID']         = 'HavGrazuri'
os.environ['SASAPAY_CLIENT_ID']     = 'e63utew7Vk3RmwKxjuMY3jocycFb5eoMZkzyKrPQ'
os.environ['SASAPAY_CLIENT_SECRET'] = '6ysF7fWBESaCNYfsh4aJOWxijTFx4JFIu5LqGFWnMx7TJxKiwaysaiNwe14Wgj9FupB6fhtRFZUas2geZZLjHwbHiShbX8w5bvWjy061Bq1SHX9EQtsBCtnuTLgaOmDV'
os.environ['SASAPAY_MERCHANT_CODE'] = '1122'
os.environ['SASAPAY_NETWORK_CODE']  = '63902'
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
from django.conf import settings
settings.DATABASES['default'].update({
    'NAME': 'xygbfpsg_loans', 'USER': 'xygbfpsg_graz',
    'PASSWORD': "+MvX9&%PV']]pW}", 'HOST': 'localhost', 'PORT': '3306',
})
django.setup()
from django.db import connections; connections['default'].close()
from payments.sasapay_service import process_ipn_callback
from loans.models import Repayment

for row in ipn_rows:
    data = row[1] if isinstance(row[1], dict) else json.loads(row[1])
    trans_id  = data.get('TransID', '')
    bill_ref  = data.get('BillRefNumber', '')
    amount    = data.get('TransAmount', '0')
    mpesa_ref = data.get('ThirdPartyTransID', trans_id)

    # Check if already processed
    if Repayment.objects.filter(mpesa_transaction_id=trans_id).exists():
        print(f"   ✅ Already processed: {trans_id} (KES {amount})")
        continue

    print(f"   Processing: {trans_id} | KES {amount} | BillRef:{bill_ref} | {row[2]}")
    result = process_ipn_callback(data)
    if result.get('success'):
        print(f"   ✅ Done! Receipt:{result.get('receipt_number')} Loan:{result.get('loan_number')}")
    else:
        print(f"   ❌ Failed: {result.get('message')}")

# ── SUMMARY ───────────────────────────────────────────────────────────────────
print("\n── Summary ──────────────────────────────────────────────────────────────")
total_reps = q("SELECT COUNT(*) FROM repayments")[0][0]
recent_sms = q("SELECT status, COUNT(*) FROM sms_logs GROUP BY status")
print(f"   Total repayments: {total_reps}")
print(f"   SMS status breakdown: {dict(recent_sms)}")

cur.close(); conn.close()