#!/usr/bin/env python3
"""
Fix missing payment entries for purchase (221,616).
Uses the correct bank account (1123 - STANDARD CHARTERED) that user selected.
"""

import sys
import uuid
from datetime import date

# Setup path
sys.path.insert(0, '/dev/cnperp-dimensions')

from app.core.database import SessionLocal, engine
from app.models.accounting import AccountingEntry, JournalEntry, AccountingCode
from app.services.accounting_service import AccountingService
from sqlalchemy import text

def main():
    db = SessionLocal()

    try:
        # Get the purchase
        with engine.connect() as conn:
            result = conn.execute(text("""
                SELECT id, supplier_id, total_amount FROM purchases WHERE total_amount = 221616
            """))
            purchase_row = result.first()

        if not purchase_row:
            print("ERROR: Purchase with total_amount=221616 not found")
            return 1

        purchase_id = purchase_row[0]
        supplier_id = purchase_row[1]
        total_amount = purchase_row[2]

        print(f"✓ Found purchase: {purchase_id}")
        print(f"  Supplier ID: {supplier_id}")
        print(f"  Total: {total_amount}")
        print()

        # Get the bank account and GL code
        with engine.connect() as conn:
            result = conn.execute(text("""
                SELECT ba.id, ac.id as code_id, ac.code
                FROM bank_accounts ba
                JOIN accounting_codes ac ON ba.accounting_code_id = ac.id
                WHERE ac.code = '1123'
            """))
            bank_row = result.first()

        if not bank_row:
            print("ERROR: Bank account for GL code 1123 not found")
            return 1

        bank_account_id = bank_row[0]
        bank_code_id = bank_row[1]
        bank_code = bank_row[2]

        print(f"✓ Bank Account (1123): {bank_account_id}")
        print(f"  GL Code ID: {bank_code_id}")
        print()

        # Get AP account
        ap_code = db.query(AccountingCode).filter(AccountingCode.code == '2110').first()
        if not ap_code:
            print("ERROR: AP account (2110) not found")
            return 1

        print(f"✓ AP Account (2110): {ap_code.id}")
        print()

        # Get current payment entries for this purchase
        with engine.connect() as conn:
            result = conn.execute(text("""
                SELECT je.id, ac.code FROM journal_entries je
                JOIN accounting_codes ac ON je.accounting_code_id = ac.id
                WHERE je.purchase_id = :pid AND ac.code IN ('1100', '1123')
            """), {"pid": purchase_id})
            existing_bank_entries = result.fetchall()

        if existing_bank_entries:
            print(f"⚠ Found {len(existing_bank_entries)} existing PAYMENT entries:")
            for entry_id, code in existing_bank_entries:
                print(f"  - {entry_id} (Account {code})")
            print()

        # Check if purchase has bank_account_id set
        with engine.connect() as conn:
            result = conn.execute(text("""
                SELECT bank_account_id FROM purchases WHERE id = :pid
            """), {"pid": purchase_id})
            bank_row = result.first()

        needs_bank_assignment = bank_row[0] is None

        if needs_bank_assignment:
            print(f"ℹ Purchase.bank_account_id is NULL - will set to {bank_account_id}")
        else:
            print(f"ℹ Purchase.bank_account_id already set to: {bank_row[0]}")

        print()
        print("=== CREATING PAYMENT ENTRIES ===")
        print()

        # Create/reuse accounting entry
        # Get or create accounting entry for this purchase
        with engine.connect() as conn:
            result = conn.execute(text("""
                SELECT DISTINCT ae.id FROM accounting_entries ae
                JOIN journal_entries je ON ae.id = je.accounting_entry_id
                WHERE je.purchase_id = :pid
                LIMIT 1
            """), {"pid": purchase_id})
            entry_row = result.first()

        if entry_row:
            accounting_entry_id = entry_row[0]
            print(f"✓ Using existing AccountingEntry: {accounting_entry_id}")
        else:
            # Create new accounting entry
            from app.models.branches import Branch
            branch = db.query(Branch).first()
            if not branch:
                print("ERROR: No branch found")
                return 1

            accounting_entry = AccountingEntry(
                id=str(uuid.uuid4()),
                date_prepared=date.today(),
                date_posted=date.today(),
                particulars=f"Payment for purchase {purchase_id}",
                book="JD",
                status="posted",
                branch_id=branch.id
            )
            db.add(accounting_entry)
            db.flush()
            accounting_entry_id = accounting_entry.id
            print(f"✓ Created new AccountingEntry: {accounting_entry_id}")

        print()

        # Create payment journal entries
        # Entry 1: Debit AP
        je_ap = JournalEntry(
            id=str(uuid.uuid4()),
            accounting_code_id=ap_code.id,
            accounting_entry_id=accounting_entry_id,
            purchase_id=purchase_id,
            entry_type="payment",
            narration=f"Payment for purchase {purchase_id}",
            date=date.today(),
            date_posted=date.today(),
            reference=purchase_id,
            description=f"AP Payment - Purchase {purchase_id}",
            debit_amount=total_amount,
            credit_amount=0,
            origin="system"
        )
        db.add(je_ap)
        db.flush()

        print(f"✓ Created AP Payment Entry (Debit 2110)")
        print(f"  Journal Entry ID: {je_ap.id}")
        print(f"  Amount: {total_amount}")
        print()

        # Entry 2: Credit Bank
        je_bank = JournalEntry(
            id=str(uuid.uuid4()),
            accounting_code_id=bank_code_id,
            accounting_entry_id=accounting_entry_id,
            purchase_id=purchase_id,
            entry_type="payment",
            narration=f"Payment from bank account for purchase {purchase_id}",
            date=date.today(),
            date_posted=date.today(),
            reference=purchase_id,
            description=f"Bank Payment - Purchase {purchase_id}",
            debit_amount=0,
            credit_amount=total_amount,
            origin="system"
        )
        db.add(je_bank)
        db.flush()

        print(f"✓ Created Bank Payment Entry (Credit 1123)")
        print(f"  Journal Entry ID: {je_bank.id}")
        print(f"  Amount: {total_amount}")
        print()

        # Update purchase's bank_account_id
        if needs_bank_assignment:
            with engine.connect() as conn:
                conn.execute(text("""
                    UPDATE purchases SET bank_account_id = :ba_id WHERE id = :pid
                """), {"ba_id": bank_account_id, "pid": purchase_id})
                conn.commit()
            print(f"✓ Updated purchase.bank_account_id = {bank_account_id}")
        else:
            print(f"ℹ Purchase.bank_account_id already set (no update needed)")

        print()

        # Update GL account balances
        print("=== UPDATING GL BALANCES ===")
        print()

        accounting_service = AccountingService(db)

        # Update AP account
        accounting_service.update_account_balance(ap_code.id)
        updated_ap = db.query(AccountingCode).filter(AccountingCode.id == ap_code.id).first()
        print(f"✓ Updated Account 2110 (AP)")
        print(f"  New Balance: {updated_ap.balance}")
        print(f"  Total Debits: {updated_ap.total_debits}")
        print(f"  Total Credits: {updated_ap.total_credits}")
        print()

        # Update Bank account
        accounting_service.update_account_balance(bank_code_id)
        updated_bank = db.query(AccountingCode).filter(AccountingCode.id == bank_code_id).first()
        print(f"✓ Updated Account 1123 (STANDARD CHARTERED BANK)")
        print(f"  New Balance: {updated_bank.balance}")
        print(f"  Total Debits: {updated_bank.total_debits}")
        print(f"  Total Credits: {updated_bank.total_credits}")
        print()

        # Commit all changes
        db.commit()

        print("=" * 50)
        print("✓ ALL CHANGES COMMITTED SUCCESSFULLY")
        print("=" * 50)
        print()
        print("Summary:")
        print(f"  Purchase ID: {purchase_id}")
        print(f"  Amount: {total_amount}")
        print(f"  Bank Account: 1123 (STANDARD CHARTERED)")
        print(f"  Journal Entries Created: 2 (AP Debit + Bank Credit)")
        print()

        return 0

    except Exception as e:
        print(f"ERROR: {e}")
        import traceback
        traceback.print_exc()
        db.rollback()
        return 1
    finally:
        db.close()

if __name__ == "__main__":
    sys.exit(main())
