"""
Cleanup script to fix malformed accounting codes.
These are hex-like codes like 402d, 61cd, 40ea70, etc. that were created
in error and should not exist in a proper chart of accounts.

This script:
1. Reassigns any journal entries using malformed codes to valid fallback accounts
2. Deletes all malformed accounting codes
"""
from app.core.database import engine
from sqlalchemy import text

def main():
    with engine.connect() as conn:
        # Get all malformed code IDs (codes containing hex letters a-f)
        result = conn.execute(text("""
            SELECT id, code, name FROM accounting_codes
            WHERE code ~ '[a-fA-F]'
            ORDER BY code
        """))
        malformed = result.fetchall()

        if not malformed:
            print("No malformed accounting codes found. Database is clean!")
            return

        print(f"Found {len(malformed)} malformed accounting codes:")
        for c in malformed:
            print(f"  - {c.code}: {c.name}")

        # Get valid fallback accounts
        # Use 4000 (Product Sales) for revenue codes starting with 4
        # Use 6100 (Rent Expense) for expense codes starting with 6
        fallback_revenue = conn.execute(text(
            "SELECT id FROM accounting_codes WHERE code = '4000'"
        )).fetchone()
        fallback_expense = conn.execute(text(
            "SELECT id FROM accounting_codes WHERE code = '6100'"
        )).fetchone()

        if not fallback_revenue:
            print("ERROR: Could not find fallback revenue account (4000)")
            return
        if not fallback_expense:
            print("ERROR: Could not find fallback expense account (6100)")
            return

        print(f"\nUsing fallback revenue account: {fallback_revenue.id}")
        print(f"Using fallback expense account: {fallback_expense.id}")

        # Update journal entries that reference malformed codes
        print("\nReassigning journal entries...")
        total_reassigned = 0
        for code in malformed:
            fallback = fallback_revenue.id if code.code.startswith('4') else fallback_expense.id
            result = conn.execute(text("""
                UPDATE journal_entries
                SET accounting_code_id = :fallback
                WHERE accounting_code_id = :bad_id
            """), {'fallback': fallback, 'bad_id': code.id})
            if result.rowcount > 0:
                print(f"  Reassigned {result.rowcount} journal entries from {code.code}")
                total_reassigned += result.rowcount

        if total_reassigned > 0:
            print(f"Total reassigned: {total_reassigned}")
        else:
            print("  No journal entries needed reassignment")

        # Delete the malformed codes
        print("\nDeleting malformed codes...")
        for code in malformed:
            conn.execute(text("DELETE FROM accounting_codes WHERE id = :id"), {'id': code.id})
            print(f"  Deleted: {code.code} - {code.name}")

        conn.commit()
        print(f"\n{'='*50}")
        print(f"SUCCESS: Deleted {len(malformed)} malformed accounting codes.")

if __name__ == '__main__':
    main()
