from __future__ import annotations

from datetime import date
from decimal import Decimal
from typing import Annotated

from fastapi import APIRouter, Depends, HTTPException, status
from sqlalchemy import func
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session

from app.api.deps import get_current_user, require_roles
from app.db.session import get_db
from app.models.enums import Role
from app.models.master_data import FinancialExpenditureHead, ReportBudgetAllocation
from app.models.procurement import EMD, PaymentTracking, PurchaseOrder
from app.schemas.financial_management import (
    BudgetHeadCreate,
    BudgetHeadUpdate,
    ExpenditureHeadCreate,
    ExpenditureHeadUpdate,
)


router = APIRouter(dependencies=[Depends(get_current_user)])
financial_writer = require_roles(Role.finance, Role.procurement_officer)


def _money(value) -> Decimal:
    return Decimal(str(value or 0))


def _current_financial_year() -> str:
    today = date.today()
    start = today.year if today.month >= 4 else today.year - 1
    return f"{start}-{str(start + 1)[-2:]}"


def _year_bounds(financial_year: str) -> tuple[date, date]:
    try:
        start_year = int(financial_year[:4])
    except (TypeError, ValueError) as exc:
        raise HTTPException(status_code=400, detail="Financial year must use YYYY-YY format.") from exc
    return date(start_year, 4, 1), date(start_year + 1, 3, 31)


def _allocation_values(db: Session, allocation: ReportBudgetAllocation) -> dict:
    start_date, end_date = _year_bounds(allocation.financial_year)
    matching_head = func.lower(func.coalesce(PurchaseOrder.budget_head, "")).in_(
        [allocation.head_code.lower(), allocation.head_name.lower()]
    )
    order_date = func.coalesce(PurchaseOrder.po_date, func.date(PurchaseOrder.created_at))
    committed = _money(
        db.query(func.coalesce(func.sum(func.coalesce(PurchaseOrder.po_value, PurchaseOrder.estimated_cost, 0)), 0))
        .filter(matching_head, order_date >= start_date, order_date <= end_date)
        .scalar()
    )
    paid = _money(
        db.query(func.coalesce(func.sum(PaymentTracking.paid_amount), 0))
        .join(PurchaseOrder, PurchaseOrder.id == PaymentTracking.po_id)
        .filter(matching_head, order_date >= start_date, order_date <= end_date)
        .scalar()
    )
    allocated = _money(allocation.allocation_amount)
    return {
        "id": allocation.id,
        "financial_year": allocation.financial_year,
        "expenditure_group": allocation.expenditure_group,
        "head_code": allocation.head_code,
        "head_name": allocation.head_name,
        "allocation_amount": allocated,
        "committed_amount": committed,
        "paid_amount": paid,
        "balance_amount": allocated - paid,
        "utilization_percent": float((paid / allocated * 100) if allocated else 0),
        "remarks": allocation.remarks,
    }


@router.get("/overview")
def overview(db: Annotated[Session, Depends(get_db)], financial_year: str | None = None):
    selected_year = financial_year or _current_financial_year()
    allocations = (
        db.query(ReportBudgetAllocation)
        .filter(ReportBudgetAllocation.financial_year == selected_year, ReportBudgetAllocation.is_active.is_(True))
        .order_by(ReportBudgetAllocation.expenditure_group, ReportBudgetAllocation.head_name)
        .all()
    )
    budget_heads = [_allocation_values(db, row) for row in allocations]
    group_summary = []
    for group in ("Administrative", "Programme"):
        group_rows = [row for row in budget_heads if row["expenditure_group"] == group]
        group_summary.append({
            "expenditure_group": group,
            "allocation_amount": sum((row["allocation_amount"] for row in group_rows), Decimal("0")),
            "committed_amount": sum((row["committed_amount"] for row in group_rows), Decimal("0")),
            "paid_amount": sum((row["paid_amount"] for row in group_rows), Decimal("0")),
            "balance_amount": sum((row["balance_amount"] for row in group_rows), Decimal("0")),
        })

    expenditure_heads = (
        db.query(FinancialExpenditureHead, ReportBudgetAllocation)
        .join(ReportBudgetAllocation, ReportBudgetAllocation.id == FinancialExpenditureHead.budget_head_id)
        .filter(FinancialExpenditureHead.is_active.is_(True))
        .order_by(ReportBudgetAllocation.expenditure_group, FinancialExpenditureHead.head_name)
        .all()
    )
    payment_rows = (
        db.query(PaymentTracking, PurchaseOrder)
        .join(PurchaseOrder, PurchaseOrder.id == PaymentTracking.po_id)
        .order_by(func.coalesce(PaymentTracking.invoice_date, PurchaseOrder.po_date).desc())
        .limit(1000).all()
    )
    emd_rows = db.query(EMD).order_by(func.coalesce(EMD.returned_date, EMD.expiry_date, EMD.created_at).desc()).limit(1000).all()
    return {
        "financial_year": selected_year,
        "budget_heads": budget_heads,
        "group_summary": group_summary,
        "totals": {
            "allocation_amount": sum((row["allocation_amount"] for row in budget_heads), Decimal("0")),
            "committed_amount": sum((row["committed_amount"] for row in budget_heads), Decimal("0")),
            "paid_amount": sum((row["paid_amount"] for row in budget_heads), Decimal("0")),
            "balance_amount": sum((row["balance_amount"] for row in budget_heads), Decimal("0")),
        },
        "expenditure_heads": [{
            "id": head.id, "budget_head_id": head.budget_head_id,
            "budget_head_code": budget.head_code, "budget_head_name": budget.head_name,
            "expenditure_group": budget.expenditure_group, "head_code": head.head_code,
            "head_name": head.head_name, "remarks": head.remarks,
        } for head, budget in expenditure_heads],
        "vendor_payments": [{
            "id": payment.id, "vendor_name": order.vendor_name, "po_no": order.po_no,
            "budget_head": order.budget_head, "invoice_no": payment.invoice_no,
            "invoice_date": payment.invoice_date, "invoice_amount": payment.invoice_amount,
            "paid_amount": payment.paid_amount,
            "outstanding_amount": max(_money(payment.invoice_amount) - _money(payment.paid_amount), Decimal("0")),
            "payment_status": payment.payment_status,
        } for payment, order in payment_rows],
        "emd_refunds": [{
            "id": emd.id, "emd_unique_id": emd.emd_unique_id, "tender_number": emd.tender_number,
            "bidder_name": emd.bidder_name, "amount": emd.amount, "expiry_date": emd.expiry_date,
            "returned_date": emd.returned_date, "refund_status": emd.refund_status,
            "return_details": emd.return_details,
        } for emd in emd_rows],
    }


def _commit_or_conflict(db: Session, message: str) -> None:
    try:
        db.commit()
    except IntegrityError as exc:
        db.rollback()
        raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail=message) from exc


@router.post("/budget-heads", status_code=status.HTTP_201_CREATED)
def create_budget_head(payload: BudgetHeadCreate, db: Annotated[Session, Depends(get_db)], _=Depends(financial_writer)):
    row = ReportBudgetAllocation(**payload.model_dump())
    db.add(row)
    _commit_or_conflict(db, "A budget head with this code already exists for the selected financial year and expenditure group.")
    db.refresh(row)
    return _allocation_values(db, row)


@router.put("/budget-heads/{head_id}")
def update_budget_head(
    head_id: int,
    payload: BudgetHeadUpdate,
    db: Annotated[Session, Depends(get_db)],
    _=Depends(financial_writer),
):
    row = db.get(ReportBudgetAllocation, head_id)
    if not row or not row.is_active:
        raise HTTPException(status_code=404, detail="Budget head not found.")
    for key, value in payload.model_dump(exclude_unset=True).items():
        setattr(row, key, value)
    _commit_or_conflict(db, "A budget head with this code already exists.")
    db.refresh(row)
    return _allocation_values(db, row)


@router.delete("/budget-heads/{head_id}")
def delete_budget_head(head_id: int, db: Annotated[Session, Depends(get_db)], _=Depends(financial_writer)):
    row = db.get(ReportBudgetAllocation, head_id)
    if not row:
        raise HTTPException(status_code=404, detail="Budget head not found.")
    row.is_active = False
    db.commit()
    return {"message": "Budget head deactivated."}


@router.post("/expenditure-heads", status_code=status.HTTP_201_CREATED)
def create_expenditure_head(payload: ExpenditureHeadCreate, db: Annotated[Session, Depends(get_db)], _=Depends(financial_writer)):
    budget = db.get(ReportBudgetAllocation, payload.budget_head_id)
    if not budget or not budget.is_active:
        raise HTTPException(status_code=404, detail="Budget head not found.")
    row = FinancialExpenditureHead(**payload.model_dump())
    db.add(row)
    _commit_or_conflict(db, "This expenditure head code already exists under the selected budget head.")
    db.refresh(row)
    return {"id": row.id, **payload.model_dump()}


@router.put("/expenditure-heads/{head_id}")
def update_expenditure_head(
    head_id: int,
    payload: ExpenditureHeadUpdate,
    db: Annotated[Session, Depends(get_db)],
    _=Depends(financial_writer),
):
    row = db.get(FinancialExpenditureHead, head_id)
    if not row or not row.is_active:
        raise HTTPException(status_code=404, detail="Expenditure head not found.")
    values = payload.model_dump(exclude_unset=True)
    budget_head_id = values.get("budget_head_id")
    if budget_head_id is not None:
        budget = db.get(ReportBudgetAllocation, budget_head_id)
        if not budget or not budget.is_active:
            raise HTTPException(status_code=404, detail="Budget head not found.")
    for key, value in values.items():
        setattr(row, key, value)
    _commit_or_conflict(db, "This expenditure head code already exists under the selected budget head.")
    return {"message": "Expenditure head updated."}


@router.delete("/expenditure-heads/{head_id}")
def delete_expenditure_head(head_id: int, db: Annotated[Session, Depends(get_db)], _=Depends(financial_writer)):
    row = db.get(FinancialExpenditureHead, head_id)
    if not row:
        raise HTTPException(status_code=404, detail="Expenditure head not found.")
    row.is_active = False
    db.commit()
    return {"message": "Expenditure head deactivated."}