"""Phase 4 finance postings: expenses (with approval/recurring), income and
cash/bank transfers. Mirrors the post patterns in transaction_postings.py:
every event posts via PostingService.post_event (idempotent by source/reference
key) and writes CashbookMovement rows.
"""
import uuid
from datetime import date, datetime
from calendar import monthrange
from decimal import Decimal
from sqlalchemy.orm import Session
from app.models.finance import Account, Expense, Income, JournalEntry
from app.models.accounting import CashbookMovement
from app.services.accounting import AccountingError, PostingService, d
from app.services.transaction_postings import resolve_cash_account, _next_movement_number

# (substring-tuples, COA code) - first match wins
_EXPENSE_RULES = (
    (("rent", "lease"), "6010"),
    (("salary", "wage", "labor", "labour", "staff", "payroll"), "6020"),
    (("transport", "fuel", "petrol", "diesel", "delivery", "courier"), "6030"),
    (("electric", "water", "utility", "utilities", "power"), "6040"),
    (("internet", "communication", "phone", "telecom", "mobile"), "6050"),
    (("marketing", "advertis", "promot", "promotion"), "6060"),
    (("packag", "packing"), "6070"),
    (("seed", "seedling", "propagation", "bulb", "cutting"), "6080"),
    (("media", "soil", "substrate", "peat"), "6090"),
    (("fertil", "nutrient", "npk", "compost", "manure"), "6100"),
    (("treatment", "pesticide", "agrochem", "chemical", "fungicide", "insecticide",
      "herbicide", "medicine"), "6110"),
    (("overhead", "general", "admin", "office", "misc"), "6120"),
    (("loss", "mortality", "damage", "waste"), "6130"),
    (("maintenance", "repair", "fix", "service"), "6140"),
    (("bank", "interest", "charge", "fee"), "6150"),
)

_INCOME_RULES = (
    (("wholesale", "bulk"), "4020"),
    (("agro", "seed", "input", "input shop"), "4030"),
    (("sales", "retail", "plant", "garden"), "4010"),
)


def _expense_code(category: str | None) -> str:
    text = (category or "").lower()
    for needles, code in _EXPENSE_RULES:
        if any(n in text for n in needles):
            return code
    return "6990"


def _income_code(category: str | None) -> str:
    text = (category or "").lower()
    for needles, code in _INCOME_RULES:
        if any(n in text for n in needles):
            return code
    return "4060"


def _account_by_code(db: Session, code: str) -> Account:
    acct = db.query(Account).filter(Account.code == code).first()
    if not acct:
        raise AccountingError(f"COA account {code} is missing; seed the chart of accounts")
    return acct


def resolve_expense_account(db: Session, category: str | None = None,
                            account_id: str | None = None) -> Account:
    if account_id:
        acct = db.query(Account).filter(Account.id == account_id).first()
        if acct:
            return acct
        raise AccountingError("Expense account not found")
    return _account_by_code(db, _expense_code(category))


def resolve_income_account(db: Session, category: str | None = None,
                           account_id: str | None = None) -> Account:
    if account_id:
        acct = db.query(Account).filter(Account.id == account_id).first()
        if acct:
            return acct
        raise AccountingError("Income account not found")
    return _account_by_code(db, _income_code(category))


def expense_posting_entry(db: Session, expense: Expense) -> JournalEntry | None:
    return db.query(JournalEntry).filter(
        JournalEntry.source == "expense",
        JournalEntry.reference_type == "expense",
        JournalEntry.reference_id == expense.id,
        JournalEntry.is_reversal == False,  # noqa: E712
    ).first()


def income_posting_entry(db: Session, income: Income) -> JournalEntry | None:
    return db.query(JournalEntry).filter(
        JournalEntry.source == "income",
        JournalEntry.reference_type == "income",
        JournalEntry.reference_id == income.id,
        JournalEntry.is_reversal == False,  # noqa: E712
    ).first()


def post_expense(db: Session, expense_id: str, user_id: str,
                 approve: bool = False) -> JournalEntry:
    expense = db.query(Expense).filter(Expense.id == expense_id).first()
    if not expense:
        raise AccountingError("Expense not found")
    if expense.approval_status == "rejected":
        raise AccountingError("A rejected expense cannot be posted")
    if expense.approval_status == "pending":
        if not approve:
            raise AccountingError("Expense is pending approval")
        expense.approval_status = "approved"
        expense.approved_by = user_id
        expense.approved_at = datetime.now()
        db.flush()
    existing = expense_posting_entry(db, expense)
    if existing:
        if existing.status == "posted":
            return existing
        raise AccountingError("A draft posting already exists for this expense")
    amount = d(expense.amount)
    if amount <= 0:
        raise AccountingError("Expense amount must be positive")
    expense_acct = resolve_expense_account(db, expense.category, expense.account_id)
    cash_acct = resolve_cash_account(db, expense.branch_id, expense.payment_method)
    entry = PostingService(db).post_event(
        source="expense", reference_type="expense", reference_id=expense.id,
        entry_date=expense.date, branch_id=expense.branch_id,
        lines=[
            {"account_id": expense_acct.id, "debit": amount},
            {"account_id": cash_acct.id, "credit": amount},
        ],
        description=f"Expense {expense.expense_number} - {expense.description or expense.category}",
        created_by=user_id,
    )
    db.add(CashbookMovement(
        movement_number=_next_movement_number(db),
        type="payment",
        account_id=cash_acct.id,
        amount=amount,
        movement_date=expense.date,
        branch_id=expense.branch_id,
        journal_entry_id=entry.id,
        reference_type="expense",
        reference_id=expense.id,
        notes=f"Expense {expense.expense_number}",
        created_by=user_id,
    ))
    db.flush()
    return entry


def reverse_expense(db: Session, expense_id: str, user_id: str,
                    reason: str | None = None) -> JournalEntry:
    expense = db.query(Expense).filter(Expense.id == expense_id).first()
    if not expense:
        raise AccountingError("Expense not found")
    entry = expense_posting_entry(db, expense)
    if not entry or entry.status != "posted":
        raise AccountingError("Expense is not posted")
    return PostingService(db).reverse_entry(entry.id, user_id, reason or "Expense reversal")


def post_income(db: Session, income_id: str, user_id: str) -> JournalEntry:
    income = db.query(Income).filter(Income.id == income_id).first()
    if not income:
        raise AccountingError("Income not found")
    existing = income_posting_entry(db, income)
    if existing:
        if existing.status == "posted":
            return existing
        raise AccountingError("A draft posting already exists for this income")
    amount = d(income.amount)
    if amount <= 0:
        raise AccountingError("Income amount must be positive")
    income_acct = resolve_income_account(db, income.category, income.account_id)
    cash_acct = resolve_cash_account(db, income.branch_id, income.payment_method)
    entry = PostingService(db).post_event(
        source="income", reference_type="income", reference_id=income.id,
        entry_date=income.date, branch_id=income.branch_id,
        lines=[
            {"account_id": cash_acct.id, "debit": amount},
            {"account_id": income_acct.id, "credit": amount},
        ],
        description=f"Income {income.income_number} - {income.description or income.category}",
        created_by=user_id,
    )
    db.add(CashbookMovement(
        movement_number=_next_movement_number(db),
        type="receipt",
        account_id=cash_acct.id,
        amount=amount,
        movement_date=income.date,
        branch_id=income.branch_id,
        journal_entry_id=entry.id,
        reference_type="income",
        reference_id=income.id,
        notes=f"Income {income.income_number}",
        created_by=user_id,
    ))
    db.flush()
    return entry


def reverse_income(db: Session, income_id: str, user_id: str,
                   reason: str | None = None) -> JournalEntry:
    income = db.query(Income).filter(Income.id == income_id).first()
    if not income:
        raise AccountingError("Income not found")
    entry = income_posting_entry(db, income)
    if not entry or entry.status != "posted":
        raise AccountingError("Income is not posted")
    return PostingService(db).reverse_entry(entry.id, user_id, reason or "Income reversal")


def _add_months(start: date, months: int) -> date:
    month_index = start.month - 1 + months
    year = start.year + month_index // 12
    month = month_index % 12 + 1
    last_day = monthrange(year, month)[1]
    return date(year, month, min(start.day, last_day))


def _next_recurring_due(base_date: date, frequency: str, ref_date: date) -> date | None:
    step = {"monthly": 1, "quarterly": 3, "yearly": 12}.get((frequency or "monthly").lower())
    if not step:
        return None
    due = _add_months(base_date, step)
    while due < ref_date:
        due = _add_months(due, step)
        if (due - base_date).days > 365 * 20:  # safety valve
            return None
    return due


def generate_recurring_expenses(db: Session, user_id: str) -> list[Expense]:
    """Clone every due recurring expense as a pending (unposted) instance.
    Top-level recurring rows only: clones carry a reference marker RX-<parent>/<date>."""
    today = date.today()
    parents = db.query(Expense).filter(
        Expense.recurring == True,  # noqa: E712
        Expense.approval_status != "rejected",
        Expense.reference.is_(None),
    ).all()
    created = []
    for parent in parents:
        if parent.recurring_until and parent.recurring_until < _add_months(today, 1):
            continue
        due = _next_recurring_due(parent.date, parent.recurring_frequency, today)
        if due is None:
            continue
        if parent.recurring_until and due > parent.recurring_until:
            continue
        marker = f"RX-{parent.expense_number}"
        exists = db.query(Expense).filter(
            Expense.reference == marker, Expense.date == due
        ).first()
        if exists:
            continue
        child = Expense(
            expense_number=_next_expense_number(db),
            category=parent.category,
            description=parent.description,
            amount=parent.amount,
            date=due,
            payment_method=parent.payment_method,
            account_id=parent.account_id,
            branch_id=parent.branch_id,
            reference=marker,
            notes=parent.notes,
            approval_status="pending",
            recurring=False,
            created_by=user_id,
        )
        db.add(child)
        created.append(child)
    db.flush()
    return created


def _next_expense_number(db: Session) -> str:
    year = datetime.now().year
    last = db.query(Expense).filter(
        Expense.expense_number.like(f"EXP-{year}-%")
    ).order_by(Expense.expense_number.desc()).first()
    seq = (int(last.expense_number.split("-")[-1]) + 1) if last else 1
    return f"EXP-{year}-{seq:06d}"


def _as_cashlike(account: Account) -> bool:
    return (account.category or "") in ("cash", "bank") or (account.code or "").startswith(("1010", "1020"))


def transfer_funds(db: Session, *, from_account_id: str, to_account_id: str,
                   amount: float, movement_date: date, branch_id: str | None = None,
                   notes: str | None = None, created_by: str,
                   out_type: str = "transfer_out", in_type: str = "transfer_in") -> dict:
    value = d(amount)
    if value <= 0:
        raise AccountingError("Transfer amount must be positive")
    if from_account_id == to_account_id:
        raise AccountingError("Source and destination accounts must differ")
    source = db.query(Account).filter(Account.id == from_account_id).first()
    target = db.query(Account).filter(Account.id == to_account_id).first()
    if not source:
        raise AccountingError("Source account not found")
    if not target:
        raise AccountingError("Destination account not found")
    if not _as_cashlike(source) or not _as_cashlike(target):
        raise AccountingError("Transfers are only allowed between cash/bank accounts")

    ref = f"CT-{uuid.uuid4().hex}"
    entry = PostingService(db).post_event(
        source="transfer", reference_type="cash_transfer", reference_id=ref,
        entry_date=movement_date, branch_id=branch_id,
        lines=[
            {"account_id": source.id, "credit": value},
            {"account_id": target.id, "debit": value},
        ],
        description=f"Cash transfer {value}" + (f" - {notes}" if notes else ""),
        created_by=created_by,
    )
    movement_out = CashbookMovement(
        movement_number=_next_movement_number(db),
        type=out_type,
        account_id=source.id,
        from_account_id=source.id,
        to_account_id=target.id,
        amount=value,
        movement_date=movement_date,
        branch_id=branch_id,
        journal_entry_id=entry.id,
        reference_type="cash_transfer",
        reference_id=ref,
        notes=notes,
        created_by=created_by,
    )
    db.add(movement_out)
    db.flush()  # persist first row so the second gets a unique movement number
    movement_in = CashbookMovement(
        movement_number=_next_movement_number(db),
        type=in_type,
        account_id=target.id,
        from_account_id=source.id,
        to_account_id=target.id,
        amount=value,
        movement_date=movement_date,
        branch_id=branch_id,
        journal_entry_id=entry.id,
        reference_type="cash_transfer",
        reference_id=ref,
        notes=notes,
        created_by=created_by,
    )
    db.add(movement_in)
    db.flush()
    return {"journal_entry": entry, "movements": [movement_out, movement_in], "reference_id": ref}


def deposit_funds(db: Session, *, amount: float, movement_date: date,
                  branch_id: str | None = None, to_account_id: str | None = None,
                  notes: str | None = None, created_by: str) -> dict:
    source = resolve_cash_account(db, branch_id, "cash")
    target = (
        db.query(Account).filter(Account.id == to_account_id).first()
        if to_account_id else resolve_cash_account(db, branch_id, "bank")
    )
    if not target:
        raise AccountingError("Destination account not found")
    return transfer_funds(
        db, from_account_id=source.id, to_account_id=target.id, amount=amount,
        movement_date=movement_date, branch_id=branch_id, notes=notes or "Cash deposit",
        created_by=created_by, out_type="withdrawal", in_type="deposit",
    )


def withdraw_funds(db: Session, *, amount: float, movement_date: date,
                   branch_id: str | None = None, from_account_id: str | None = None,
                   notes: str | None = None, created_by: str) -> dict:
    source = (
        db.query(Account).filter(Account.id == from_account_id).first()
        if from_account_id else resolve_cash_account(db, branch_id, "bank")
    )
    if not source:
        raise AccountingError("Source account not found")
    target = resolve_cash_account(db, branch_id, "cash")
    return transfer_funds(
        db, from_account_id=source.id, to_account_id=target.id, amount=amount,
        movement_date=movement_date, branch_id=branch_id, notes=notes or "Cash withdrawal",
        created_by=created_by, out_type="withdrawal", in_type="deposit",
    )