"""
Ledger-driven financial reports (Ocean / QuickBooks-style).

All reports read from AccountHistory + AccountType classification on the branch COA.
"""
from __future__ import annotations

from collections import defaultdict
from datetime import date as date_cls
from datetime import datetime

from .models import AccountHistory

# ── Account type buckets (aligned with Ocean acc_account_type ids via type_key) ──

ASSET_TYPES = {
    "accounts_receivable", "current_assets", "cash_and_cash_equivalents",
    "fixed_assets", "non_current_assets", "bank",
}
LIABILITY_TYPES = {
    "accounts_payable", "credit_card", "current_liabilities", "non_current_liabilities",
}
EQUITY_TYPES = {"owners_equity"}
INCOME_TYPES = {"income", "other_income"}
COGS_TYPES = {"cost_of_sales"}
EXPENSE_TYPES = {"expenses", "other_expense"}
CASH_TYPES = {"cash_and_cash_equivalents", "bank"}

# Cash-flow classification fallbacks when account_detail_type.statement_of_cash_flows is blank
CF_OPERATING_TYPES = (
    INCOME_TYPES | COGS_TYPES | EXPENSE_TYPES
    | {"accounts_receivable", "accounts_payable", "current_assets", "current_liabilities", "credit_card"}
)
CF_INVESTING_TYPES = {"fixed_assets", "non_current_assets"}
CF_FINANCING_TYPES = {"owners_equity", "non_current_liabilities"}


def parse_finance_date(value):
    if value in (None, ""):
        return None
    if isinstance(value, date_cls):
        return value
    text = str(value).strip()
    for fmt in ("%Y-%m-%d", "%d/%m/%Y"):
        try:
            return datetime.strptime(text, fmt).date()
        except ValueError:
            continue
    raise ValueError(f"Invalid date format: {value!r}")


def ledger_amount(value) -> float:
    return float(value or 0)


def filter_ledger_queryset(qs, date_from=None, date_to=None):
    if date_from:
        qs = qs.filter(transaction_date__gte=parse_finance_date(date_from))
    if date_to:
        qs = qs.filter(transaction_date__lte=parse_finance_date(date_to))
    return qs


def branch_ledger(branch, date_from=None, date_to=None):
    qs = AccountHistory.objects.filter(
        company_branch=branch,
    ).select_related(
        "account",
        "account__account_type",
        "account__account_detail_type",
    ).order_by("transaction_date", "id")
    return filter_ledger_queryset(qs, date_from, date_to)


def _build_account_lines(bucket: dict) -> list:
    return sorted([
        {
            "account_id": account_id,
            "account_name": values["name"],
            "account_type": values["type"],
            "amount": str(round(values["net"], 2)),
        }
        for account_id, values in bucket.items()
    ], key=lambda row: row["account_name"])


def build_trial_balance(qs) -> dict:
    account_totals = defaultdict(lambda: {"debit": 0.0, "credit": 0.0, "account": None})
    for hist in qs:
        account_totals[hist.account_id]["debit"] += ledger_amount(hist.debit)
        account_totals[hist.account_id]["credit"] += ledger_amount(hist.credit)
        account_totals[hist.account_id]["account"] = hist.account

    lines = []
    total_debit = 0.0
    total_credit = 0.0

    for account_id, values in account_totals.items():
        account = values["account"]
        if not account:
            continue
        debit = round(values["debit"], 2)
        credit = round(values["credit"], 2)
        net = round(debit - credit, 2)
        normal_balance = account.account_type.normal_balance if account.account_type else "debit"
        lines.append({
            "account_id": account_id,
            "account_number": account.account_number,
            "account_name": account.account_name,
            "account_type": account.account_type.name if account.account_type else "",
            "account_type_key": account.account_type.type_key if account.account_type else "",
            "financial_statement": account.account_type.financial_statement if account.account_type else "",
            "total_debit": str(debit),
            "total_credit": str(credit),
            "net_debit": str(max(net, 0)) if normal_balance == "debit" else "0.00",
            "net_credit": str(max(-net, 0)) if normal_balance == "credit" else "0.00",
        })
        total_debit += debit
        total_credit += credit

    lines.sort(key=lambda row: row["account_number"])
    return {
        "lines": lines,
        "total_debit": str(round(total_debit, 2)),
        "total_credit": str(round(total_credit, 2)),
        "is_balanced": round(total_debit, 2) == round(total_credit, 2),
    }


def build_profit_and_loss(qs) -> dict:
    income_acc = defaultdict(lambda: {"name": "", "type": "", "net": 0.0})
    cogs_acc = defaultdict(lambda: {"name": "", "type": "", "net": 0.0})
    expense_acc = defaultdict(lambda: {"name": "", "type": "", "net": 0.0})

    for hist in qs:
        if not hist.account.account_type:
            continue
        type_key = hist.account.account_type.type_key
        account_id = hist.account_id
        debit = ledger_amount(hist.debit)
        credit = ledger_amount(hist.credit)

        if type_key in INCOME_TYPES:
            income_acc[account_id]["name"] = hist.account.account_name
            income_acc[account_id]["type"] = type_key
            income_acc[account_id]["net"] += credit - debit
        elif type_key in COGS_TYPES:
            cogs_acc[account_id]["name"] = hist.account.account_name
            cogs_acc[account_id]["type"] = type_key
            cogs_acc[account_id]["net"] += debit - credit
        elif type_key in EXPENSE_TYPES:
            expense_acc[account_id]["name"] = hist.account.account_name
            expense_acc[account_id]["type"] = type_key
            expense_acc[account_id]["net"] += debit - credit

    total_income = round(sum(values["net"] for values in income_acc.values()), 2)
    total_cogs = round(sum(values["net"] for values in cogs_acc.values()), 2)
    gross_profit = round(total_income - total_cogs, 2)
    total_expenses = round(sum(values["net"] for values in expense_acc.values()), 2)
    net_income = round(gross_profit - total_expenses, 2)

    return {
        "income_lines": _build_account_lines(income_acc),
        "cogs_lines": _build_account_lines(cogs_acc),
        "expense_lines": _build_account_lines(expense_acc),
        "total_income": str(total_income),
        "total_cogs": str(total_cogs),
        "gross_profit": str(gross_profit),
        "total_expenses": str(total_expenses),
        "net_income": str(net_income),
    }


def build_balance_sheet(qs) -> dict:
    assets = defaultdict(lambda: {"name": "", "type": "", "net": 0.0})
    liabilities = defaultdict(lambda: {"name": "", "type": "", "net": 0.0})
    equity = defaultdict(lambda: {"name": "", "type": "", "net": 0.0})
    net_income = 0.0

    for hist in qs:
        if not hist.account.account_type:
            continue
        type_key = hist.account.account_type.type_key
        account_id = hist.account_id
        debit = ledger_amount(hist.debit)
        credit = ledger_amount(hist.credit)

        if type_key in ASSET_TYPES:
            assets[account_id]["name"] = hist.account.account_name
            assets[account_id]["type"] = type_key
            assets[account_id]["net"] += debit - credit
        elif type_key in LIABILITY_TYPES:
            liabilities[account_id]["name"] = hist.account.account_name
            liabilities[account_id]["type"] = type_key
            liabilities[account_id]["net"] += credit - debit
        elif type_key in EQUITY_TYPES:
            equity[account_id]["name"] = hist.account.account_name
            equity[account_id]["type"] = type_key
            equity[account_id]["net"] += credit - debit
        elif type_key in INCOME_TYPES:
            net_income += credit - debit
        elif type_key in EXPENSE_TYPES | COGS_TYPES:
            net_income -= debit - credit

    total_assets = round(sum(values["net"] for values in assets.values()), 2)
    total_liabilities = round(sum(values["net"] for values in liabilities.values()), 2)
    total_equity_accounts = round(sum(values["net"] for values in equity.values()), 2)
    net_income = round(net_income, 2)
    total_equity = round(total_equity_accounts + net_income, 2)
    total_liabilities_and_equity = round(total_liabilities + total_equity, 2)

    return {
        "assets": _build_account_lines(assets),
        "liabilities": _build_account_lines(liabilities),
        "equity": _build_account_lines(equity),
        "net_income_in_equity": str(net_income),
        "total_assets": str(total_assets),
        "total_liabilities": str(total_liabilities),
        "total_equity": str(total_equity),
        "total_liabilities_and_equity": str(total_liabilities_and_equity),
        "is_balanced": round(total_assets, 2) == round(total_liabilities_and_equity, 2),
    }


def _cash_flow_bucket_for_account(account) -> str:
    detail = account.account_detail_type
    if detail and detail.statement_of_cash_flows:
        bucket = detail.statement_of_cash_flows.lower()
        if bucket in {"operating", "investing", "financing"}:
            return bucket

    type_key = account.account_type.type_key if account.account_type else ""
    if type_key in CF_INVESTING_TYPES:
        return "investing"
    if type_key in CF_FINANCING_TYPES:
        return "financing"
    if type_key in CASH_TYPES:
        return "cash"
    return "operating"


def _cash_impact(hist) -> float:
    """Positive = cash inflow, negative = cash outflow (direct method approximation)."""
    account = hist.account
    type_key = account.account_type.type_key if account.account_type else ""
    debit = ledger_amount(hist.debit)
    credit = ledger_amount(hist.credit)

    if type_key in CASH_TYPES:
        return credit - debit

    normal_balance = account.account_type.normal_balance if account.account_type else "debit"
    if normal_balance == "debit":
        return -(debit - credit)
    return credit - debit


def build_cash_flow_statement(qs) -> dict:
    pl = build_profit_and_loss(qs)
    net_income = float(pl["net_income"])

    operating_lines = [{"label": "Net income", "amount": str(round(net_income, 2))}]
    investing_lines = []
    financing_lines = []

    bucket_totals = defaultdict(float)
    bucket_lines = defaultdict(list)

    for hist in qs:
        account = hist.account
        if not account.account_type:
            continue
        bucket = _cash_flow_bucket_for_account(account)
        if bucket == "cash":
            continue

        impact = round(_cash_impact(hist), 2)
        if impact == 0:
            continue

        bucket_totals[bucket] += impact
        bucket_lines[bucket].append({
            "account_name": account.account_name,
            "amount": str(impact),
            "rel_type": hist.rel_type or "",
            "description": hist.description or "",
        })

    for row in bucket_lines["operating"]:
        operating_lines.append({
            "label": row["account_name"],
            "amount": row["amount"],
        })
    for row in bucket_lines["investing"]:
        investing_lines.append({
            "label": row["account_name"],
            "amount": row["amount"],
        })
    for row in bucket_lines["financing"]:
        financing_lines.append({
            "label": row["account_name"],
            "amount": row["amount"],
        })

    operating_total = round(net_income + bucket_totals["operating"], 2)
    investing_total = round(bucket_totals["investing"], 2)
    financing_total = round(bucket_totals["financing"], 2)
    net_cash_flow = round(operating_total + investing_total + financing_total, 2)

    return {
        "operating_lines": operating_lines,
        "investing_lines": investing_lines,
        "financing_lines": financing_lines,
        "operating_activities": str(operating_total),
        "investing_activities": str(investing_total),
        "financing_activities": str(financing_total),
        "net_cash_flow": str(net_cash_flow),
        # Legacy nested shape for finance.vue compatibility
        "cfo": {
            "net_income": str(round(net_income, 2)),
            "adjustments_total": str(round(bucket_totals["operating"], 2)),
            "net_cash_from_operating": str(operating_total),
        },
        "cfi": {
            "net_cash_from_investing": str(investing_total),
        },
        "cff": {
            "net_cash_from_financing": str(financing_total),
        },
    }


def build_ledger_journal(qs) -> dict:
    entries = []
    for hist in qs.order_by("-transaction_date", "-id")[:500]:
        account = hist.account
        entries.append({
            "entry_date": str(hist.transaction_date),
            "reference": hist.rel_id or "",
            "description": hist.description or hist.rel_type or "",
            "account_name": account.account_name if account else "",
            "account_number": account.account_number if account else "",
            "rel_type": hist.rel_type or "",
            "total_debit": str(round(ledger_amount(hist.debit), 2)),
            "total_credit": str(round(ledger_amount(hist.credit), 2)),
        })

    return {
        "journal_entries": entries,
        "total": len(entries),
    }


# ---------------------------------------------------------------------------
# ACCOUNTS RECEIVABLE AGEING  (based on CustomerOrder outstanding balances)
# ---------------------------------------------------------------------------

def build_ar_ageing(branch, as_of_date=None):
    """
    AR ageing by customer. Outstanding = net_value - amount_paid on approved,
    non-cancelled customer orders. Buckets: Current (0–30), 31–60, 61–90, 90+.
    """
    from sales_and_marketing.models import CustomerOrder
    from datetime import date as date_cls, timedelta

    as_of = as_of_date or date_cls.today()
    if isinstance(as_of, str):
        as_of = parse_finance_date(as_of)

    orders = CustomerOrder.objects.filter(
        company_branch=branch,
        customer_order_approved=True,
        recycle_bin=False,
    ).exclude(
        customer_order_cancelled_by_customer=True,
    ).exclude(
        customer_order_cancelled_by_sales_team=True,
    ).select_related("customer_profile").order_by("created_on")

    customer_rows = {}
    totals = {"current": 0.0, "days_31_60": 0.0, "days_61_90": 0.0, "over_90": 0.0, "total": 0.0}

    for order in orders:
        net = float(order.customer_order_total_net_value or 0)
        paid = float(order.customer_order_total_amount_paid or 0)
        outstanding = round(net - paid, 2)
        if outstanding <= 0:
            continue

        order_date = order.created_on.date() if hasattr(order.created_on, 'date') else order.created_on
        age_days = (as_of - order_date).days if order_date <= as_of else 0

        customer_name = str(order.customer_profile) if order.customer_profile else "Unknown Customer"
        customer_id = order.customer_profile_id or 0

        if customer_id not in customer_rows:
            customer_rows[customer_id] = {
                "customer_id": customer_id,
                "customer_name": customer_name,
                "current": 0.0, "days_31_60": 0.0, "days_61_90": 0.0, "over_90": 0.0, "total": 0.0,
            }

        row = customer_rows[customer_id]
        if age_days <= 30:
            row["current"] += outstanding
            totals["current"] += outstanding
        elif age_days <= 60:
            row["days_31_60"] += outstanding
            totals["days_31_60"] += outstanding
        elif age_days <= 90:
            row["days_61_90"] += outstanding
            totals["days_61_90"] += outstanding
        else:
            row["over_90"] += outstanding
            totals["over_90"] += outstanding
        row["total"] += outstanding
        totals["total"] += outstanding

    for row in customer_rows.values():
        for k in ("current", "days_31_60", "days_61_90", "over_90", "total"):
            row[k] = str(round(row[k], 2))

    for k in totals:
        totals[k] = str(round(totals[k], 2))

    return {
        "as_of_date": str(as_of),
        "rows": sorted(customer_rows.values(), key=lambda r: r["customer_name"]),
        "totals": totals,
    }


# ---------------------------------------------------------------------------
# ACCOUNTS PAYABLE AGEING  (based on PurchaseOrder outstanding balances)
# ---------------------------------------------------------------------------

def build_ap_ageing(branch, as_of_date=None):
    """
    AP ageing by supplier. Outstanding = purchase_value - credits posted to
    accounts_payable via AccountHistory for that PO. Buckets: 0–30, 31–60, 61–90, 90+.
    Falls back to simple purchase_value when no ledger posts exist.
    """
    from procurement.models import PurchaseOrder
    from datetime import date as date_cls

    as_of = as_of_date or date_cls.today()
    if isinstance(as_of, str):
        as_of = parse_finance_date(as_of)

    orders = PurchaseOrder.objects.filter(
        company_profile=branch.company_profile,
        purchase_order_approved=True,
        recycle_bin=False,
    ).order_by("created_on")

    # Build map of amount paid per PO from AccountHistory (rel_type='purchase_order', credit side)
    paid_map = {}
    ah_qs = AccountHistory.objects.filter(
        company_branch=branch,
        rel_type="purchase_order",
    ).values("rel_id", "debit", "credit")
    for h in ah_qs:
        rid = str(h["rel_id"])
        paid_map[rid] = paid_map.get(rid, 0.0) + ledger_amount(h["credit"])

    supplier_rows = {}
    totals = {"current": 0.0, "days_31_60": 0.0, "days_61_90": 0.0, "over_90": 0.0, "total": 0.0}

    for order in orders:
        total_val = float(order.purchase_value_overall or 0)
        paid = paid_map.get(str(order.id), 0.0)
        outstanding = round(total_val - paid, 2)
        if outstanding <= 0:
            continue

        order_date = order.created_on.date() if hasattr(order.created_on, 'date') else order.created_on
        age_days = (as_of - order_date).days if order_date <= as_of else 0

        first_instance = order.purchase_order_product_instances.filter(recycle_bin=False).first()
        supplier = first_instance.supplier if first_instance else None
        supplier_name = str(supplier) if supplier else "Unknown Supplier"
        supplier_id = supplier.id if supplier else 0

        if supplier_id not in supplier_rows:
            supplier_rows[supplier_id] = {
                "supplier_id": supplier_id,
                "supplier_name": supplier_name,
                "current": 0.0, "days_31_60": 0.0, "days_61_90": 0.0, "over_90": 0.0, "total": 0.0,
            }

        row = supplier_rows[supplier_id]
        if age_days <= 30:
            row["current"] += outstanding
            totals["current"] += outstanding
        elif age_days <= 60:
            row["days_31_60"] += outstanding
            totals["days_31_60"] += outstanding
        elif age_days <= 90:
            row["days_61_90"] += outstanding
            totals["days_61_90"] += outstanding
        else:
            row["over_90"] += outstanding
            totals["over_90"] += outstanding
        row["total"] += outstanding
        totals["total"] += outstanding

    for row in supplier_rows.values():
        for k in ("current", "days_31_60", "days_61_90", "over_90", "total"):
            row[k] = str(round(row[k], 2))

    for k in totals:
        totals[k] = str(round(totals[k], 2))

    return {
        "as_of_date": str(as_of),
        "rows": sorted(supplier_rows.values(), key=lambda r: r["supplier_name"]),
        "totals": totals,
    }