#!/usr/bin/env python
"""
check_sms_history.py
────────────────────
Checks whether SMS history can be recovered from any source.
Run on the server: python check_sms_history.py
"""

import os, sys, django
os.environ['DJANGO_SETTINGS_MODULE'] = 'branch_system.settings_production'
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
django.setup()

from django.db import connection

SEP = '─' * 68

def run():
    with connection.cursor() as c:

        # 1. How many rows in sms_logs now?
        c.execute("SELECT COUNT(*) FROM sms_logs")
        count = c.fetchone()[0]
        print(f'\n  sms_logs rows now : {count}')

        # 2. Check MySQL binary log / general log — not accessible via Django,
        #    but we can check if there's a backup table
        c.execute("""
            SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME LIKE '%sms%'
        """)
        sms_tables = [row[0] for row in c.fetchall()]
        print(f'  Tables with "sms" : {sms_tables}')

        # 3. Check repayments for SMS evidence — every payment should have
        #    sent an SMS, so we can at least see what payments exist
        c.execute("SELECT COUNT(*) FROM repayments WHERE payment_source='automatic'")
        auto_payments = c.fetchone()[0]
        c.execute("SELECT COUNT(*) FROM repayments")
        total_payments = c.fetchone()[0]
        print(f'\n  Total repayments  : {total_payments}')
        print(f'  Auto (M-Pesa)     : {auto_payments}')
        print(f'  Manual            : {total_payments - auto_payments}')

        # 4. Check when sms_logs table was created (as a proxy for when it was lost)
        c.execute("""
            SELECT CREATE_TIME FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'sms_logs'
        """)
        row = c.fetchone()
        created_at = row[0] if row else 'unknown'
        print(f'\n  sms_logs created  : {created_at}')

        # 5. Check django_migrations to see when 0006 ran (that's when logs were lost)
        c.execute("""
            SELECT name, applied FROM django_migrations
            WHERE app = 'payments'
            ORDER BY applied
        """)
        print(f'\n  payments migrations:')
        for name, applied in c.fetchall():
            marker = ' ← deleted sms_logs from state' if '0006' in name else ''
            print(f'    {name}  applied={applied}{marker}')

    print(f'\n{SEP}')
    print('  VERDICT:')
    if count == 0:
        print('  The sms_logs table was just created empty by create_missing_tables.py.')
        print('  Previous SMS history is gone — the table was dropped/never existed on')
        print('  this server. Going forward, all new SMS messages will be logged here.')
    else:
        print(f'  {count} SMS log rows exist — history is intact.')
    print(SEP)

if __name__ == '__main__':
    run()