"""Delete all purchase-module entries (vendors, POs, GRNs, bills, payments, related vouchers/stock)."""

import asyncio
import sys
from decimal import Decimal
from pathlib import Path

from sqlalchemy import delete, select, update

sys.path.insert(0, str(Path(__file__).resolve().parent.parent))

from app.core.database import close_database_connection, connect_to_database, get_session_factory
from app.infrastructure.db.models import (
    ChartOfAccountModel,
    GoodsReceiptModel,
    InventoryBalanceModel,
    InventoryTransactionModel,
    LedgerEntryModel,
    PurchaseOrderModel,
    PurchasePaymentAllocationModel,
    PurchasePaymentModel,
    VendorBillModel,
    VendorModel,
    VoucherEntryModel,
    VoucherModel,
)


async def _reverse_voucher_balances(session, voucher_ids: list[str]) -> None:
    if not voucher_ids:
        return
    entries = (
        await session.execute(
            select(LedgerEntryModel).where(LedgerEntryModel.voucher_id.in_(voucher_ids))
        )
    ).scalars().all()
    deltas: dict[str, tuple[Decimal, Decimal]] = {}
    for entry in entries:
        debit = Decimal(str(entry.debit_amount or 0))
        credit = Decimal(str(entry.credit_amount or 0))
        prev = deltas.get(entry.account_id, (Decimal("0.00"), Decimal("0.00")))
        deltas[entry.account_id] = (prev[0] + debit, prev[1] + credit)

    for account_id, (debit, credit) in deltas.items():
        account = await session.get(ChartOfAccountModel, account_id)
        if not account:
            continue
        # Reverse original post: subtract debit, subtract credit (opposite of update_balance).
        account.current_balance = (
            Decimal(str(account.current_balance or 0)) - debit + credit
        ).quantize(Decimal("0.01"))


async def clear_purchase_data() -> None:
    await connect_to_database()
    factory = get_session_factory()

    async with factory() as session:
        # Collect purchase-related vouchers (auto-created from purchase flow).
        purchase_vouchers = (
            await session.execute(
                select(VoucherModel.id).where(
                    VoucherModel.voucher_type.in_(["purchase", "payment"]),
                    VoucherModel.reference.like("BILL-%")
                    | VoucherModel.reference.like("PAY-%")
                    | VoucherModel.reference.like("GRN-%")
                    | VoucherModel.narration.like("%Vendor bill%")
                    | VoucherModel.narration.like("%Purchase payment%")
                    | VoucherModel.narration.like("%Stock in against%")
                    | VoucherModel.narration.like("%GRN%"),
                )
            )
        ).scalars().all()
        voucher_ids = list(purchase_vouchers)

        # Also include vouchers linked from bills/payments.
        linked = (
            await session.execute(
                select(VendorBillModel.voucher_id).where(VendorBillModel.voucher_id.is_not(None))
            )
        ).scalars().all()
        linked += (
            await session.execute(
                select(PurchasePaymentModel.voucher_id).where(
                    PurchasePaymentModel.voucher_id.is_not(None)
                )
            )
        ).scalars().all()
        for vid in linked:
            if vid and vid not in voucher_ids:
                voucher_ids.append(vid)

        await _reverse_voucher_balances(session, voucher_ids)

        if voucher_ids:
            await session.execute(
                delete(LedgerEntryModel).where(LedgerEntryModel.voucher_id.in_(voucher_ids))
            )
            await session.execute(
                delete(VoucherEntryModel).where(VoucherEntryModel.voucher_id.in_(voucher_ids))
            )
            await session.execute(delete(VoucherModel).where(VoucherModel.id.in_(voucher_ids)))

        # Reverse/remove stock-in transactions from GRN confirm.
        grn_txns = (
            await session.execute(
                select(InventoryTransactionModel).where(
                    InventoryTransactionModel.txn_type == "purchase_receipt"
                )
            )
        ).scalars().all()
        for txn in grn_txns:
            balance = (
                await session.execute(
                    select(InventoryBalanceModel).where(
                        InventoryBalanceModel.company_id == txn.company_id,
                        InventoryBalanceModel.item_id == txn.item_id,
                    )
                )
            ).scalar_one_or_none()
            if balance:
                qty_in = Decimal(str(txn.quantity_in or 0))
                qty_out = Decimal(str(txn.quantity_out or 0))
                balance.quantity_on_hand = (
                    Decimal(str(balance.quantity_on_hand or 0)) - qty_in + qty_out
                ).quantize(Decimal("0.0001"))
                if balance.quantity_on_hand < 0:
                    balance.quantity_on_hand = Decimal("0.0000")

        await session.execute(
            delete(InventoryTransactionModel).where(
                InventoryTransactionModel.txn_type == "purchase_receipt"
            )
        )

        # Delete purchase documents in FK-safe order.
        await session.execute(delete(PurchasePaymentAllocationModel))
        await session.execute(delete(PurchasePaymentModel))
        await session.execute(delete(VendorBillModel))
        await session.execute(delete(GoodsReceiptModel))
        await session.execute(delete(PurchaseOrderModel))
        await session.execute(delete(VendorModel))

        await session.commit()

        counts = {
            "vendors": (await session.execute(select(VendorModel.id))).scalars().all(),
            "purchase_orders": (await session.execute(select(PurchaseOrderModel.id))).scalars().all(),
            "goods_receipts": (await session.execute(select(GoodsReceiptModel.id))).scalars().all(),
            "vendor_bills": (await session.execute(select(VendorBillModel.id))).scalars().all(),
            "payments": (await session.execute(select(PurchasePaymentModel.id))).scalars().all(),
        }
        print("Purchase module cleared.")
        print(f"Deleted purchase vouchers: {len(voucher_ids)}")
        for key, rows in counts.items():
            print(f"Remaining {key}: {len(rows)}")

    await close_database_connection()


if __name__ == "__main__":
    asyncio.run(clear_purchase_data())