from datetime import date, datetime, timedelta
from decimal import Decimal
from sqlalchemy.orm import Session
from sqlalchemy import func
from app.services.accounting import PostingService, AccountingError, d
from app.models.finance import Account, JournalEntry, Payment
from app.models.accounting import PaymentAllocation, CashbookMovement, InventoryLot
from app.models.sale import Sale
from app.models.purchase import GoodsReceiptNote
from app.models.customer import Customer
from app.models.supplier import Supplier
from app.models.branch import Branch
from app.models.inventory import InventoryItem, InventoryTransaction
from app.services.inventory_costing import inventory_in, peek_fifo, record_out

SALE_STATUS_POST = {"completed", "paid", "confirmed", "closed"}
REVENUE_MAP = {
    "retail": "4010",
    "wholesale": "4020",
    "agro": "4030",
    "seed": "4030",
    "input": "4030",
}
PLANT_CATEGORIES = {
    "plant", "plants", "flower", "flowering", "tree", "herb",
    "succulent", "ornamental", "foliage", "shrub",
}


def get_account(db: Session, code: str) -> Account | None:
    return db.query(Account).filter(Account.code == code).first()


def resolve_cash_account(db: Session, branch_id: str | None, method: str | None) -> Account:
    prefix = "1010" if (method or "cash").lower() in ("cash", "") else "1020"
    parent = get_account(db, prefix)
    if branch_id:
        branch = db.query(Branch).filter(Branch.id == branch_id).first()
        if branch:
            child = get_account(db, f"{prefix}-{branch.code}")
            if child:
                return child
    if not parent:
        raise AccountingError(f"COA account {prefix} is missing; seed the chart of accounts")
    return parent


def _inventory_mapping(item: InventoryItem) -> tuple[str, str]:
    if (item.category or "").lower() in PLANT_CATEGORIES:
        return "1040", "5010"
    return "1045", "5020"


def sale_posting_entry(db: Session, sale: Sale) -> JournalEntry | None:
    return db.query(JournalEntry).filter(
        JournalEntry.source == "sale",
        JournalEntry.reference_type == "sale",
        JournalEntry.reference_id == sale.id,
        JournalEntry.is_reversal == False,  # noqa: E712
    ).first()


def _allocated(db: Session, doc_type: str, doc_id: str) -> Decimal:
    return d(db.query(func.coalesce(func.sum(PaymentAllocation.amount), 0)).filter(
        PaymentAllocation.document_type == doc_type,
        PaymentAllocation.document_id == doc_id,
    ).scalar())


def sale_remaining(db: Session, sale: Sale) -> Decimal:
    return d(sale.due) - _allocated(db, "sale", sale.id)


def post_sale(db: Session, sale_id: str, user_id: str) -> JournalEntry:
    sale = db.query(Sale).filter(Sale.id == sale_id).first()
    if not sale:
        raise AccountingError("Sale not found")
    existing = sale_posting_entry(db, sale)
    if existing:
        if existing.status == "posted":
            return existing
        raise AccountingError("A draft posting already exists for this sale")
    if sale.status == "returned":
        raise AccountingError("A returned sale cannot be posted")

    cash_acct = resolve_cash_account(db, sale.branch_id, sale.payment_method)
    lines = []
    paid = d(sale.paid)
    due = d(sale.due)
    if paid > 0:
        lines.append({"account_id": cash_acct.id, "debit": paid})
    if due > 0:
        ar = get_account(db, "1030")
        if not ar:
            raise AccountingError("COA account 1030 (Accounts Receivable) is missing")
        lines.append({"account_id": ar.id, "debit": due})
    gross = d(Decimal(str(sale.subtotal or 0)) - Decimal(str(sale.discount or 0)))
    rev = get_account(db, REVENUE_MAP.get((sale.sale_type or "retail").lower(), "4010"))
    if not rev:
        raise AccountingError("Sales revenue account not found for this sale type")
    lines.append({"account_id": rev.id, "credit": gross})
    tax = d(sale.tax or 0)
    if tax > 0:
        vat = get_account(db, "2020")
        if vat:
            lines.append({"account_id": vat.id, "credit": tax})

    plans = {}
    for si in sale.items:
        if not si.inventory_id:
            continue
        inv = db.query(InventoryItem).filter(InventoryItem.id == si.inventory_id).first()
        if not inv:
            continue
        plans[si.inventory_id] = (inv, peek_fifo(db, inv.id, si.quantity, sale.branch_id))

    for inv, planned in plans.values():
        cogs = d(sum(p["value"] for p in planned))
        if cogs <= 0:
            continue
        inv_code, cogs_code = _inventory_mapping(inv)
        inv_acct = get_account(db, inv_code)
        cogs_acct = get_account(db, cogs_code)
        if inv_acct and cogs_acct:
            lines.append({"account_id": inv_acct.id, "credit": cogs})
            lines.append({"account_id": cogs_acct.id, "debit": cogs})

    svc = PostingService(db)
    entry = svc.post_event(
        source="sale", reference_type="sale", reference_id=sale.id,
        entry_date=sale.date, branch_id=sale.branch_id,
        lines=lines, description=f"Sale {sale.invoice_number}", created_by=user_id,
    )
    for inv, planned in plans.items():
        item, planned_rows = plans[inv]
        record_out(
            db, item_id=item.id, planned=planned_rows, branch_id=sale.branch_id,
            reference_type="sale", reference_id=sale.id, created_by=user_id,
            notes=f"Sale {sale.invoice_number}", adjust_quantity=False,
            journal_entry_id=entry.id,
        )
    return entry


def return_sale(db: Session, sale_id: str, user_id: str, reason: str | None = None) -> JournalEntry:
    sale = db.query(Sale).filter(Sale.id == sale_id).first()
    if not sale:
        raise AccountingError("Sale not found")
    entry = sale_posting_entry(db, sale)
    if not entry or entry.status != "posted":
        raise AccountingError("Sale is not posted")
    if sale.status == "returned":
        raise AccountingError("Sale has already been returned")
    reversal = PostingService(db).reverse_entry(entry.id, user_id, reason or "Sale return")
    for si in sale.items:
        if not si.inventory_id:
            continue
        out_txs = db.query(InventoryTransaction).filter(
            InventoryTransaction.item_id == si.inventory_id,
            InventoryTransaction.type == "out",
            InventoryTransaction.reference_type == "sale",
            InventoryTransaction.reference_id == sale.id,
        ).all()
        quantity = sum(int(t.quantity) for t in out_txs)
        value = sum(d(t.value or 0) for t in out_txs)
        if quantity <= 0:
            continue
        unit_cost = d(value / Decimal(quantity)) if value > 0 else None
        inventory_in(
            db, item_id=si.inventory_id, quantity=quantity, unit_cost=unit_cost,
            received_date=sale.date, branch_id=sale.branch_id,
            reference_type="sale_return", reference_id=sale.id, created_by=user_id,
            notes=f"Return of {sale.invoice_number}", adjust_quantity=True,
        )
    sale.status = "returned"
    if sale.customer_id:
        customer = db.query(Customer).filter(Customer.id == sale.customer_id).first()
        if customer:
            customer.outstanding = max(Decimal("0"), d(customer.outstanding) - d(sale.due))
    return reversal


def grn_posting_entry(db: Session, grn: GoodsReceiptNote) -> JournalEntry | None:
    return db.query(JournalEntry).filter(
        JournalEntry.source == "purchase",
        JournalEntry.reference_type == "grn",
        JournalEntry.reference_id == grn.id,
        JournalEntry.is_reversal == False,  # noqa: E712
    ).first()


def post_grn(db: Session, grn_id: str, user_id: str) -> JournalEntry:
    grn = db.query(GoodsReceiptNote).filter(GoodsReceiptNote.id == grn_id).first()
    if not grn:
        raise AccountingError("GRN not found")
    existing = grn_posting_entry(db, grn)
    if existing:
        if existing.status == "posted":
            return existing
        raise AccountingError("A draft posting already exists for this GRN")

    lines = {}
    total = Decimal("0")
    for gi in grn.items:
        amount = d(gi.total)
        total += amount
        if gi.inventory_id and gi.quantity > 0:
            inventory_in(
                db, item_id=gi.inventory_id, quantity=gi.quantity, unit_cost=gi.unit_price,
                received_date=grn.received_date, branch_id=grn.branch_id,
                reference_type="grn", reference_id=grn.id, created_by=user_id,
                notes=f"GRN {grn.grn_number}",
            )
            item = db.query(InventoryItem).filter(InventoryItem.id == gi.inventory_id).first()
            if item:
                inv_code, _ = _inventory_mapping(item)
                inv_acct = get_account(db, inv_code)
                if inv_acct:
                    lines[inv_acct.id] = d(lines.get(inv_acct.id, 0)) + amount
    if total <= 0:
        raise AccountingError("GRN has zero value; nothing to post")

    ap = get_account(db, "2010")
    if not ap:
        raise AccountingError("COA account 2010 (Accounts Payable) is missing")
    journal_lines = [{"account_id": aid, "debit": amount} for aid, amount in lines.items()]
    journal_lines.append({"account_id": ap.id, "credit": total})

    supplier_name = ""
    if grn.supplier_id:
        supplier = db.query(Supplier).filter(Supplier.id == grn.supplier_id).first()
        supplier_name = supplier.name if supplier else ""

    entry = PostingService(db).post_event(
        source="purchase", reference_type="grn", reference_id=grn.id,
        entry_date=grn.received_date, branch_id=grn.branch_id,
        lines=journal_lines,
        description=f"GRN {grn.grn_number} - {supplier_name}",
        created_by=user_id,
    )
    for tx in db.query(InventoryTransaction).filter(
        InventoryTransaction.reference_type == "grn",
        InventoryTransaction.reference_id == grn.id,
        InventoryTransaction.type == "in",
    ).all():
        tx.journal_entry_id = entry.id
    supplier = db.query(Supplier).filter(Supplier.id == grn.supplier_id).first()
    if supplier:
        supplier.balance = float(d(Decimal(str(supplier.balance))) + total)
    return entry


def _next_movement_number(db: Session) -> str:
    year = datetime.now().year
    prefix = f"CB-{year}-"
    last = db.query(CashbookMovement).filter(
        CashbookMovement.movement_number.like(f"{prefix}%")
    ).order_by(CashbookMovement.movement_number.desc()).first()
    seq = (int(last.movement_number.split("-")[-1]) + 1) if last else 1
    return f"{prefix}{seq:06d}"


def _party_open_docs(db: Session, party_type: str, party_id: str) -> list:
    docs = []
    if party_type == "customer":
        for sale in db.query(Sale).filter(Sale.customer_id == party_id).order_by(
                Sale.date, Sale.created_at).all():
            entry = sale_posting_entry(db, sale)
            if not entry or entry.status != "posted":
                continue
            rem = max(Decimal("0"), sale_remaining(db, sale))
            if rem > 0:
                docs.append({"type": "sale", "doc": sale, "date": sale.date, "remaining": rem})
    elif party_type == "supplier":
        for grn in db.query(GoodsReceiptNote).filter(
                GoodsReceiptNote.supplier_id == party_id).order_by(
                GoodsReceiptNote.received_date, GoodsReceiptNote.created_at).all():
            entry = grn_posting_entry(db, grn)
            if not entry or entry.status != "posted":
                continue
            rem = max(Decimal("0"), d(grn.total) - _allocated(db, "grn", grn.id))
            if rem > 0:
                docs.append({"type": "grn", "doc": grn, "date": grn.received_date, "remaining": rem})
    return docs


def post_payment(db: Session, payment_id: str, user_id: str) -> JournalEntry:
    payment = db.query(Payment).filter(Payment.id == payment_id).first()
    if not payment:
        raise AccountingError("Payment not found")
    existing = db.query(JournalEntry).filter(
        JournalEntry.source == "payment",
        JournalEntry.reference_type == "payment",
        JournalEntry.reference_id == payment.id,
        JournalEntry.is_reversal == False,  # noqa: E712
    ).first()
    if existing:
        if existing.status == "posted":
            return existing
        raise AccountingError("A draft posting already exists for this payment")

    amount = d(payment.amount)
    if amount <= 0:
        raise AccountingError("Payment amount must be positive")
    cash_acct = resolve_cash_account(db, payment.branch_id, payment.method)
    lines = []
    if payment.party_type == "customer":
        ar = get_account(db, "1030")
        if not ar:
            raise AccountingError("COA account 1030 is missing")
        lines.append({"account_id": cash_acct.id, "debit": amount})
        lines.append({"account_id": ar.id, "credit": amount})
    elif payment.party_type == "supplier":
        ap = get_account(db, "2010")
        if not ap:
            raise AccountingError("COA account 2010 is missing")
        lines.append({"account_id": ap.id, "debit": amount})
        lines.append({"account_id": cash_acct.id, "credit": amount})
    else:
        raise AccountingError("party_type must be 'customer' or 'supplier'")

    entry = PostingService(db).post_event(
        source="payment", reference_type="payment", reference_id=payment.id,
        entry_date=payment.date, branch_id=payment.branch_id,
        lines=lines, description=f"Payment {payment.payment_number}",
        created_by=user_id,
    )

    remaining = amount
    for row in _party_open_docs(db, payment.party_type, payment.party_id):
        if remaining <= 0:
            break
        take = min(row["remaining"], remaining)
        db.add(PaymentAllocation(
            payment_id=payment.id,
            document_type=row["type"],
            document_id=row["doc"].id,
            amount=take,
        ))
        remaining -= take

    db.add(CashbookMovement(
        movement_number=_next_movement_number(db),
        type="receipt" if payment.party_type == "customer" else "payment",
        account_id=cash_acct.id,
        amount=amount,
        movement_date=payment.date,
        branch_id=payment.branch_id,
        journal_entry_id=entry.id,
        reference_type="payment",
        reference_id=payment.id,
        notes=payment.notes or f"Payment {payment.payment_number}",
        created_by=user_id,
    ))

    if payment.party_type == "customer":
        customer = db.query(Customer).filter(Customer.id == payment.party_id).first()
        if customer:
            customer.outstanding = max(Decimal("0"), d(customer.outstanding) - amount)
    elif payment.party_type == "supplier":
        supplier = db.query(Supplier).filter(Supplier.id == payment.party_id).first()
        if supplier:
            supplier.balance = max(Decimal("0"), d(Decimal(str(supplier.balance))) - amount)
    return entry


def reverse_payment(db: Session, payment_id: str, user_id: str, reason: str | None = None) -> JournalEntry:
    payment = db.query(Payment).filter(Payment.id == payment_id).first()
    if not payment:
        raise AccountingError("Payment not found")
    entry = db.query(JournalEntry).filter(
        JournalEntry.source == "payment",
        JournalEntry.reference_type == "payment",
        JournalEntry.reference_id == payment.id,
        JournalEntry.is_reversal == False,  # noqa: E712
    ).first()
    if not entry or entry.status != "posted":
        raise AccountingError("Payment is not posted")
    reversal = PostingService(db).reverse_entry(entry.id, user_id, reason or "Payment reversal")
    for alloc in db.query(PaymentAllocation).filter(PaymentAllocation.payment_id == payment.id).all():
        db.delete(alloc)
    amount = d(payment.amount)
    if payment.party_type == "customer":
        customer = db.query(Customer).filter(Customer.id == payment.party_id).first()
        if customer:
            customer.outstanding = d(customer.outstanding) + amount
    elif payment.party_type == "supplier":
        supplier = db.query(Supplier).filter(Supplier.id == payment.party_id).first()
        if supplier:
            supplier.balance = d(Decimal(str(supplier.balance))) + amount
    return reversal


def _age_bucket(age_days: int) -> str:
    if age_days <= 30:
        return "bucket_30"
    if age_days <= 60:
        return "bucket_60"
    if age_days <= 90:
        return "bucket_90"
    return "bucket_91"


def party_aging(db: Session, party_type: str, as_of: date | None = None) -> list:
    if party_type not in ("customer", "supplier"):
        raise AccountingError("party_type must be 'customer' or 'supplier'")
    as_of = as_of or date.today()
    rows = []
    if party_type == "customer":
        parties = db.query(Customer).filter(Customer.is_active == True).order_by(Customer.name).all()  # noqa: E712
    else:
        parties = db.query(Supplier).filter(Supplier.is_active == True).order_by(Supplier.name).all()  # noqa: E712
    for party in parties:
        docs = _party_open_docs(db, party_type, party.id)
        if not docs:
            continue
        buckets = {"bucket_30": Decimal("0"), "bucket_60": Decimal("0"),
                   "bucket_90": Decimal("0"), "bucket_91": Decimal("0")}
        balance = Decimal("0")
        for row in docs:
            balance += row["remaining"]
            buckets[_age_bucket(max(0, (as_of - row["date"]).days))] += row["remaining"]
        rows.append({
            "party_id": party.id,
            "party_name": party.name,
            "party_code": party.code,
            "balance": float(d(balance)),
            **{k: float(d(v)) for k, v in buckets.items()},
        })
    return rows


def party_ledger(db: Session, party_type: str, party_id: str) -> list:
    if party_type not in ("customer", "supplier"):
        raise AccountingError("party_type must be 'customer' or 'supplier'")
    events = []
    if party_type == "customer":
        for sale in db.query(Sale).filter(Sale.customer_id == party_id).order_by(
                Sale.date, Sale.created_at).all():
            entry = sale_posting_entry(db, sale)
            if not entry or entry.status != "posted":
                continue
            events.append({
                "date": sale.date,
                "document_type": "sale",
                "document_number": sale.invoice_number,
                "reference": entry.entry_number,
                "description": f"Invoice {sale.invoice_number}",
                "amount": float(d(sale.due)),
                "balance": float(d(sale.due)),
            })
        for payment in db.query(Payment).filter(
                Payment.party_type == "customer", Payment.party_id == party_id).order_by(
                Payment.date, Payment.created_at).all():
            entry = db.query(JournalEntry).filter(
                JournalEntry.source == "payment",
                JournalEntry.reference_type == "payment",
                JournalEntry.reference_id == payment.id,
                JournalEntry.is_reversal == False,  # noqa: E712
            ).first()
            if not entry or entry.status != "posted":
                continue
            events.append({
                "date": payment.date,
                "document_type": "payment",
                "document_number": payment.payment_number,
                "reference": entry.entry_number,
                "description": f"Payment received {payment.payment_number}",
                "amount": float(d(payment.amount)),
                "balance": float(-d(payment.amount)),
            })
    else:
        for grn in db.query(GoodsReceiptNote).filter(
                GoodsReceiptNote.supplier_id == party_id).order_by(
                GoodsReceiptNote.received_date, GoodsReceiptNote.created_at).all():
            entry = grn_posting_entry(db, grn)
            if not entry or entry.status != "posted":
                continue
            events.append({
                "date": grn.received_date,
                "document_type": "grn",
                "document_number": grn.grn_number,
                "reference": entry.entry_number,
                "description": f"Goods receipt {grn.grn_number}",
                "amount": float(d(grn.total)),
                "balance": float(d(grn.total)),
            })
        for payment in db.query(Payment).filter(
                Payment.party_type == "supplier", Payment.party_id == party_id).order_by(
                Payment.date, Payment.created_at).all():
            entry = db.query(JournalEntry).filter(
                JournalEntry.source == "payment",
                JournalEntry.reference_type == "payment",
                JournalEntry.reference_id == payment.id,
                JournalEntry.is_reversal == False,  # noqa: E712
            ).first()
            if not entry or entry.status != "posted":
                continue
            events.append({
                "date": payment.date,
                "document_type": "payment",
                "document_number": payment.payment_number,
                "reference": entry.entry_number,
                "description": f"Payment sent {payment.payment_number}",
                "amount": float(d(payment.amount)),
                "balance": float(-d(payment.amount)),
            })
    events.sort(key=lambda e: (e["date"], e["document_number"]))
    running = Decimal("0")
    for ev in events:
        running += d(ev["balance"])
        ev["running_balance"] = float(d(running))
    return events