#!/usr/bin/env python3
"""
Backfill transactions and invoices for orphaned checkout payment requests.

These PRs were submitted through the checkout public flow before the inline
transaction creation was in place. Kafka was unreachable and no listener
existed to create transactions from checkout PRs, so only the PaymentRequest
was ever committed.

Customer IDs are resolved from the checkout_activities table (actor_customer_id
matched by timestamp to the payment_request created_at).

Usage:
    cd /var/www/html/hubwallet/hubwallet-api
    python scripts/backfill_checkout_transactions_invoices.py
"""
import sys
import uuid

sys.path.insert(0, "/var/www/html/hubwallet/hubwallet-api")

from sqlalchemy import select

from src.core.database import SessionLocal
from src.apps.transactions.models.transactions import Transactions
from src.apps.transactions.services import generate_txn_literal
from src.apps.invoices.models.invoice import Invoice
from src.apps.invoices.services.invoice_services import create_invoice
from src.apps.payment_requests.models.payment_request import PaymentRequest
from src.core.utils.enums import (
    TransactionStatusTypes,
    TransactionCategories,
    TransactionTypes,
    TransactionSourceTypes,
    InvoiceStatusTypes,
)
import src.core.database as _db_module
_db_module.register_models()

# PR id → customer_id from checkout_activities (actor_customer_id matched by timestamp)
PR_CUSTOMER_MAP = {
    736: 257,
    737: 258,
    738: 259,
    739: 260,
    741: 257,
}


def backfill():
    db = SessionLocal()
    created_txns = []
    created_invs = []

    try:
        for pr_id, customer_id in PR_CUSTOMER_MAP.items():
            pr = db.execute(
                select(PaymentRequest).where(PaymentRequest.id == pr_id)
            ).scalar_one_or_none()

            if pr is None:
                print(f"[SKIP] PR {pr_id} not found in DB")
                continue

            # ── Transaction ────────────────────────────────────────────────────
            existing_txn = db.execute(
                select(Transactions).where(Transactions.payment_request_id == pr_id)
            ).scalar_one_or_none()

            if existing_txn is not None:
                print(f"[SKIP] Transaction already exists for PR {pr_id}: {existing_txn.txn_literal}")
                transaction = existing_txn
            else:
                txn_literal = generate_txn_literal(db)
                mock_txn_id = f"chk_{uuid.uuid4().hex[:12]}"
                transaction = Transactions(
                    txn_amount=float(pr.amount),
                    txn_type="charge",
                    txn_status=TransactionStatusTypes.PAID,
                    txn_id=mock_txn_id,
                    txn_literal=txn_literal,
                    currency=pr.currency or "USD",
                    description="Checkout payment (backfilled)",
                    category=TransactionCategories.CHARGE,
                    transaction_type=TransactionTypes.CHECKOUT,
                    txn_source=TransactionSourceTypes.CHECKOUT,
                    payment_request_id=pr.id,
                    merchant_id=pr.merchant_id,
                    customer_id=customer_id,
                )
                db.add(transaction)
                db.flush()
                created_txns.append((pr_id, txn_literal))
                print(f"[CREATE] Transaction {txn_literal} for PR {pr_id} (customer {customer_id})")

            # ── Invoice ────────────────────────────────────────────────────────
            existing_inv = db.execute(
                select(Invoice).where(
                    Invoice.payment_request_id == pr_id,
                    Invoice.deleted_at.is_(None),
                )
            ).scalar_one_or_none()

            if existing_inv is not None:
                print(f"[SKIP] Invoice already exists for PR {pr_id}: {existing_inv.invoice_literal}")
            else:
                invoice = create_invoice(
                    db=db,
                    payment_request=pr,
                    merchant_id=pr.merchant_id,
                    customer_id=customer_id,
                    amount=float(pr.amount),
                    status=InvoiceStatusTypes.PENDING,
                    due_date=getattr(pr, "due_date", None),
                )
                created_invs.append((pr_id, invoice.invoice_literal))
                print(f"[CREATE] Invoice {invoice.invoice_literal} for PR {pr_id} (customer {customer_id})")

        db.commit()
        print()
        print(f"Done. Created {len(created_txns)} transaction(s), {len(created_invs)} invoice(s).")
        if created_txns:
            print("Transactions:", ", ".join(lit for _, lit in created_txns))
        if created_invs:
            print("Invoices:    ", ", ".join(lit for _, lit in created_invs))

    except Exception as exc:
        db.rollback()
        print(f"\n[ERROR] Rolled back. {exc}")
        raise
    finally:
        db.close()


if __name__ == "__main__":
    backfill()