"""
Cleanup script to remove duplicate Sales Revenue and Operating Expense accounts.
These junk entries have codes like 4007, 4019, 4020, 614248, etc. and all have the same name.
"""
from app.core.database import engine
from sqlalchemy import text

def main():
    with engine.connect() as conn:
        # Get the proper fallback accounts
        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"Fallback revenue (4000): {fallback_revenue.id}")
        print(f"Fallback expense (6100): {fallback_expense.id}")

        # Get all junk Sales Revenue accounts (not 4000, 4100, 4200 which are valid parent accounts)
        junk_revenue = conn.execute(text("""
            SELECT id, code, name FROM accounting_codes
            WHERE name = 'Sales Revenue'
            ORDER BY code
        """)).fetchall()

        # Get all junk Operating Expense accounts
        junk_expense = conn.execute(text("""
            SELECT id, code, name FROM accounting_codes
            WHERE name = 'Operating Expense'
            ORDER BY code
        """)).fetchall()

        print(f"\nFound {len(junk_revenue)} Sales Revenue accounts to clean up")
        print(f"Found {len(junk_expense)} Operating Expense accounts to clean up")

        for acc in junk_revenue:
            print(f"  - {acc.code}: {acc.name}")
        for acc in junk_expense:
            print(f"  - {acc.code}: {acc.name}")

        # Reassign journal entries from junk revenue accounts
        print("\nReassigning journal entries...")
        total_reassigned = 0
        for acc in junk_revenue:
            result = conn.execute(text("""
                UPDATE journal_entries SET accounting_code_id = :fallback
                WHERE accounting_code_id = :old_id
            """), {'fallback': fallback_revenue.id, 'old_id': acc.id})
            if result.rowcount > 0:
                print(f"  Reassigned {result.rowcount} from {acc.code}")
                total_reassigned += result.rowcount

        # Reassign journal entries from junk expense accounts
        for acc in junk_expense:
            result = conn.execute(text("""
                UPDATE journal_entries SET accounting_code_id = :fallback
                WHERE accounting_code_id = :old_id
            """), {'fallback': fallback_expense.id, 'old_id': acc.id})
            if result.rowcount > 0:
                print(f"  Reassigned {result.rowcount} from {acc.code}")
                total_reassigned += result.rowcount

        print(f"\nTotal journal entries reassigned: {total_reassigned}")

        # Now delete the junk accounts
        print("\nDeleting junk accounts...")
        for acc in junk_revenue:
            conn.execute(text("DELETE FROM accounting_codes WHERE id = :id"), {'id': acc.id})
            print(f"  Deleted: {acc.code} - {acc.name}")

        for acc in junk_expense:
            conn.execute(text("DELETE FROM accounting_codes WHERE id = :id"), {'id': acc.id})
            print(f"  Deleted: {acc.code} - {acc.name}")

        conn.commit()
        print(f"\n{'='*50}")
        print(f"SUCCESS: Cleaned up {len(junk_revenue) + len(junk_expense)} junk accounts")

if __name__ == '__main__':
    main()
