from __future__ import annotations
import csv
import io
from datetime import date, datetime
from decimal import Decimal
from typing import Optional
from fastapi import APIRouter, Depends, HTTPException
from fastapi.responses import StreamingResponse
from sqlalchemy.orm import Session
from pydantic import BaseModel, Field
from app.database import get_db
from app.models.finance import Account, JournalEntry
from app.models.accounting import (
    AccountingPeriod, JournalLine, CashbookMovement,
)
from app.models.audit import AuditLog
from app.services.auth import get_current_user, require_permission
from app.services import accounting as svc
from app.services import balances as bal_svc
from app.services import reports_accounting as rep
from app.services.transaction_postings import party_aging, party_ledger

router = APIRouter(prefix="/accounting", tags=["accounting"])

ACCOUNT_TYPES = {"asset", "liability", "equity", "revenue", "expense"}
NORMAL_BALANCES = {"D", "C"}


class AccountCreate(BaseModel):
    name: str
    code: Optional[str] = None
    type: Optional[str] = None
    account_type: Optional[str] = None
    normal_balance: Optional[str] = None
    category: Optional[str] = None
    parent_id: Optional[str] = None
    branch_id: Optional[str] = None
    is_system: bool = False
    balance: float = 0.0
    is_active: bool = True


class AccountUpdate(BaseModel):
    name: Optional[str] = None
    code: Optional[str] = None
    type: Optional[str] = None
    account_type: Optional[str] = None
    normal_balance: Optional[str] = None
    category: Optional[str] = None
    parent_id: Optional[str] = None
    branch_id: Optional[str] = None
    is_active: Optional[bool] = None


class AccountOut(BaseModel):
    id: str
    name: str
    type: str
    code: Optional[str] = None
    account_type: Optional[str] = None
    normal_balance: Optional[str] = None
    category: Optional[str] = None
    parent_id: Optional[str] = None
    branch_id: Optional[str] = None
    is_system: bool
    balance: float
    is_active: bool
    created_at: datetime
    model_config = {"from_attributes": True}


class PeriodCreate(BaseModel):
    period_code: Optional[str] = None
    start_date: date
    end_date: date
    status: str = "open"


class PeriodOut(BaseModel):
    id: str
    period_code: str
    start_date: date
    end_date: date
    status: str
    closed_by: Optional[str] = None
    closed_at: Optional[datetime] = None
    model_config = {"from_attributes": True}


class JournalLineIn(BaseModel):
    account_id: str
    debit: Decimal = Field(default=Decimal("0"), ge=0)
    credit: Decimal = Field(default=Decimal("0"), ge=0)
    branch_id: Optional[str] = None
    production_batch_id: Optional[str] = None
    passport_id: Optional[str] = None
    cost_center: Optional[str] = None


class JournalCreate(BaseModel):
    entry_date: date
    description: str
    lines: list[JournalLineIn]
    branch_id: Optional[str] = None
    source: str = "journal"
    reference_type: Optional[str] = None
    reference_id: Optional[str] = None


class JournalUpdate(BaseModel):
    entry_date: Optional[date] = None
    description: Optional[str] = None
    branch_id: Optional[str] = None
    lines: Optional[list[JournalLineIn]] = None


class ReverseIn(BaseModel):
    reason: Optional[str] = None


class OpeningBalanceItem(BaseModel):
    account_id: str
    amount: Decimal = Field(ge=0)


class OpeningBalancesIn(BaseModel):
    items: list[OpeningBalanceItem]
    entry_date: Optional[date] = None
    branch_id: Optional[str] = None
    description: Optional[str] = None


class LineOut(BaseModel):
    account_id: str
    debit: float
    credit: float
    branch_id: Optional[str] = None
    production_batch_id: Optional[str] = None
    passport_id: Optional[str] = None
    cost_center: Optional[str] = None


class EntryOut(BaseModel):
    id: str
    entry_number: str
    entry_date: date
    period_id: str
    branch_id: Optional[str] = None
    status: str
    source: str
    reference_type: Optional[str] = None
    reference_id: Optional[str] = None
    description: str
    is_reversal: bool
    reversal_of_id: Optional[str] = None
    created_by: str
    posted_by: Optional[str] = None
    posted_at: Optional[datetime] = None
    created_at: datetime
    lines: list[LineOut] = []
    model_config = {"from_attributes": True}


def _coa_or_default(name: str, code: str, account_type: str, normal_balance: str, category: str,
                    db: Session):
    account = db.query(Account).filter(Account.code == code).first()
    if account:
        return account
    return Account(name=name, code=code, type=account_type, account_type=account_type,
                   normal_balance=normal_balance, category=category, is_system=True, is_active=True)


@router.get("/accounts", response_model=list[AccountOut])
def list_accounts(
    account_type: Optional[str] = None,
    code_prefix: Optional[str] = None,
    branch_id: Optional[str] = None,
    db: Session = Depends(get_db),
    _=Depends(require_permission("accounting", "view")),
):
    q = db.query(Account)
    if account_type:
        q = q.filter(Account.account_type == account_type)
    if code_prefix:
        q = q.filter(Account.code.like(f"{code_prefix}%"))
    if branch_id:
        q = q.filter((Account.branch_id == branch_id) | (Account.branch_id.is_(None)))
    return q.order_by(Account.code).all()


@router.post("/accounts", response_model=AccountOut, status_code=201)
def create_account(data: AccountCreate, db: Session = Depends(get_db),
                   _=Depends(require_permission("accounting", "create"))):
    if data.account_type and data.account_type not in ACCOUNT_TYPES:
        raise HTTPException(422, detail=f"account_type must be one of {sorted(ACCOUNT_TYPES)}")
    if data.normal_balance and data.normal_balance not in NORMAL_BALANCES:
        raise HTTPException(422, detail="normal_balance must be 'D' or 'C'")
    if data.code:
        dup = db.query(Account).filter(Account.code == data.code).first()
        if dup:
            raise HTTPException(409, detail=f"Account code {data.code} already exists")
    attrs = data.model_dump()
    attrs["type"] = attrs.get("type") or attrs.get("account_type") or "asset"
    obj = Account(**attrs)
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj


@router.get("/accounts/{account_id}", response_model=AccountOut)
def get_account(account_id: str, db: Session = Depends(get_db),
                _=Depends(require_permission("accounting", "view"))):
    obj = db.query(Account).filter(Account.id == account_id).first()
    if not obj:
        raise HTTPException(404, detail="Account not found")
    return obj


@router.put("/accounts/{account_id}", response_model=AccountOut)
def update_account(account_id: str, data: AccountUpdate, db: Session = Depends(get_db),
                   _=Depends(require_permission("accounting", "edit"))):
    obj = db.query(Account).filter(Account.id == account_id).first()
    if not obj:
        raise HTTPException(404, detail="Account not found")
    if obj.is_system and data.code is not None and data.code != obj.code:
        raise HTTPException(403, detail="System account code cannot change")
    for k, v in data.model_dump(exclude_unset=True).items():
        setattr(obj, k, v)
    db.commit()
    db.refresh(obj)
    return obj


@router.delete("/accounts/{account_id}")
def delete_account(account_id: str, db: Session = Depends(get_db),
                   _=Depends(require_permission("accounting", "delete"))):
    obj = db.query(Account).filter(Account.id == account_id).first()
    if not obj:
        raise HTTPException(404, detail="Account not found")
    if obj.is_system:
        raise HTTPException(403, detail="System accounts cannot be deleted")
    used = db.query(JournalLine).filter(JournalLine.account_id == account_id).first()
    if used:
        raise HTTPException(409, detail="Account is used by journal lines")
    db.delete(obj)
    db.commit()
    return {"detail": "Account deleted"}


@router.get("/periods", response_model=list[PeriodOut])
def list_periods(db: Session = Depends(get_db),
                 _=Depends(require_permission("accounting", "view"))):
    return db.query(AccountingPeriod).order_by(
        AccountingPeriod.period_code.desc()
    ).all()


@router.post("/periods", response_model=PeriodOut, status_code=201)
def create_period(data: PeriodCreate, db: Session = Depends(get_db),
                  _=Depends(require_permission("accounting", "create"))):
    if data.end_date < data.start_date:
        raise HTTPException(422, detail="end_date must be >= start_date")
    code = data.period_code or data.start_date.strftime("%Y-%m")
    dup = db.query(AccountingPeriod).filter(AccountingPeriod.period_code == code).first()
    if dup:
        raise HTTPException(409, detail=f"Period {code} already exists")
    obj = AccountingPeriod(**data.model_dump(exclude={"period_code"}), period_code=code)
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj


@router.post("/periods/{period_id}/close", response_model=PeriodOut)
def close_period(period_id: str, db: Session = Depends(get_db),
                 current_user=Depends(require_permission("accounting", "edit"))):
    obj = db.query(AccountingPeriod).filter(AccountingPeriod.id == period_id).first()
    if not obj:
        raise HTTPException(404, detail="Period not found")
    if obj.status == "closed":
        raise HTTPException(409, detail="Period already closed")
    obj.status = "closed"
    obj.closed_by = current_user.id
    obj.closed_at = datetime.now()
    db.add(AuditLog(user_id=current_user.id, action="close_period", module="accounting",
                    record_id=obj.id, new_value=obj.period_code))
    db.commit()
    db.refresh(obj)
    return obj


@router.post("/periods/{period_id}/reopen", response_model=PeriodOut)
def reopen_period(period_id: str, db: Session = Depends(get_db),
                  current_user=Depends(require_permission("accounting", "edit"))):
    obj = db.query(AccountingPeriod).filter(AccountingPeriod.id == period_id).first()
    if not obj:
        raise HTTPException(404, detail="Period not found")
    if obj.status == "open":
        raise HTTPException(409, detail="Period already open")
    obj.status = "open"
    obj.closed_by = None
    obj.closed_at = None
    db.add(AuditLog(user_id=current_user.id, action="reopen_period", module="accounting",
                    record_id=obj.id, new_value=obj.period_code))
    db.commit()
    db.refresh(obj)
    return obj


def _entry_out_json(entry: JournalEntry) -> dict:
    return {
        "id": entry.id,
        "entry_number": entry.entry_number,
        "entry_date": entry.entry_date,
        "period_id": entry.period_id,
        "branch_id": entry.branch_id,
        "status": entry.status,
        "source": entry.source,
        "reference_type": entry.reference_type,
        "reference_id": entry.reference_id,
        "description": entry.description,
        "is_reversal": entry.is_reversal,
        "reversal_of_id": entry.reversal_of_id,
        "created_by": entry.created_by,
        "posted_by": entry.posted_by,
        "posted_at": entry.posted_at,
        "created_at": entry.created_at,
        "lines": [
            {
                "account_id": ln.account_id,
                "debit": float(ln.debit),
                "credit": float(ln.credit),
                "branch_id": ln.branch_id,
                "production_batch_id": ln.production_batch_id,
                "passport_id": ln.passport_id,
                "cost_center": ln.cost_center,
            }
            for ln in entry.lines
        ],
    }


@router.get("/journal")
def list_journal(
    status: Optional[str] = None,
    source: Optional[str] = None,
    branch_id: Optional[str] = None,
    period_id: Optional[str] = None,
    from_date: Optional[date] = None,
    to_date: Optional[date] = None,
    db: Session = Depends(get_db),
    _=Depends(require_permission("accounting", "view")),
):
    q = db.query(JournalEntry)
    if status:
        q = q.filter(JournalEntry.status == status)
    if source:
        q = q.filter(JournalEntry.source == source)
    if branch_id:
        q = q.filter((JournalEntry.branch_id == branch_id) | (JournalEntry.branch_id.is_(None)))
    if period_id:
        q = q.filter(JournalEntry.period_id == period_id)
    if from_date:
        q = q.filter(JournalEntry.entry_date >= from_date)
    if to_date:
        q = q.filter(JournalEntry.entry_date <= to_date)
    entries = q.order_by(JournalEntry.entry_date.desc(),
                         JournalEntry.entry_number.desc()).all()
    return [_entry_out_json(e) for e in entries]


@router.post("/journal", response_model=EntryOut, status_code=201)
def create_journal(data: JournalCreate, db: Session = Depends(get_db),
                   current_user=Depends(require_permission("accounting", "create"))):
    lines = [ln.model_dump() for ln in data.lines]
    try:
        entry = svc.PostingService(db).create_draft(
            entry_date=data.entry_date, lines=lines, description=data.description,
            branch_id=data.branch_id, source=data.source,
            reference_type=data.reference_type, reference_id=data.reference_id,
            created_by=current_user.id,
        )
        db.commit()
    except svc.AccountingError as e:
        db.rollback()
        raise HTTPException(422, detail=str(e))
    db.refresh(entry)
    return entry


@router.get("/journal/{entry_id}", response_model=EntryOut)
def get_journal(entry_id: str, db: Session = Depends(get_db),
                _=Depends(require_permission("accounting", "view"))):
    entry = db.query(JournalEntry).filter(JournalEntry.id == entry_id).first()
    if not entry:
        raise HTTPException(404, detail="Journal entry not found")
    return entry


@router.put("/journal/{entry_id}", response_model=EntryOut)
def update_journal(entry_id: str, data: JournalUpdate, db: Session = Depends(get_db),
                   current_user=Depends(require_permission("accounting", "edit"))):
    entry = db.query(JournalEntry).filter(JournalEntry.id == entry_id).first()
    if not entry:
        raise HTTPException(404, detail="Journal entry not found")
    if entry.status != "draft":
        raise HTTPException(409, detail="Only draft entries can be edited")
    try:
        if data.entry_date is not None:
            entry.entry_date = data.entry_date
            period = svc.PostingService(db).get_or_create_period(data.entry_date)
            entry.period_id = period.id
        if data.description is not None:
            entry.description = data.description
        if data.branch_id is not None:
            entry.branch_id = data.branch_id
        if data.lines is not None:
            for line in list(entry.lines):
                db.delete(line)
            db.flush()
            new_lines = [
                JournalLine(entry_id=entry.id, account_id=ln.account_id,
                            debit=svc.d(ln.debit), credit=svc.d(ln.credit),
                            branch_id=ln.branch_id,
                            production_batch_id=ln.production_batch_id,
                            passport_id=ln.passport_id,
                            cost_center=ln.cost_center)
                for ln in data.lines
            ]
            svc.PostingService(db)._check_balance([
                {"debit": ln.debit, "credit": ln.credit} for ln in new_lines
            ])
            for line in new_lines:
                db.add(line)
        db.add(AuditLog(user_id=current_user.id, action="edit_journal", module="accounting",
                        record_id=entry.id, new_value=entry.entry_number))
        db.commit()
    except svc.AccountingError as e:
        db.rollback()
        raise HTTPException(422, detail=str(e))
    db.refresh(entry)
    return entry


@router.delete("/journal/{entry_id}")
def delete_journal(entry_id: str, db: Session = Depends(get_db),
                   current_user=Depends(require_permission("accounting", "delete"))):
    entry = db.query(JournalEntry).filter(JournalEntry.id == entry_id).first()
    if not entry:
        raise HTTPException(404, detail="Journal entry not found")
    if entry.status != "draft":
        raise HTTPException(409, detail="Only draft entries can be deleted")
    db.delete(entry)
    db.add(AuditLog(user_id=current_user.id, action="delete_journal", module="accounting",
                    record_id=entry.id, new_value=entry.entry_number))
    db.commit()
    return {"detail": "Journal entry deleted"}


@router.post("/journal/{entry_id}/post", response_model=EntryOut)
def post_journal(entry_id: str, db: Session = Depends(get_db),
                 current_user=Depends(require_permission("accounting", "edit"))):
    try:
        entry = svc.PostingService(db).post_entry(entry_id, current_user.id)
        db.commit()
    except svc.AccountingError as e:
        db.rollback()
        raise HTTPException(422, detail=str(e))
    db.refresh(entry)
    return entry


@router.post("/journal/{entry_id}/reverse", response_model=EntryOut)
def reverse_journal(entry_id: str, db: Session = Depends(get_db),
                    current_user=Depends(require_permission("accounting", "edit")),
                    data: ReverseIn | None = None):
    try:
        reversal = svc.PostingService(db).reverse_entry(
            entry_id, current_user.id, data.reason if data else None
        )
        db.commit()
    except svc.AccountingError as e:
        db.rollback()
        raise HTTPException(422, detail=str(e))
    db.refresh(reversal)
    return reversal


@router.post("/opening-balances", response_model=EntryOut, status_code=201)
def post_opening_balances(data: OpeningBalancesIn, db: Session = Depends(get_db),
                          current_user=Depends(require_permission("accounting", "create"))):
    entry_date = data.entry_date or date.today()
    lines = []
    for item in data.items:
        account = db.query(Account).filter(Account.id == item.account_id).first()
        if not account:
            raise HTTPException(404, detail=f"Account {item.account_id} not found")
        amount = svc.d(item.amount)
        if (account.normal_balance or "D") == "C":
            lines.append({"account_id": account.id, "debit": 0, "credit": amount,
                          "branch_id": data.branch_id})
        else:
            lines.append({"account_id": account.id, "debit": amount, "credit": 0,
                          "branch_id": data.branch_id})
    total_debit = sum((svc.d(x["debit"]) for x in lines), Decimal("0"))
    total_credit = sum((svc.d(x["credit"]) for x in lines), Decimal("0"))
    if total_debit != total_credit:
        obe = _coa_or_default("Opening Balance Equity", "3010", "equity", "C", "equity", db)
        db.add(obe)
        db.flush()
        diff = svc.d(total_debit - total_credit)
        if diff > 0:
            lines.append({"account_id": obe.id, "debit": 0, "credit": diff,
                          "branch_id": data.branch_id})
        else:
            lines.append({"account_id": obe.id, "debit": -1 * diff, "credit": 0,
                          "branch_id": data.branch_id})
    try:
        entry = svc.PostingService(db).post_event(
            source="opening", reference_type=None, reference_id=None,
            entry_date=entry_date, branch_id=data.branch_id, lines=lines,
            description=data.description or "Opening balances", created_by=current_user.id,
        )
        db.commit()
    except svc.AccountingError as e:
        db.rollback()
        raise HTTPException(422, detail=str(e))
    db.refresh(entry)
    return entry


@router.get("/trial-balance")
def get_trial_balance(period_id: Optional[str] = None,
                      branch_id: Optional[str] = None,
                      db: Session = Depends(get_db),
                      _=Depends(require_permission("accounting", "view"))):
    return rep.trial_balance(db, period_id=period_id, branch_id=branch_id)


@router.get("/general-ledger")
def get_general_ledger(account_id: str, branch_id: Optional[str] = None,
                       from_date: Optional[date] = None, to_date: Optional[date] = None,
                       db: Session = Depends(get_db),
                       _=Depends(require_permission("accounting", "view"))):
    return rep.general_ledger(db, account_id, branch_id=branch_id,
                              from_date=from_date, to_date=to_date)


@router.get("/profit-loss")
def get_profit_loss(from_date: Optional[date] = None, to_date: Optional[date] = None,
                    branch_id: Optional[str] = None, db: Session = Depends(get_db),
                    _=Depends(require_permission("accounting", "view"))):
    return rep.profit_and_loss(db, from_date=from_date, to_date=to_date, branch_id=branch_id)


@router.get("/balance-sheet")
def get_balance_sheet(as_of: Optional[date] = None, branch_id: Optional[str] = None,
                      db: Session = Depends(get_db),
                      _=Depends(require_permission("accounting", "view"))):
    return rep.balance_sheet(db, as_of=as_of, branch_id=branch_id)


@router.get("/chart-of-accounts")
def get_chart_of_accounts(db: Session = Depends(get_db),
                          _=Depends(require_permission("accounting", "view"))):
    accounts = db.query(Account).order_by(Account.code).all()
    nodes = {}
    for a in accounts:
        nodes[a.id] = {
            "id": a.id,
            "code": a.code,
            "name": a.name,
            "account_type": a.account_type,
            "normal_balance": a.normal_balance,
            "branch_id": a.branch_id,
            "parent_id": a.parent_id,
            "children": [],
        }
    roots = []
    for node in nodes.values():
        if node["parent_id"] and node["parent_id"] in nodes:
            nodes[node["parent_id"]]["children"].append(node)
        else:
            roots.append(node)

    def sort_tree(items):
        items.sort(key=lambda x: x["code"] or "")
        for item in items:
            sort_tree(item["children"])

    sort_tree(roots)
    return roots


@router.post("/balances/recompute")
def recompute_balances(db: Session = Depends(get_db),
                       current_user=Depends(require_permission("accounting", "edit"))):
    bal_svc.recompute_account_balances(db)
    db.add(AuditLog(user_id=current_user.id, action="recompute_balances", module="accounting"))
    db.commit()
    return {"detail": "Account balances recomputed"}


@router.get("/payments/aging")
def get_payments_aging(party_type: str = "customer",
                       as_of: Optional[date] = None,
                       db: Session = Depends(get_db),
                       _=Depends(require_permission("accounting", "view"))):
    rows = party_aging(db, party_type, as_of)
    return {
        "party_type": party_type,
        "total_balance": round(sum(r["balance"] for r in rows), 2),
        "rows": rows,
    }


@router.get("/parties/{party_type}/{party_id}/ledger")
def get_party_ledger(party_type: str, party_id: str,
                     db: Session = Depends(get_db),
                     _=Depends(require_permission("accounting", "view"))):
    events = party_ledger(db, party_type, party_id)
    return {"party_type": party_type, "party_id": party_id, "events": events}


@router.get("/cash-flow")
def get_cash_flow(from_date: Optional[date] = None, to_date: Optional[date] = None,
                  branch_id: Optional[str] = None, db: Session = Depends(get_db),
                  _=Depends(require_permission("accounting", "view"))):
    return rep.cash_flow(db, from_date=from_date, to_date=to_date, branch_id=branch_id)


@router.get("/pnl-by-branch")
def get_pnl_by_branch(from_date: Optional[date] = None, to_date: Optional[date] = None,
                      db: Session = Depends(get_db),
                      _=Depends(require_permission("accounting", "view"))):
    return rep.pnl_by_branch(db, from_date=from_date, to_date=to_date)


@router.get("/dashboard-metrics")
def get_dashboard_metrics(as_of: Optional[date] = None, db: Session = Depends(get_db),
                          _=Depends(require_permission("accounting", "view"))):
    return rep.dashboard_metrics(db, as_of)


def _report_rows(kind: str, db: Session, *, account_id: Optional[str] = None,
                 from_date: Optional[date] = None, to_date: Optional[date] = None,
                 as_of: Optional[date] = None, branch_id: Optional[str] = None,
                 period_id: Optional[str] = None, party_type: str = "customer") -> list[dict]:
    if kind == "trial-balance":
        tb = rep.trial_balance(db, period_id=period_id, branch_id=branch_id)
        rows = [{"Code": ln.get("code"), "Account": ln.get("name"),
                 "Type": ln.get("account_type"), "Normal": ln.get("normal_balance"),
                 "Debit": ln.get("debit"), "Credit": ln.get("credit")} for ln in tb.get("lines", [])]
        rows.append({"Code": "", "Account": "TOTAL", "Debit": tb.get("total_debit"),
                     "Credit": tb.get("total_credit")})
        return rows
    if kind == "general-ledger":
        gl = rep.general_ledger(db, account_id or "", branch_id=branch_id,
                                from_date=from_date, to_date=to_date)
        rows = [{"Date": ln.get("entry_date"), "Entry": ln.get("entry_number"),
                 "Description": ln.get("description"), "Source": ln.get("source"),
                 "Debit": ln.get("debit"), "Credit": ln.get("credit"),
                 "Balance": ln.get("balance")} for ln in gl.get("lines", [])]
        if gl.get("found"):
            rows.append({"Date": "", "Entry": "", "Description": "Closing balance",
                         "Debit": "", "Credit": "", "Balance": gl.get("closing_balance")})
        return [{"Account Code": gl.get("code"), "Account Name": gl.get("name")}] + rows
    if kind == "profit-loss":
        pnl = rep.profit_and_loss(db, from_date=from_date, to_date=to_date, branch_id=branch_id)
        rows = [{"Section": "Revenue", "Code": r.get("code"), "Account": r.get("name"),
                 "Amount": r.get("amount")} for r in pnl.get("revenue", [])]
        rows.append({"Section": "Revenue", "Code": "", "Account": "Total Revenue",
                     "Amount": pnl.get("total_revenue")})
        rows += [{"Section": "COGS", "Code": r.get("code"), "Account": r.get("name"),
                  "Amount": r.get("amount")} for r in pnl.get("cogs", [])]
        rows.append({"Section": "COGS", "Code": "", "Account": "Total COGS",
                     "Amount": pnl.get("total_cogs")})
        rows.append({"Section": "", "Code": "", "Account": "Gross Profit",
                     "Amount": pnl.get("gross_profit")})
        rows += [{"Section": "Expenses", "Code": r.get("code"), "Account": r.get("name"),
                  "Amount": r.get("amount")} for r in pnl.get("expenses", [])]
        rows.append({"Section": "Expenses", "Code": "", "Account": "Total Expenses",
                     "Amount": pnl.get("total_expenses")})
        rows.append({"Section": "", "Code": "", "Account": "Net Profit",
                     "Amount": pnl.get("net_profit")})
        return rows
    if kind == "balance-sheet":
        bs = rep.balance_sheet(db, as_of=as_of, branch_id=branch_id)
        rows = [{"Section": "Assets", "Code": r.get("code"), "Account": r.get("name"),
                 "Amount": r.get("amount")} for r in bs.get("assets", [])]
        rows.append({"Section": "Assets", "Code": "", "Account": "Total Assets",
                     "Amount": bs.get("total_assets")})
        rows += [{"Section": "Liabilities", "Code": r.get("code"), "Account": r.get("name"),
                  "Amount": r.get("amount")} for r in bs.get("liabilities", [])]
        rows.append({"Section": "Liabilities", "Code": "", "Account": "Total Liabilities",
                     "Amount": bs.get("total_liabilities")})
        rows += [{"Section": "Equity", "Code": r.get("code"), "Account": r.get("name"),
                  "Amount": r.get("amount")} for r in bs.get("equity", [])]
        rows.append({"Section": "Equity", "Code": "", "Account": "Total Equity",
                     "Amount": bs.get("total_equity")})
        return rows
    if kind == "cash-flow":
        cf = rep.cash_flow(db, from_date=from_date, to_date=to_date, branch_id=branch_id)
        rows = [{"Date": r.get("entry_date"), "Entry": r.get("entry_number"),
                 "Description": r.get("description"), "Source": r.get("source"),
                 "Bucket": r.get("buckets"), "Amount": r.get("amount")}
                for r in cf.get("detail", [])]
        return rows + [
            {"Date": "", "Entry": "", "Description": "Operating", "Amount": cf.get("operating")},
            {"Date": "", "Entry": "", "Description": "Investing", "Amount": cf.get("investing")},
            {"Date": "", "Entry": "", "Description": "Financing", "Amount": cf.get("financing")},
            {"Date": "", "Entry": "", "Description": "Net Cash Change", "Amount": cf.get("net_cash_change")},
        ]
    if kind == "aging":
        rows = [{"Party ID": r.get("party_id"), "Party": r.get("party_name"),
                 "Code": r.get("party_code"), "Balance": r.get("balance"),
                 "0-30": r.get("bucket_30"), "31-60": r.get("bucket_60"),
                 "61-90": r.get("bucket_90"), "91+": r.get("bucket_91")}
                for r in party_aging(db, party_type, as_of or date.today())]
        return rows
    if kind == "cashbook":
        q = db.query(CashbookMovement)
        if branch_id:
            q = q.filter((CashbookMovement.branch_id == branch_id) |
                         (CashbookMovement.branch_id.is_(None)))
        if from_date:
            q = q.filter(CashbookMovement.movement_date >= from_date)
        if to_date:
            q = q.filter(CashbookMovement.movement_date <= to_date)
        return [{"Date": m.movement_date.isoformat(), "Number": m.movement_number,
                 "Type": m.type, "Amount": float(m.amount), "Reference": m.reference_type,
                 "Notes": m.notes or ""}
                for m in q.order_by(CashbookMovement.movement_date,
                                    CashbookMovement.movement_number).all()]
    if kind == "loans":
        from app.models.accounting import Loan as LoanModel
        from app.services.loan_service import loan_detail as loan_detail_service
        rows = []
        for loan in db.query(LoanModel).order_by(LoanModel.created_at.desc()).all():
            detail = loan_detail_service(db, loan)
            rows.append({"Loan": detail["loan_number"], "Lender": detail["lender_name"],
                         "Principal": detail["principal"],
                         "Total Interest": detail["total_interest"],
                         "Total Paid": detail["total_paid"],
                         "Remaining": detail["remaining_principal"],
                         "Status": detail["status"]})
        return rows
    if kind == "pnl-by-branch":
        data = rep.pnl_by_branch(db, from_date=from_date, to_date=to_date)
        rows = []
        for b in data.get("branches", []):
            pnl = b.get("pnl", {})
            rows.append({"Branch Code": b.get("branch_code"), "Branch": b.get("branch_name"),
                         "Revenue": pnl.get("total_revenue"), "COGS": pnl.get("total_cogs"),
                         "Expenses": pnl.get("total_expenses"), "Net Profit": pnl.get("net_profit")})
        return rows
    raise HTTPException(404, detail=f"Unknown report kind '{kind}'")


_REPORT_KINDS = {"trial-balance", "general-ledger", "profit-loss", "balance-sheet",
                 "cash-flow", "aging", "cashbook", "loans", "pnl-by-branch"}


@router.get("/reports/{kind}/export.csv")
def export_report_csv(kind: str, account_id: Optional[str] = None,
                      from_date: Optional[date] = None, to_date: Optional[date] = None,
                      as_of: Optional[date] = None, branch_id: Optional[str] = None,
                      period_id: Optional[str] = None, party_type: str = "customer",
                      db: Session = Depends(get_db),
                      _=Depends(require_permission("accounting", "view"))):
    if kind not in _REPORT_KINDS:
        raise HTTPException(404, detail=f"Unknown report kind '{kind}'")
    rows = _report_rows(kind, db, account_id=account_id, from_date=from_date,
                        to_date=to_date, as_of=as_of, branch_id=branch_id,
                        period_id=period_id, party_type=party_type)
    headers = []
    for row in rows:
        for key in row:
            if key not in headers:
                headers.append(key)
    buffer = io.StringIO()
    writer = csv.DictWriter(buffer, fieldnames=headers)
    writer.writeheader()
    for row in rows:
        writer.writerow(row)
    return StreamingResponse(
        io.BytesIO(buffer.getvalue().encode("utf-8-sig")),
        media_type="text/csv; charset=utf-8",
        headers={"Content-Disposition": f"attachment; filename={kind}.csv"},
    )


@router.get("/reports/{kind}/export.xlsx")
def export_report_xlsx(kind: str, account_id: Optional[str] = None,
                       from_date: Optional[date] = None, to_date: Optional[date] = None,
                       as_of: Optional[date] = None, branch_id: Optional[str] = None,
                       period_id: Optional[str] = None, party_type: str = "customer",
                       db: Session = Depends(get_db),
                       _=Depends(require_permission("accounting", "view"))):
    if kind not in _REPORT_KINDS:
        raise HTTPException(404, detail=f"Unknown report kind '{kind}'")
    try:
        from openpyxl import Workbook
    except ImportError:
        raise HTTPException(501, detail="openpyxl is not installed")
    rows = _report_rows(kind, db, account_id=account_id, from_date=from_date,
                        to_date=to_date, as_of=as_of, branch_id=branch_id,
                        period_id=period_id, party_type=party_type)
    headers = []
    for row in rows:
        for key in row:
            if key not in headers:
                headers.append(key)
    workbook = Workbook()
    sheet = workbook.active
    sheet.title = "Report"
    sheet.append(headers)
    for row in rows:
        sheet.append([row.get(key, "") for key in headers])
    buffer = io.BytesIO()
    workbook.save(buffer)
    buffer.seek(0)
    return StreamingResponse(
        buffer,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": f"attachment; filename={kind}.xlsx"},
    )


@router.get("/reports/{kind}/export.pdf")
def export_report_pdf(kind: str, account_id: Optional[str] = None,
                      from_date: Optional[date] = None, to_date: Optional[date] = None,
                      as_of: Optional[date] = None, branch_id: Optional[str] = None,
                      period_id: Optional[str] = None, party_type: str = "customer",
                      db: Session = Depends(get_db),
                      _=Depends(require_permission("accounting", "view"))):
    if kind not in _REPORT_KINDS:
        raise HTTPException(404, detail=f"Unknown report kind '{kind}'")
    try:
        from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer
        from reportlab.lib.pagesizes import A4
        from reportlab.lib.units import cm
        from reportlab.lib import colors
        from reportlab.lib.styles import getSampleStyleSheet
    except ImportError:
        raise HTTPException(501, detail="reportlab is not installed")
    rows = _report_rows(kind, db, account_id=account_id, from_date=from_date,
                        to_date=to_date, as_of=as_of, branch_id=branch_id,
                        period_id=period_id, party_type=party_type)
    headers = []
    for row in rows:
        for key in row:
            if key not in headers:
                headers.append(key)
    buffer = io.BytesIO()
    doc = SimpleDocTemplate(buffer, pagesize=A4, leftMargin=1.5 * cm, rightMargin=1.5 * cm,
                            topMargin=1.5 * cm, bottomMargin=1.5 * cm)
    styles = getSampleStyleSheet()
    story = [
        Paragraph(f"NurseryOps - {kind.replace('-', ' ').title()} Report", styles["Title"]),
        Spacer(1, 0.4 * cm),
    ]
    ncols = len(headers)
    col_w = max(0.5 * cm, 17.8 * cm / max(ncols, 1))
    data = [headers] + [[row.get(key, "") for key in headers] for row in rows]
    table = Table(data, colWidths=[col_w] * ncols, repeatRows=1)
    table.setStyle(TableStyle([
        ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#166534")),
        ("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
        ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
        ("FONTSIZE", (0, 0), (-1, 0), 8),
        ("FONTSIZE", (0, 1), (-1, -1), 8),
        ("GRID", (0, 0), (-1, -1), 0.3, colors.HexColor("#d1d5db")),
        ("ROWBACKGROUNDS", (0, 1), (-1, -1), [colors.white, colors.HexColor("#f0fdf4")]),
        ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
        ("TOPPADDING", (0, 0), (-1, -1), 3),
        ("BOTTOMPADDING", (0, 0), (-1, -1), 3),
    ]))
    story.append(table)
    doc.build(story)
    buffer.seek(0)
    return StreamingResponse(
        buffer,
        media_type="application/pdf",
        headers={"Content-Disposition": f"attachment; filename={kind}.pdf"},
    )