"""Phase 5 — Nursery production cost accounting.

Capitalizes direct batch costs into WIP (1050), transfers WIP→plant inventory
(1040) when a batch is marked ready (creating a FIFO lot), posts mortality to
6130, refreshes passport production cost, and produces a batch profitability
report from the FIFO lots actually drained by plant sales.
"""
from __future__ import annotations
from datetime import date, datetime
from decimal import Decimal, ROUND_HALF_UP
from sqlalchemy.orm import Session
from app.models.production import ProductionBatch
from app.models.plant import Plant, PlantCategory
from app.models.inventory import InventoryItem, InventoryTransaction
from app.models.finance import Account, JournalEntry
from app.models.accounting import ProductionCostEntry, InventoryLot, JournalLine
from app.models.passport import PlantPassport
from app.models.sale import SaleItem
from app.services.accounting import PostingService, AccountingError, d
from app.services.transaction_postings import resolve_cash_account, get_account
from app.services.inventory_costing import inventory_in, peek_fifo, record_out

WIP_CODE = "1050"
PLANT_INVENTORY_CODE = "1040"
ACCESSORY_INVENTORY_CODE = "1045"
MORTALITY_EXPENSE_CODE = "6130"

COST_TYPES = {
    "seed", "media", "fertilizer", "treatment", "labor", "water",
    "electricity", "overhead", "mortality", "other",
}

PLANTISH_CATEGORIES = {
    "plant", "plants", "flower", "flowering", "tree", "herb",
    "succulent", "ornamental", "foliage", "shrub",
}


class ProductionCostingError(AccountingError):
    pass


def _inventory_mapping(item: InventoryItem) -> str:
    if (item.category or "").lower() in PLANTISH_CATEGORIES:
        return PLANT_INVENTORY_CODE
    return ACCESSORY_INVENTORY_CODE


def batch_net_wip(db: Session, batch_id: str) -> Decimal:
    """Net capitalized cost on 1050 for a batch (reversals offset)."""
    lines = db.query(JournalLine).filter(
        JournalLine.production_batch_id == batch_id).all()
    total = Decimal("0")
    for ln in lines:
        total += d(ln.debit) - d(ln.credit)
    return d(total)


def get_or_create_plant_inventory_item(db: Session, plant: Plant,
                                       branch_id: str | None) -> InventoryItem:
    """Map a Plant to an InventoryItem by code so batch-ready lots and sale
    COGS share the same FIFO ledger. Creates one on first use."""
    item = db.query(InventoryItem).filter(InventoryItem.code == plant.code).first()
    if item:
        return item
    category_text = ""
    if plant.category_id:
        cat = db.query(PlantCategory).filter(PlantCategory.id == plant.category_id).first()
        category_text = (cat.name or "").lower() if cat else ""
    item = InventoryItem(
        code=plant.code,
        name=plant.name,
        category=category_text or "plant",
        unit="piece",
        quantity=0,
        min_quantity=0,
        cost_price=float(plant.retail_price or 0),
        sell_price=float(plant.retail_price or 0),
        branch_id=branch_id,
    )
    db.add(item)
    db.flush()
    return item


def refresh_passport_costs(db: Session, batch: ProductionBatch,
                           unit_cost: Decimal) -> None:
    """Service-boundary update of financial passport fields (design §16)."""
    plant = db.query(Plant).filter(Plant.id == batch.plant_id).first()
    for p in db.query(PlantPassport).filter(PlantPassport.batch_id == batch.id).all():
        p.production_cost = float(d(unit_cost))
        if plant and plant.retail_price:
            p.selling_price = float(plant.retail_price)
        p.profit_margin = round(float(d(Decimal(str(p.selling_price or 0))
                                          - d(unit_cost))), 2)


def post_batch_cost(db: Session, *, batch_id: str, cost_type: str,
                    amount: Decimal | None = None, quantity: Decimal | None = None,
                    unit: str | None = None, inventory_item_id: str | None = None,
                    account_id: str | None = None, reference_type: str | None = None,
                    reference_id: str | None = None, cost_center: str | None = None,
                    notes: str | None = None, cost_date: date | None = None,
                    created_by: str) -> tuple[ProductionCostEntry, JournalEntry]:
    if cost_type not in COST_TYPES:
        raise ProductionCostingError(f"Unsupported cost type '{cost_type}'")
    batch = db.query(ProductionBatch).filter(ProductionBatch.id == batch_id).first()
    if not batch:
        raise ProductionCostingError("Production batch not found")
    wip = get_account(db, WIP_CODE)
    if not wip:
        raise ProductionCostingError("COA account 1050 (WIP) is missing")

    planned = []
    if inventory_item_id:
        qty = d(quantity or 0)
        if qty <= 0:
            raise ProductionCostingError("quantity is required when consuming inventory")
        item = db.query(InventoryItem).filter(InventoryItem.id == inventory_item_id).first()
        if not item:
            raise ProductionCostingError("Inventory item not found")
        planned = peek_fifo(db, item.id, qty, batch.branch_id)
        debit = d(sum(p["value"] for p in planned))
        if debit <= 0:
            raise ProductionCostingError("Consumed inventory has zero cost")
        contra = get_account(db, _inventory_mapping(item))
        if not contra:
            raise ProductionCostingError("COA inventory account is missing")
        amount = debit
    else:
        debit = d(amount or 0)
        if debit <= 0:
            raise ProductionCostingError("Amount must be positive")
        if account_id:
            contra = db.query(Account).filter(Account.id == account_id).first()
            if not contra:
                raise ProductionCostingError("Funding account not found")
        else:
            contra = resolve_cash_account(db, batch.branch_id, "cash")

    entry = ProductionCostEntry(
        batch_id=batch.id,
        cost_type=cost_type,
        amount=float(debit),
        quantity=float(d(quantity or 0)),
        unit=unit,
        reference_type=reference_type,
        reference_id=reference_id,
        allocated_to_qty=float(batch.current_quantity or 0),
        cost_center=cost_center,
        notes=notes,
        created_by=created_by,
    )
    db.add(entry)
    db.flush()

    lines = [
        {"account_id": wip.id, "debit": debit, "production_batch_id": batch.id},
        {"account_id": contra.id, "credit": debit},
    ]
    jentry = PostingService(db).post_event(
        source="production", reference_type="batch_cost", reference_id=entry.id,
        entry_date=cost_date or date.today(), branch_id=batch.branch_id,
        lines=lines, description=f"Production cost {batch.batch_number} - {cost_type}",
        created_by=created_by,
    )

    if inventory_item_id:
        record_out(
            db, item_id=item.id, planned=planned,
            branch_id=batch.branch_id, reference_type="production_cost",
            reference_id=entry.id, created_by=created_by,
            notes=f"Batch {batch.batch_number} {cost_type}", adjust_quantity=True,
            journal_entry_id=jentry.id,
        )

    entry.journal_line_id = jentry.id
    db.flush()
    unit_cost = batch_unit_cost(db, batch)
    if unit_cost is not None:
        refresh_passport_costs(db, batch, unit_cost)
    db.flush()
    return entry, jentry


def batch_unit_cost(db: Session, batch: ProductionBatch) -> Decimal | None:
    qty = Decimal(batch.current_quantity or 0)
    if qty <= 0:
        return None
    return d(batch_net_wip(db, batch.id) / qty)


def mark_batch_ready(db: Session, *, batch_id: str, user_id: str,
                     ready_date: date | None = None,
                     transfer_qty: Decimal | None = None) -> dict:
    batch = db.query(ProductionBatch).filter(ProductionBatch.id == batch_id).first()
    if not batch:
        raise ProductionCostingError("Production batch not found")
    existing = db.query(JournalEntry).filter(
        JournalEntry.source == "production",
        JournalEntry.reference_type == "ready",
        JournalEntry.reference_id == batch.id,
        JournalEntry.is_reversal == False,  # noqa: E712
    ).first()
    if existing:
        if existing.status == "posted":
            raise ProductionCostingError("Batch has already been marked ready")
        raise ProductionCostingError("A draft ready posting already exists")
    qty = int(transfer_qty or batch.current_quantity or 0)
    if qty <= 0:
        raise ProductionCostingError("Transfer quantity must be positive")
    wip_net = batch_net_wip(db, batch.id)
    if wip_net <= 0:
        raise ProductionCostingError(
            "No capitalized WIP for this batch; post production costs first")
    unit_cost = d(wip_net / Decimal(qty))

    plant = db.query(Plant).filter(Plant.id == batch.plant_id).first()
    if not plant:
        raise ProductionCostingError("Batch plant not found")
    item = get_or_create_plant_inventory_item(db, plant, batch.branch_id)
    inv_acct = get_account(db, PLANT_INVENTORY_CODE)
    wip_acct = get_account(db, WIP_CODE)
    if not inv_acct or not wip_acct:
        raise ProductionCostingError("COA accounts 1040/1050 are missing")

    lines = [
        {"account_id": inv_acct.id, "debit": wip_net},
        {"account_id": wip_acct.id, "credit": wip_net, "production_batch_id": batch.id},
    ]
    jentry = PostingService(db).post_event(
        source="production", reference_type="ready", reference_id=batch.id,
        entry_date=ready_date or date.today(), branch_id=batch.branch_id,
        lines=lines, description=f"Batch ready {batch.batch_number}",
        created_by=user_id,
    )
    lot = inventory_in(
        db, item_id=item.id, quantity=qty, unit_cost=unit_cost,
        received_date=ready_date or date.today(), branch_id=batch.branch_id,
        batch_id=batch.id, reference_type="production_ready",
        reference_id=batch.id, created_by=user_id,
        notes=f"Ready {batch.batch_number}", journal_entry_id=jentry.id,
    )
    batch.status = "ready"
    db.flush()
    refresh_passport_costs(db, batch, unit_cost)
    db.flush()
    return {
        "batch_id": batch.id,
        "batch_number": batch.batch_number,
        "journal_entry_id": jentry.id,
        "entry_number": jentry.entry_number,
        "inventory_item_id": item.id,
        "lot_id": lot.id,
        "lot_number": lot.lot_number,
        "transfer_qty": qty,
        "unit_cost": float(unit_cost),
        "total_value": float(wip_net),
    }


def post_batch_mortality(db: Session, *, batch_id: str, user_id: str,
                         quantity: Decimal, reason: str | None = None,
                         cost_date: date | None = None) -> dict:
    batch = db.query(ProductionBatch).filter(ProductionBatch.id == batch_id).first()
    if not batch:
        raise ProductionCostingError("Production batch not found")
    qty = int(quantity)
    if qty <= 0:
        raise ProductionCostingError("Mortality quantity must be positive")
    if qty > (batch.current_quantity or 0):
        raise ProductionCostingError("Mortality exceeds current batch quantity")

    ready_entry = db.query(JournalEntry).filter(
        JournalEntry.source == "production",
        JournalEntry.reference_type == "ready",
        JournalEntry.reference_id == batch.id,
        JournalEntry.is_reversal == False,  # noqa: E712
    ).first()
    is_ready = bool(ready_entry and ready_entry.status == "posted")

    entry = ProductionCostEntry(
        batch_id=batch.id,
        cost_type="mortality",
        amount=0.0,
        quantity=float(qty),
        unit="piece",
        notes=reason,
        allocated_to_qty=float((batch.current_quantity or 0) - qty),
        created_by=user_id,
    )
    db.add(entry)
    db.flush()

    if is_ready:
        lot = db.query(InventoryLot).filter(
            InventoryLot.batch_id == batch.id,
            InventoryLot.is_active == True,  # noqa: E712
            InventoryLot.remaining_qty > 0,
        ).order_by(InventoryLot.received_date).first()
        if not lot or int(lot.remaining_qty) < qty:
            raise ProductionCostingError("Insufficient inventory lot for this batch")
        unit_cost = d(lot.unit_cost)
        value = d(unit_cost * qty)
        mort = get_account(db, MORTALITY_EXPENSE_CODE)
        inv_acct = get_account(db, PLANT_INVENTORY_CODE)
        if not mort or not inv_acct:
            raise ProductionCostingError("COA accounts 6130/1040 are missing")
        if value > 0:
            lines = [
                {"account_id": mort.id, "debit": value},
                {"account_id": inv_acct.id, "credit": value},
            ]
            jentry = PostingService(db).post_event(
                source="production", reference_type="mortality", reference_id=entry.id,
                entry_date=cost_date or date.today(), branch_id=batch.branch_id,
                lines=lines, description=f"Mortality {batch.batch_number} - {qty} plants",
                created_by=user_id,
            )
            entry.journal_line_id = jentry.id
            entry.amount = float(value)
            planned = [{
                "lot_id": lot.id, "quantity": qty, "unit_cost": unit_cost,
                "value": value, "legacy": False,
            }]
            record_out(
                db, item_id=lot.item_id, planned=planned,
                branch_id=batch.branch_id, reference_type="mortality",
                reference_id=entry.id, created_by=user_id,
                notes=f"Batch {batch.batch_number} mortality", adjust_quantity=True,
                journal_entry_id=jentry.id,
            )
        else:
            db.delete(entry)
            db.flush()
            _drop_lot_qty(db, lot, qty)
            jentry = None
    else:
        unit_cost = batch_unit_cost(db, batch)
        if unit_cost is None:
            raise ProductionCostingError("No cost basis to value mortality")
        value = d(unit_cost * qty)
        mort = get_account(db, MORTALITY_EXPENSE_CODE)
        wip_acct = get_account(db, WIP_CODE)
        if not mort or not wip_acct:
            raise ProductionCostingError("COA accounts 6130/1050 are missing")
        lines = [
            {"account_id": mort.id, "debit": value},
            {"account_id": wip_acct.id, "credit": value, "production_batch_id": batch.id},
        ]
        jentry = PostingService(db).post_event(
            source="production", reference_type="mortality", reference_id=entry.id,
            entry_date=cost_date or date.today(), branch_id=batch.branch_id,
            lines=lines, description=f"Mortality {batch.batch_number} - {qty} plants",
            created_by=user_id,
        )
        entry.journal_line_id = jentry.id
        entry.amount = float(value)

    batch.current_quantity = max(0, (batch.current_quantity or 0) - qty)
    db.flush()
    new_unit = batch_unit_cost(db, batch)
    if new_unit is not None:
        refresh_passport_costs(db, batch, new_unit)
    db.flush()
    return {
        "batch_id": batch.id,
        "batch_number": batch.batch_number,
        "quantity": qty,
        "entry_id": entry.id,
        "amount": float(jentry and (d(entry.amount) if entry.journal_line_id else 0)),
        "journal_entry_id": jentry.id if jentry else None,
        "entry_number": jentry.entry_number if jentry else None,
        "remaining_qty": batch.current_quantity,
    }


def _drop_lot_qty(db: Session, lot: InventoryLot, qty: int) -> None:
    lot.remaining_qty = d(Decimal(str(lot.remaining_qty)) - qty)
    if lot.remaining_qty <= 0:
        lot.remaining_qty = Decimal("0")
        lot.is_active = False
    db.flush()


def list_batch_costs(db: Session, batch_id: str | None = None,
                     batch_ids: list[str] | None = None) -> list:
    q = db.query(ProductionCostEntry)
    if batch_ids:
        q = q.filter(ProductionCostEntry.batch_id.in_(batch_ids))
    elif batch_id:
        q = q.filter(ProductionCostEntry.batch_id == batch_id)
    out = []
    for e in q.order_by(ProductionCostEntry.created_at.desc()).all():
        entry_number = None
        if e.journal_line_id:
            j = db.query(JournalEntry).filter(JournalEntry.id == e.journal_line_id).first()
            entry_number = j.entry_number if j else None
        out.append({
            "id": e.id,
            "batch_id": e.batch_id,
            "cost_type": e.cost_type,
            "amount": float(e.amount),
            "quantity": float(e.quantity) if e.quantity is not None else None,
            "unit": e.unit,
            "reference_type": e.reference_type,
            "reference_id": e.reference_id,
            "cost_center": e.cost_center,
            "notes": e.notes,
            "journal_entry_id": e.journal_line_id,
            "entry_number": entry_number,
            "created_at": e.created_at.isoformat() if e.created_at else None,
        })
    return out


def batch_costing_summary(db: Session, batch: ProductionBatch) -> dict:
    entries = list_batch_costs(db, batch_id=batch.id)
    by_type: dict[str, float] = {}
    posted_total = Decimal("0")
    mortality_qty = Decimal("0")
    mortality_total = Decimal("0")
    for e in entries:
        by_type[e["cost_type"]] = by_type.get(e["cost_type"], 0) + e["amount"]
        if e["journal_entry_id"]:
            posted_total += d(e["amount"])
        if e["cost_type"] == "mortality":
            mortality_total += d(e["amount"])
            mortality_qty += d(e["quantity"] or 0)

    net_wip = batch_net_wip(db, batch.id)
    ready = None
    re = db.query(JournalEntry).filter(
        JournalEntry.source == "production",
        JournalEntry.reference_type == "ready",
        JournalEntry.reference_id == batch.id,
        JournalEntry.is_reversal == False,  # noqa: E712
    ).first()
    if re:
        lot = db.query(InventoryLot).filter(
            InventoryLot.batch_id == batch.id,
            InventoryLot.is_active == True,  # noqa: E712
        ).order_by(InventoryLot.received_date).first()
        qty = lot.received_qty if lot else (batch.current_quantity or 0)
        unit_cost = d(re.lines[0].debit / Decimal(qty)) if re.lines and qty else None
        ready = {
            "posted": re.status == "posted",
            "entry_number": re.entry_number,
            "date": re.entry_date.isoformat() if re.entry_date else None,
            "transfer_qty": float(qty),
            "unit_cost": float(unit_cost) if unit_cost is not None else None,
            "lot_id": lot.id if lot else None,
            "lot_number": lot.lot_number if lot else None,
            "lot_value": float(d(Decimal(str(lot.remaining_qty)) * unit_cost))
                if lot and unit_cost is not None else None,
        }

    unit = batch_unit_cost(db, batch)
    passports = db.query(PlantPassport).filter(
        PlantPassport.batch_id == batch.id).all()
    return {
        "batch_id": batch.id,
        "batch_number": batch.batch_number,
        "plant_id": batch.plant_id,
        "status": batch.status,
        "current_quantity": batch.current_quantity,
        "entries": entries,
        "by_type": by_type,
        "posted_cost": float(posted_total),
        "net_wip": float(net_wip),
        "unit_cost": float(unit) if unit is not None else None,
        "mortality_qty": float(mortality_qty),
        "mortality_total": float(mortality_total),
        "ready": ready,
        "passports": [
            {
                "passport_id": p.passport_id,
                "production_cost": p.production_cost,
                "selling_price": p.selling_price,
                "profit_margin": p.profit_margin,
            } for p in passports
        ],
    }


def batch_lot_ids(db: Session, batch_id: str) -> list[str]:
    return [lot.id for lot in db.query(InventoryLot).filter(
        InventoryLot.batch_id == batch_id).all()]


def batch_profitability_report(db: Session, batch_id: str | None = None) -> list:
    batches = db.query(ProductionBatch).order_by(
        ProductionBatch.created_at.desc()).all()
    rows = []
    for batch in batches:
        lot_ids = batch_lot_ids(db, batch.id)
        sold_qty = Decimal("0")
        sold_cost = Decimal("0")
        attribution: dict[tuple, Decimal] = {}
        sale_ids: set[str] = set()
        item_ids: set[str] = set()
        if lot_ids:
            out_txs = db.query(InventoryTransaction).filter(
                InventoryTransaction.type == "out",
                InventoryTransaction.lot_id.in_(lot_ids),
            ).all()
            for tx in out_txs:
                if tx.reference_type == "sale" and tx.reference_id:
                    sale_ids.add(tx.reference_id)
                    item_ids.add(tx.item_id)
                    sold_qty += d(tx.quantity)
                    sold_cost += d(tx.value or 0)
                    key = (tx.reference_id, tx.item_id)
                    attribution[key] = attribution.get(key, Decimal("0")) + d(tx.value or 0)
            if sale_ids:
                # Total sale COGS per (sale, item) across all lots (used to
                # attribute this batch's share of each sale line's revenue).
                line_cogs: dict[tuple, Decimal] = {}
                all_tx = db.query(InventoryTransaction).filter(
                    InventoryTransaction.type == "out",
                    InventoryTransaction.reference_type == "sale",
                    InventoryTransaction.reference_id.in_(sale_ids),
                ).all()
                for tx in all_tx:
                    line_cogs[(tx.reference_id, tx.item_id)] = (
                        line_cogs.get((tx.reference_id, tx.item_id), Decimal("0"))
                        + d(tx.value or 0))
                line_rev: dict[tuple, Decimal] = {}
                if item_ids:
                    for si in db.query(SaleItem).filter(
                            SaleItem.sale_id.in_(sale_ids),
                            SaleItem.inventory_id.in_(item_ids)).all():
                        key = (si.sale_id, si.inventory_id)
                        line_rev[key] = d(line_rev.get(key, Decimal("0")) + d(
                            Decimal(str(si.unit_price or 0)) * Decimal(si.quantity or 0)
                            - Decimal(str(si.discount or 0))))
                revenue = Decimal("0")
                for key, batch_value in attribution.items():
                    tc = line_cogs.get(key, Decimal("0"))
                    tr = line_rev.get(key, Decimal("0"))
                    if tc > 0:
                        revenue += d(tr * batch_value / tc)
                revenue = d(revenue)
            else:
                revenue = Decimal("0")
        else:
            revenue = Decimal("0")

        summary = batch_costing_summary(db, batch)
        profit = d(revenue - sold_cost)
        ready_qty = (summary["ready"] or {}).get("transfer_qty")
        if ready_qty:
            unit_cost = d(Decimal(summary["posted_cost"]) / Decimal(ready_qty))
        else:
            unit_cost = summary["unit_cost"]
        rows.append({
            "batch_id": batch.id,
            "batch_number": batch.batch_number,
            "plant_id": batch.plant_id,
            "status": batch.status,
            "current_quantity": batch.current_quantity,
            "total_cost": summary["posted_cost"],
            "unit_cost": unit_cost,
            "ready_qty": ready_qty,
            "sold_qty": float(sold_qty),
            "revenue": float(revenue),
            "cost_of_sold": float(sold_cost),
            "profit": float(profit),
            "margin_pct": float(d(sold_cost and revenue / sold_cost * 100 - 100 or 0))
                if sold_cost > 0 else None,
        })
    if batch_id:
        rows = [r for r in rows if r["batch_id"] == batch_id]
    return rows