from datetime import date
from decimal import Decimal
from sqlalchemy import func
from sqlalchemy.orm import Session
from app.models.finance import Account, JournalEntry
from app.models.accounting import JournalLine
from app.services.accounting import d


def _signed(normal_balance: str | None, debit: Decimal, credit: Decimal) -> Decimal:
    if (normal_balance or "D") == "C":
        return d(credit - debit)
    return d(debit - credit)


def _account_map(db: Session):
    return {a.id: a for a in db.query(Account).all()}


def trial_balance(db: Session, period_id: str | None = None,
                  branch_id: str | None = None) -> dict:
    query = db.query(
        JournalLine.account_id,
        func.sum(JournalLine.debit).label("debit_sum"),
        func.sum(JournalLine.credit).label("credit_sum"),
    ).join(JournalEntry, JournalEntry.id == JournalLine.entry_id).filter(
        JournalEntry.status == "posted"
    )
    if period_id:
        query = query.filter(JournalLine.period_id == period_id)
    if branch_id:
        query = query.filter(
            (JournalLine.branch_id == branch_id) | (JournalLine.branch_id.is_(None))
        )
    rows = query.group_by(JournalLine.account_id).all()
    accounts = _account_map(db)
    lines = []
    total_debit = Decimal("0")
    total_credit = Decimal("0")
    for account_id, debit_sum, credit_sum in rows:
        account = accounts.get(account_id)
        debit = d(debit_sum or 0)
        credit = d(credit_sum or 0)
        total_debit += debit
        total_credit += credit
        lines.append({
            "account_id": account_id,
            "code": account.code if account else None,
            "name": account.name if account else account_id,
            "account_type": account.account_type if account else None,
            "normal_balance": account.normal_balance if account else "D",
            "debit": float(debit),
            "credit": float(credit),
        })
    return {
        "lines": lines,
        "total_debit": float(total_debit),
        "total_credit": float(total_credit),
        "balanced": total_debit == total_credit,
    }


def general_ledger(db: Session, account_id: str, branch_id: str | None = None,
                   from_date: date | None = None, to_date: date | None = None) -> dict:
    account = db.query(Account).filter(Account.id == account_id).first()
    if not account:
        return {"account_id": account_id, "found": False, "lines": [], "closing_balance": 0}
    query = db.query(JournalLine, JournalEntry).join(
        JournalEntry, JournalEntry.id == JournalLine.entry_id
    ).filter(JournalEntry.status == "posted", JournalLine.account_id == account_id)
    if branch_id:
        query = query.filter(JournalLine.branch_id == branch_id)
    if from_date:
        query = query.filter(JournalEntry.entry_date >= from_date)
    if to_date:
        query = query.filter(JournalEntry.entry_date <= to_date)
    rows = query.order_by(JournalEntry.entry_date, JournalEntry.entry_number).all()
    running = Decimal("0")
    lines = []
    for line, entry in rows:
        signed = _signed(account.normal_balance, d(line.debit), d(line.credit))
        running = d(running + signed)
        lines.append({
            "entry_id": entry.id,
            "entry_number": entry.entry_number,
            "entry_date": entry.entry_date.isoformat(),
            "description": entry.description,
            "source": entry.source,
            "branch_id": entry.branch_id,
            "debit": float(d(line.debit)),
            "credit": float(d(line.credit)),
            "balance": float(running),
        })
    return {
        "account_id": account_id,
        "code": account.code,
        "name": account.name,
        "found": True,
        "lines": lines,
        "closing_balance": float(running),
    }


def profit_and_loss(db: Session, from_date: date | None = None, to_date: date | None = None,
                    branch_id: str | None = None) -> dict:
    query = db.query(JournalLine).join(
        JournalEntry, JournalEntry.id == JournalLine.entry_id
    ).filter(JournalEntry.status == "posted")
    if from_date:
        query = query.filter(JournalEntry.entry_date >= from_date)
    if to_date:
        query = query.filter(JournalEntry.entry_date <= to_date)
    if branch_id:
        query = query.filter(JournalLine.branch_id == branch_id)
    accounts = _account_map(db)
    buckets = {"revenue": [], "cogs": [], "expenses": []}
    for line in query.all():
        account = accounts.get(line.account_id)
        if not account:
            continue
        signed = _signed(account.normal_balance, d(line.debit), d(line.credit))
        if account.account_type == "revenue":
            buckets["revenue"].append((account, signed))
        elif account.category == "cogs":
            buckets["cogs"].append((account, signed))
        elif account.account_type == "expense":
            buckets["expenses"].append((account, signed))

    def aggregate(items):
        totals = {}
        names = {}
        overall = Decimal("0")
        for account, value in items:
            key = account.code or account.id
            names[key] = account.name
            totals[key] = totals.get(key, Decimal("0")) + value
            overall += value
        detail = [{"code": key, "name": names[key], "amount": float(d(value))}
                  for key, value in totals.items()]
        return overall, detail

    total_revenue, revenue_detail = aggregate(buckets["revenue"])
    total_cogs, cogs_detail = aggregate(buckets["cogs"])
    total_expenses, expense_detail = aggregate(buckets["expenses"])
    gross_profit = d(total_revenue - total_cogs)
    net_profit = d(gross_profit - total_expenses)
    return {
        "from_date": from_date.isoformat() if from_date else None,
        "to_date": to_date.isoformat() if to_date else None,
        "revenue": revenue_detail,
        "total_revenue": float(total_revenue),
        "cogs": cogs_detail,
        "total_cogs": float(total_cogs),
        "gross_profit": float(gross_profit),
        "expenses": expense_detail,
        "total_expenses": float(total_expenses),
        "net_profit": float(net_profit),
    }


def balance_sheet(db: Session, as_of: date | None = None, branch_id: str | None = None) -> dict:
    query = db.query(JournalLine).join(
        JournalEntry, JournalEntry.id == JournalLine.entry_id
    ).filter(JournalEntry.status == "posted")
    if as_of:
        query = query.filter(JournalEntry.entry_date <= as_of)
    if branch_id:
        query = query.filter(JournalLine.branch_id == branch_id)
    accounts = _account_map(db)
    assets = []
    liabilities = []
    equities = []
    total_asset = Decimal("0")
    total_liability = Decimal("0")
    total_equity = Decimal("0")
    revenue_contrib = Decimal("0")
    cogs_contrib = Decimal("0")
    expense_contrib = Decimal("0")
    for line in query.all():
        account = accounts.get(line.account_id)
        if not account:
            continue
        signed = _signed(account.normal_balance, d(line.debit), d(line.credit))
        code = account.code or account.id
        if account.account_type == "asset":
            total_asset += signed
            token = next((a for a in assets if a["code"] == code), None)
            if token:
                token["amount"] = float(d(Decimal(str(token["amount"])) + signed))
            else:
                assets.append({"code": code, "name": account.name, "amount": float(signed)})
        elif account.account_type == "liability":
            total_liability += signed
            token = next((a for a in liabilities if a["code"] == code), None)
            if token:
                token["amount"] = float(d(Decimal(str(token["amount"])) + signed))
            else:
                liabilities.append({"code": code, "name": account.name, "amount": float(signed)})
        elif account.account_type == "equity":
            total_equity += signed
            token = next((a for a in equities if a["code"] == code), None)
            if token:
                token["amount"] = float(d(Decimal(str(token["amount"])) + signed))
            else:
                equities.append({"code": code, "name": account.name, "amount": float(signed)})
        elif account.account_type == "revenue":
            revenue_contrib += signed
        elif account.category == "cogs":
            cogs_contrib += signed
        elif account.account_type == "expense":
            expense_contrib += signed
    current_earnings = d(revenue_contrib - cogs_contrib - expense_contrib)
    total_equity = d(total_equity + current_earnings)
    equities.append({
        "code": "PNL", "name": "Current Period Profit/Loss",
        "amount": float(current_earnings),
    })
    return {
        "as_of": as_of.isoformat() if as_of else None,
        "assets": assets,
        "total_assets": float(total_asset),
        "liabilities": liabilities,
        "total_liabilities": float(total_liability),
        "equity": equities,
        "total_equity": float(total_equity),
        "balanced": total_asset == total_liability + total_equity,
    }


def _cash_account_ids(accounts: dict) -> set:
    cash_ids = set()
    for account_id, account in accounts.items():
        if (account.category or "") in ("cash", "bank") or (account.code or "").startswith(("1010", "1020")):
            cash_ids.add(account_id)
    return cash_ids


def _cf_bucket(account: Account | None, entry: JournalEntry) -> str:
    if entry.source in ("loan", "loan_payment"):
        return "financing"
    if account is None:
        return "operating"
    if (account.category or "") == "fixed_asset":
        return "investing"
    if account.account_type == "equity" or (account.category or "") == "loan":
        return "financing"
    return "operating"


def cash_flow(db: Session, from_date: date | None = None, to_date: date | None = None,
              branch_id: str | None = None) -> dict:
    """Direct-method statement of cash flows: net change on cash/bank accounts,
    classified by the counterpart account of each posted entry."""
    accounts = _account_map(db)
    cash_ids = _cash_account_ids(accounts)
    query = db.query(JournalLine, JournalEntry).join(
        JournalEntry, JournalEntry.id == JournalLine.entry_id
    ).filter(JournalEntry.status == "posted")
    if from_date:
        query = query.filter(JournalEntry.entry_date >= from_date)
    if to_date:
        query = query.filter(JournalEntry.entry_date <= to_date)
    if branch_id:
        query = query.filter(
            (JournalLine.branch_id == branch_id) | (JournalLine.branch_id.is_(None))
        )
    entries_by_id = {}
    rows = query.all()
    totals = {"operating": Decimal("0"), "investing": Decimal("0"), "financing": Decimal("0")}
    detail = []
    for line, entry in rows:
        if line.account_id not in cash_ids:
            continue
        entries_by_id.setdefault(entry.id, entry)
        account = accounts[line.account_id]
        signed = d(d(line.debit) - d(line.credit))  # cash/bank are asset accounts (normal D)
        if signed == 0:
            continue
        counterpart = None
        for cl in entry.lines:
            if cl.account_id not in cash_ids:
                counterpart = accounts.get(cl.account_id)
                break
        bucket = _cf_bucket(counterpart, entry)
        totals[bucket] += signed
        detail.append({
            "entry_id": entry.id,
            "entry_number": entry.entry_number,
            "entry_date": entry.entry_date.isoformat(),
            "description": entry.description,
            "source": entry.source,
            "buckets": bucket,
            "amount": float(signed),
        })
    return {
        "from_date": from_date.isoformat() if from_date else None,
        "to_date": to_date.isoformat() if to_date else None,
        "operating": float(totals["operating"]),
        "investing": float(totals["investing"]),
        "financing": float(totals["financing"]),
        "net_cash_change": float(totals["operating"] + totals["investing"] + totals["financing"]),
        "detail": sorted(detail, key=lambda r: (r["entry_date"], r["entry_number"])),
    }


def pnl_by_branch(db: Session, from_date: date | None = None,
                  to_date: date | None = None) -> dict:
    from app.models.branch import Branch
    branches = db.query(Branch).filter(Branch.is_active == True).order_by(Branch.code).all()  # noqa: E712
    rows = []
    for branch in branches:
        rows.append({
            "branch_id": branch.id,
            "branch_code": branch.code,
            "branch_name": branch.name,
            "pnl": profit_and_loss(db, from_date, to_date, branch.id),
        })
    rows.append({
        "branch_id": None,
        "branch_code": None,
        "branch_name": "Unallocated",
        "pnl": profit_and_loss(db, from_date, to_date, None),
    })
    return {
        "from_date": from_date.isoformat() if from_date else None,
        "to_date": to_date.isoformat() if to_date else None,
        "branches": rows,
    }


def dashboard_metrics(db: Session, as_of: date | None = None) -> dict:
    from app.models.sale import Sale
    from app.models.accounting import Loan, LoanSchedule
    from app.services.transaction_postings import party_aging
    accounts = _account_map(db)
    cash_ids = _cash_account_ids(accounts)
    query = db.query(JournalLine).join(
        JournalEntry, JournalEntry.id == JournalLine.entry_id
    ).filter(JournalEntry.status == "posted")
    if as_of:
        query = query.filter(JournalEntry.entry_date <= as_of)

    cash_total = Decimal("0")
    receivables = Decimal("0")
    payables = Decimal("0")
    total_revenue = Decimal("0")
    total_cogs = Decimal("0")
    total_expenses = Decimal("0")
    for line in query.all():
        account = accounts.get(line.account_id)
        if not account:
            continue
        signed = _signed(account.normal_balance, d(line.debit), d(line.credit))
        if line.account_id in cash_ids:
            cash_total += signed
        if account.account_type == "asset" and account.category == "receivable":
            receivables += signed
        if account.account_type == "liability" and account.category == "payable":
            payables += signed
        if account.account_type == "revenue":
            total_revenue += signed
        elif account.category == "cogs":
            total_cogs += signed
        elif account.account_type == "expense":
            total_expenses += signed
    net_profit = d(total_revenue - total_cogs - total_expenses)

    postable = {"completed", "paid", "confirmed", "closed"}
    sales_query = db.query(Sale).filter(Sale.status.in_(postable))
    sales_count = sales_query.count()
    from app.services.transaction_postings import sale_remaining
    unpaid_amount = Decimal("0")
    unpaid_count = 0
    for sale in sales_query.all():
        rem = sale_remaining(db, sale)
        if rem > 0:
            unpaid_amount += rem
            unpaid_count += 1

    aging = {"bucket_30": Decimal("0"), "bucket_60": Decimal("0"),
             "bucket_90": Decimal("0"), "bucket_91": Decimal("0")}
    for party_type in ("customer", "supplier"):
        for row in party_aging(db, party_type, as_of or date.today()):
            for key in aging:
                aging[key] += Decimal(str(row.get(key, 0)))

    open_loan_principal = Decimal("0")
    active_loans = db.query(Loan).filter(Loan.status == "active").all()
    for loan in active_loans:
        schedules = db.query(LoanSchedule).filter(
            LoanSchedule.loan_id == loan.id, LoanSchedule.status == "pending"
        ).all()
        open_loan_principal += sum(
            (d(s.principal_due) - d(s.principal_paid) for s in schedules), Decimal("0")
        )
    return {
        "as_of": as_of.isoformat() if as_of else None,
        "cash_and_bank": float(d(cash_total)),
        "receivables": float(d(receivables)),
        "payables": float(d(payables)),
        "total_revenue": float(d(total_revenue)),
        "net_profit": float(net_profit),
        "sales_count": sales_count,
        "unpaid_sales": unpaid_count,
        "unpaid_amount": float(d(unpaid_amount)),
        "aging": {k: float(d(v)) for k, v in aging.items()},
        "open_loans": len(active_loans),
        "open_loan_principal": float(d(open_loan_principal)),
    }