from __future__ import annotations

from .shared import (
    Any,
    CASHFLOW_KIND_SQL,
    date,
    read_db,
    read_operation,
    normalize_date,
    normalize_month,
    ok,
    row_to_dict,
)

from .budgets_write_services import create_ledger_budget as create_ledger_budget
from .transactions_write_services import create_ledger_transaction as create_ledger_transaction
from .transactions_write_services import delete_ledger_transaction as delete_ledger_transaction
from .transactions_write_services import update_ledger_transaction as update_ledger_transaction

@read_operation
def list_ledger_transactions(start_date: str = "", end_date: str = "", type: str = "", category: str = "", person: str = "", account_id: str = "", include_deleted: bool = False, limit: int = 100, db_path: str | None = None) -> dict[str, Any]:
    clauses=[]; params=[]
    if not include_deleted:
        clauses.append("status='active'")
    for field, value in [("type", type), ("category", category), ("person", person), ("account_id", account_id)]:
        if value:
            clauses.append(f"{field}=?"); params.append(value)
    if start_date:
        clauses.append("transaction_date>=?"); params.append(normalize_date(start_date))
    if end_date:
        clauses.append("transaction_date<=?"); params.append(normalize_date(end_date))
    where = " WHERE " + " AND ".join(clauses) if clauses else ""
    sql = f"SELECT * FROM transactions{where} ORDER BY transaction_date DESC, created_at DESC LIMIT ?"
    params.append(max(1, min(int(limit), 1000)))
    with read_db(db_path) as conn:
        rows = conn.execute(sql, params).fetchall()
    return ok(transactions=[row_to_dict(r) for r in rows], count=len(rows))

from .reconciliation_service import get_balance_reconciliation_report as get_balance_reconciliation_report

@read_operation
def list_ledger_budgets(month: str = "", db_path: str | None = None) -> dict[str, Any]:
    clauses=[]; params=[]
    if month:
        clauses.append("month=?"); params.append(normalize_month(month))
    where = " WHERE " + " AND ".join(clauses) if clauses else ""
    with read_db(db_path) as conn:
        rows = conn.execute(f"SELECT * FROM budgets{where} ORDER BY month DESC, category", params).fetchall()
    return ok(budgets=[row_to_dict(r) for r in rows], count=len(rows))

def _month_range(month: str) -> tuple[str, str]:
    m = normalize_month(month)
    y, mo = map(int, m.split("-"))
    start = date(y, mo, 1)
    end = date(y + (mo == 12), 1 if mo == 12 else mo + 1, 1)
    return start.isoformat(), end.isoformat()

@read_operation
def get_monthly_cashflow_report(month: str = "", db_path: str | None = None) -> dict[str, Any]:
    month_norm = normalize_month(month); start, next_month = _month_range(month_norm)
    with read_db(db_path) as conn:
        type_rows = conn.execute("""SELECT type, SUM(amount) AS total FROM transactions
                                   WHERE status='active' AND transaction_date>=? AND transaction_date<?
                                   GROUP BY type""", (start, next_month)).fetchall()
        cashflow_rows = conn.execute(f"""SELECT {CASHFLOW_KIND_SQL} AS cashflow_kind, SUM(t.amount) AS total
                                       FROM transactions t
                                       LEFT JOIN ledger_categories lc
                                         ON lc.active=1 AND t.category=lc.category AND t.subcategory=lc.subcategory
                                      WHERE t.status='active' AND t.transaction_date>=? AND t.transaction_date<?
                                      GROUP BY {CASHFLOW_KIND_SQL}""", (start, next_month)).fetchall()
        category_rows = conn.execute(f"""SELECT {CASHFLOW_KIND_SQL} AS cashflow_kind,
                                              t.category, t.subcategory, t.currency,
                                              SUM(t.amount) AS amount, COUNT(*) AS count
                                       FROM transactions t
                                       LEFT JOIN ledger_categories lc
                                         ON lc.active=1 AND t.category=lc.category AND t.subcategory=lc.subcategory
                                      WHERE t.status='active' AND t.transaction_date>=? AND t.transaction_date<?
                                      GROUP BY {CASHFLOW_KIND_SQL}, t.category, t.subcategory, t.currency
                                      ORDER BY cashflow_kind, amount DESC""", (start, next_month)).fetchall()
    type_totals = {r["type"]: int(r["total"] or 0) for r in type_rows}
    cashflow_totals = {r["cashflow_kind"]: int(r["total"] or 0) for r in cashflow_rows}
    income = cashflow_totals.get("income", 0)
    expenses = cashflow_totals.get("expense", 0)
    net_cashflow = income - expenses
    savings_rate = round(net_cashflow / income, 4) if income else None
    categories = [row_to_dict(r) for r in category_rows]
    unknown_total = {}
    unknown_category_items = []
    for row in categories:
        if row.get("cashflow_kind") == "unknown":
            currency = row.get("currency") or "미지정"
            unknown_total[currency] = unknown_total.get(currency, 0) + int(row.get("amount") or 0)
            unknown_category_items.append(row)
    return ok(month=month_norm, income=income, expenses=expenses, net_cashflow=net_cashflow, savings_rate=savings_rate, by_type=type_totals, by_cashflow_kind=cashflow_totals, categories=categories, unknown_total=unknown_total, unknown_category_items=unknown_category_items)

@read_operation
def get_settlement_cashflow_snapshot(monthly_settlement_id: str, db_path: str | None = None) -> dict[str, Any]:
    """Read only the persisted cashflow rows for a closed settlement."""
    with read_db(db_path) as conn:
        rows = conn.execute("""SELECT cashflow_kind, category, subcategory, amount,
                                     transaction_count AS count, currency
                                FROM settlement_cashflow_snapshots
                               WHERE monthly_settlement_id=?
                               ORDER BY cashflow_kind, category, subcategory, currency""", (monthly_settlement_id,)).fetchall()
        settlement = conn.execute("SELECT status FROM monthly_settlement WHERE id=?", (monthly_settlement_id,)).fetchone()
    categories = [row_to_dict(row) for row in rows]
    if not categories:
        saved = bool(settlement and settlement["status"] == "closed")
        return ok(saved=saved, categories=[], income=0 if saved else None, expenses=0 if saved else None,
                  net_cashflow=0 if saved else None, month="", message="저장된 현금흐름이 비어 있습니다." if saved else "저장된 현금흐름 스냅샷이 없습니다.")
    by_currency = {}
    for row in categories:
        cur = row.get("currency") or "미지정"
        bucket = by_currency.setdefault(cur, {"income": 0, "expenses": 0, "net_cashflow": 0})
        if row["cashflow_kind"] == "income": bucket["income"] += int(row["amount"] or 0)
        elif row["cashflow_kind"] == "expense": bucket["expenses"] += int(row["amount"] or 0)
    for bucket in by_currency.values(): bucket["net_cashflow"] = bucket["income"] - bucket["expenses"]
    only = next(iter(by_currency.values())) if len(by_currency) == 1 else None
    unknown_total = {}
    unknown_category_items = []
    for row in categories:
        if row.get("cashflow_kind") == "unknown":
            currency = row.get("currency") or "미지정"
            unknown_total[currency] = unknown_total.get(currency, 0) + int(row.get("amount") or 0)
            unknown_category_items.append(row)
    return ok(saved=True, categories=categories, unknown_total=unknown_total, unknown_category_items=unknown_category_items, by_currency=by_currency,
              income=only["income"] if only else None,
              expenses=only["expenses"] if only else None,
              net_cashflow=only["net_cashflow"] if only else None, month="")

@read_operation
def get_category_spending_report(month: str = "", start_date: str = "", end_date: str = "", person: str = "", db_path: str | None = None) -> dict[str, Any]:
    if month:
        start_date, next_month = _month_range(month); end_op = "<"; end_value = next_month
    else:
        start_date = normalize_date(start_date, default_today=False) if start_date else "0001-01-01"
        end_value = normalize_date(end_date, default_today=False) if end_date else "9999-12-31"; end_op = "<="
    params=[start_date, end_value]; person_clause=""
    if person:
        person_clause=" AND t.person=?"; params.append(person)
    sql=f"""SELECT t.category, t.subcategory, SUM(t.amount) AS total, COUNT(*) AS count FROM transactions t
             LEFT JOIN ledger_categories lc ON lc.active=1 AND t.category=lc.category AND t.subcategory=lc.subcategory
             WHERE t.status='active' AND {CASHFLOW_KIND_SQL}='expense' AND t.transaction_date>=? AND t.transaction_date{end_op}?{person_clause}
             GROUP BY t.category, t.subcategory ORDER BY total DESC"""
    with read_db(db_path) as conn:
        rows=conn.execute(sql, params).fetchall()
    total=sum(int(r["total"] or 0) for r in rows)
    items=[{"category": r["category"], "subcategory": r["subcategory"], "amount": int(r["total"] or 0), "count": r["count"], "share": round((int(r["total"] or 0)/total),4) if total else 0} for r in rows]
    return ok(total_expense=total, items=items, count=len(items))

@read_operation
def get_fixed_cost_report(month: str = "", db_path: str | None = None) -> dict[str, Any]:
    month_norm=normalize_month(month); start,next_month=_month_range(month_norm)
    with read_db(db_path) as conn:
        rows=conn.execute(f"""SELECT t.category, t.merchant, t.amount, t.transaction_date, t.person, t.memo FROM transactions t
                             LEFT JOIN ledger_categories lc ON lc.active=1 AND t.category=lc.category AND t.subcategory=lc.subcategory
                             WHERE t.status='active' AND {CASHFLOW_KIND_SQL}='expense' AND t.is_fixed=1 AND t.transaction_date>=? AND t.transaction_date<?
                             ORDER BY t.amount DESC""", (start,next_month)).fetchall()
    items=[row_to_dict(r) for r in rows]
    return ok(month=month_norm, total_fixed_cost=sum(i["amount"] for i in items), items=items, count=len(items))

@read_operation
def get_budget_vs_actual_report(month: str = "", db_path: str | None = None) -> dict[str, Any]:
    month_norm=normalize_month(month); start,next_month=_month_range(month_norm)
    with read_db(db_path) as conn:
        budgets=conn.execute("SELECT * FROM budgets WHERE month=?", (month_norm,)).fetchall()
        actuals=conn.execute(f"""SELECT t.category, t.subcategory, t.person, SUM(t.amount) AS amount FROM transactions t
                                LEFT JOIN ledger_categories lc ON lc.active=1 AND t.category=lc.category AND t.subcategory=lc.subcategory
                                WHERE t.status='active' AND {CASHFLOW_KIND_SQL}='expense' AND t.transaction_date>=? AND t.transaction_date<?
                                GROUP BY t.category, t.subcategory, t.person""", (start,next_month)).fetchall()
    actual_map={(r["category"], r["subcategory"], r["person"]): int(r["amount"] or 0) for r in actuals}
    items=[]
    for b in budgets:
        key=(b["category"], b["subcategory"], b["person"]); actual=actual_map.get(key,0); budget=int(b["amount"])
        items.append({"category": b["category"], "subcategory": b["subcategory"], "person": b["person"], "budget": budget, "actual": actual, "remaining": budget-actual, "usage_rate": round(actual/budget,4) if budget else None, "status": "over" if actual>budget else "ok"})
    return ok(month=month_norm, items=items, total_budget=sum(i["budget"] for i in items), total_actual=sum(i["actual"] for i in items))

@read_operation
def get_investable_cashflow_report(month: str = "", minimum_cash_buffer: int = 0, planned_extra_expense: int = 0, db_path: str | None = None) -> dict[str, Any]:
    cashflow=get_monthly_cashflow_report(month, db_path)
    if not cashflow.get("success"):
        return cashflow
    investable=max(0, int(cashflow["net_cashflow"]) - int(minimum_cash_buffer) - int(planned_extra_expense))
    return ok(month=cashflow["month"], income=cashflow["income"], expenses=cashflow["expenses"], net_cashflow=cashflow["net_cashflow"], minimum_cash_buffer=int(minimum_cash_buffer), planned_extra_expense=int(planned_extra_expense), investable_cashflow=investable)