#!/usr/bin/env python3
"""
Backfill script: create missing BankTransaction records for purchases
that were paid via bank account but never had a BankTransaction created.

This fixes the data gap caused by the frontend sending accounting_code.id
instead of bank_account.id as the payment_source_id.

Usage:
    python scripts/backfill_purchase_bank_transactions.py           # preview (dry-run by default)
    python scripts/backfill_purchase_bank_transactions.py --commit  # write to DB
"""
import sys
import os

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

import argparse
from app.core.database import SessionLocal
from app.models.purchases import Purchase
from app.models.banking import BankAccount, BankTransaction
from app.models.accounting import JournalEntry


def resolve_bank_account(db, purchase):
    """
    Try to find the BankAccount that was used to pay for this purchase.
    Returns (BankAccount, source_hint) or (None, reason).
    """
    # Method 1: purchase.bank_account_id is a direct FK to bank_accounts.id
    if purchase.bank_account_id:
        acct = db.query(BankAccount).filter(BankAccount.id == purchase.bank_account_id).first()
        if acct:
            return acct, "bank_account_id column"
        # bank_account_id may hold an accounting_code.id (old bug) — try fallback
        acct = db.query(BankAccount).filter(
            BankAccount.accounting_code_id == purchase.bank_account_id
        ).first()
        if acct:
            return acct, f"bank_account_id resolved via accounting_code_id fallback"

    # Method 2: look at credit journal entries linked to this purchase.
    # A bank payment journal entry credits the bank's GL code.
    credit_entries = (
        db.query(JournalEntry)
        .filter(
            JournalEntry.purchase_id == purchase.id,
            JournalEntry.entry_type == "credit",
        )
        .all()
    )
    for je in credit_entries:
        if not je.accounting_code_id:
            continue
        acct = db.query(BankAccount).filter(
            BankAccount.accounting_code_id == je.accounting_code_id
        ).first()
        if acct:
            return acct, f"journal entry credit GL code → bank account"

    return None, "no bank account resolvable"


def already_has_transaction(db, purchase, bank_acct):
    """Return True if a purchase_payment BankTransaction already exists for this purchase."""
    # Match by purchase ID in description, OR by reference
    filters = []
    if purchase.reference:
        filters.append(BankTransaction.reference == purchase.reference)
    filters.append(BankTransaction.description.contains(str(purchase.id)))

    from sqlalchemy import or_
    existing = (
        db.query(BankTransaction)
        .filter(
            BankTransaction.bank_account_id == bank_acct.id,
            BankTransaction.transaction_type == "purchase_payment",
            or_(*filters),
        )
        .first()
    )
    return existing is not None


def main():
    parser = argparse.ArgumentParser(
        description="Backfill missing BankTransaction records for bank-paid purchases"
    )
    parser.add_argument(
        "--commit",
        action="store_true",
        help="Actually write records to the DB (default is dry-run preview)",
    )
    args = parser.parse_args()
    dry_run = not args.commit

    db = SessionLocal()
    try:
        # All purchases where some amount was paid
        purchases = (
            db.query(Purchase)
            .filter(Purchase.amount_paid > 0)
            .order_by(Purchase.purchase_date)
            .all()
        )

        print(f"Scanning {len(purchases)} paid purchases...")
        print()

        created = 0
        skipped_existing = 0
        skipped_no_bank = 0

        for p in purchases:
            bank_acct, source = resolve_bank_account(db, p)

            if bank_acct is None:
                # Can't determine which bank account → not a bank payment OR no data
                skipped_no_bank += 1
                continue

            if already_has_transaction(db, p, bank_acct):
                skipped_existing += 1
                continue

            # Need to create a BankTransaction for this purchase
            label = f"Purchase {p.reference or p.id[:8]+'...'} | {p.purchase_date} | {bank_acct.name} | BWP {p.amount_paid}"
            if dry_run:
                print(f"  WOULD CREATE: {label}  (resolved via: {source})")
            else:
                bt = BankTransaction(
                    bank_account_id=bank_acct.id,
                    date=p.purchase_date,
                    amount=-p.amount_paid,  # outflow = negative
                    description=f"Purchase payment - {p.reference or p.id}",
                    transaction_type="purchase_payment",
                    reference=p.reference or None,
                    reconciled=False,
                )
                db.add(bt)
                print(f"  CREATED: {label}")

            created += 1

        print()
        print("=" * 60)
        if dry_run:
            print(f"DRY RUN — no changes written.")
            print(f"  Would create : {created} BankTransaction record(s)")
        else:
            db.commit()
            print(f"COMMITTED — {created} BankTransaction record(s) created.")

        print(f"  Already existed  : {skipped_existing}")
        print(f"  No bank account  : {skipped_no_bank} (cash/credit/unpaid purchases)")

        if dry_run and created > 0:
            print()
            print("Run with --commit to apply these changes.")

    except Exception as e:
        db.rollback()
        print(f"\nERROR: {e}")
        raise
    finally:
        db.close()


if __name__ == "__main__":
    main()
