from __future__ import annotations

from decimal import Decimal, InvalidOperation
from sqlite3 import Connection

from fastapi import HTTPException

from jarvis_finance.audit.log import record_audit_event
from jarvis_finance.services.budget_common import new_id, now, row_to_dict


def _fmt(value: Decimal) -> str:
    return format(value.quantize(Decimal("0.01")), "f")


def _derived_amounts(row: dict) -> tuple[str, str]:
    monthly = Decimal(str(row.get("monthly_amount_chf") or "0"))
    annual = Decimal(str(row.get("annual_amount_chf") or "0"))
    cadence = str(row.get("cadence") or "monthly")
    if annual and not monthly:
        monthly = annual / Decimal("12")
    elif monthly and not annual:
        annual = monthly if cadence == "one_time" else monthly * Decimal("12")
    return _fmt(monthly), _fmt(annual)


def _income_projection(row: dict) -> tuple[str, str, str]:
    monthly, annual = _derived_amounts(row)
    monthly_average = Decimal(annual) / Decimal("12") if Decimal(annual) else Decimal("0")
    return monthly, annual, _fmt(monthly_average)


def _parse_notes(notes: str | None) -> dict[str, str]:
    result: dict[str, str] = {}
    for part in str(notes or "").replace("|", ";").split(";"):
        if "=" in part:
            k, v = part.split("=", 1)
            result[k.strip()] = v.strip()
    return result


def _notes_from_meta(meta: dict[str, str], extra: str | None = None) -> str:
    parts = [f"{key}={value}" for key, value in meta.items() if value]
    if extra:
        parts.append(str(extra))
    return ";".join(parts)


def _find_active_income_plan_by_names(conn: Connection, year: str, names: list[str]) -> dict | None:
    lowered = [n.lower() for n in names]
    placeholders = ",".join("?" for _ in lowered)
    row = conn.execute(
        f"""
        SELECT p.*, c.category_type
        FROM budget_plan_items p
        JOIN budget_categories c ON c.category_id=p.category_id
        WHERE substr(p.plan_month,1,4)=? AND p.is_active=1 AND c.category_type='income'
          AND lower(p.name) IN ({placeholders})
        ORDER BY p.created_at LIMIT 1
        """,
        [year, *lowered],
    ).fetchone()
    return row_to_dict(row) if row else None


def _category_id_by_name(conn: Connection, name: str) -> str | None:
    row = conn.execute("SELECT category_id FROM budget_categories WHERE is_active=1 AND category_type='income' AND lower(name)=lower(?) ORDER BY sort_order LIMIT 1", (name,)).fetchone()
    return str(row["category_id"]) if row else None


def _ensure_income_category(conn: Connection, name: str, *, sort_order: int = 900) -> str:
    existing = _category_id_by_name(conn, name)
    if existing:
        return existing
    category_id = new_id("bcat")
    ts = now()
    conn.execute(
        """
        INSERT INTO budget_categories(category_id, parent_category_id, name, category_type, color, icon, is_active, sort_order, created_at, updated_at)
        VALUES (?, NULL, ?, 'income', NULL, NULL, 1, ?, ?, ?)
        """,
        (category_id, name, sort_order, ts, ts),
    )
    record_audit_event(conn, source="vue_dashboard", action="income_template_category_created", entity_type="budget_category", entity_id=category_id, new_values={"name": name, "category_type": "income"}, created_by="user")
    return category_id


CADENCES = {"monthly", "quarterly", "annual", "one_time", "irregular"}

INCOME_TEMPLATE_SPECS = [
    {"name": "Lohn Marcel (ERNE)", "legacy_names": ["Lohn Marcel"], "person": "Marcel", "employer": "ERNE", "income_type": "Lohn", "cadence": "monthly", "target_month": "monatlich", "category": "Lohn Marcel"},
    {"name": "13. Monatslohn Marcel", "person": "Marcel", "income_type": "13. Monatslohn", "cadence": "annual", "target_month": "Dezember", "category": "Lohn Marcel"},
    {"name": "Bonus Marcel", "person": "Marcel", "income_type": "Bonus", "cadence": "annual", "target_month": "April", "category": "Gratifikation"},
    {"name": "Lohn Melanie Musikschule", "person": "Melanie", "income_type": "Lohn", "cadence": "monthly", "target_month": "monatlich", "category": "Lohn Melanie"},
    {"name": "13. Monatslohn Melanie Musikschule", "person": "Melanie", "income_type": "13. Monatslohn", "cadence": "annual", "target_month": "Dezember", "category": "Lohn Melanie"},
    {"name": "Lohn Melanie – Gasser Bauunternehmen", "person": "Melanie", "income_type": "Lohn", "cadence": "monthly", "target_month": "monatlich", "category": "Lohn Melanie"},
    {"name": "13. Monatslohn Melanie – Gasser Bauunternehmen", "person": "Melanie", "income_type": "13. Monatslohn", "cadence": "annual", "target_month": "Dezember", "category": "Lohn Melanie"},
    {"name": "Bonus Melanie", "person": "Melanie", "income_type": "Bonus", "cadence": "annual", "target_month": "April", "category": "Gratifikation"},
    {"name": "Rückvergütung Solarstrom", "person": "Haushalt", "employer": "Solarstrom", "income_type": "Rückvergütung", "cadence": "annual", "target_month": "noch festzulegen", "category": "Sonstige Einnahmen"},
    {"name": "Rückerstattungen / Sonstige Einnahmen", "person": "Haushalt", "income_type": "Rückerstattung", "cadence": "irregular", "target_month": "bei Bedarf", "category": "Rückerstattungen"},
]


def _decimal_text(value: object, *, required: bool = False) -> str | None:
    if value in (None, ""):
        if required:
            raise HTTPException(status_code=422, detail="amount is required")
        return None
    try:
        amount = Decimal(str(value).strip())
    except (InvalidOperation, ValueError):
        raise HTTPException(status_code=422, detail="invalid decimal amount") from None
    if amount < 0:
        raise HTTPException(status_code=422, detail="amount must be positive")
    return format(amount, "f")


def _normalise_plan_payload(conn: Connection, payload: dict) -> dict:
    plan_month = str(payload.get("plan_month") or "").strip()
    if len(plan_month) != 7 or plan_month[4] != "-":
        raise HTTPException(status_code=422, detail="plan_month must be YYYY-MM")
    category_id = str(payload.get("category_id") or "").strip()
    if not category_id:
        raise HTTPException(status_code=422, detail="category_id is required")
    category = conn.execute("SELECT 1 FROM budget_categories WHERE category_id=?", (category_id,)).fetchone()
    if not category:
        raise HTTPException(status_code=422, detail="category not found")
    name = str(payload.get("name") or "").strip()
    if not name:
        raise HTTPException(status_code=422, detail="name is required")
    cadence = str(payload.get("cadence") or "monthly").strip()
    if cadence not in CADENCES:
        raise HTTPException(status_code=422, detail="invalid cadence")
    monthly = _decimal_text(payload.get("monthly_amount_chf"))
    annual = _decimal_text(payload.get("annual_amount_chf"))
    meta = _parse_notes(payload.get("notes"))
    if monthly is None and annual is None and meta.get("template") != "true":
        raise HTTPException(status_code=422, detail="monthly or annual amount is required")
    return {
        "plan_item_id": payload.get("plan_item_id") or new_id("bplan"),
        "plan_month": plan_month,
        "category_id": category_id,
        "name": name,
        "monthly_amount_chf": monthly,
        "annual_amount_chf": annual,
        "cadence": cadence,
        "is_fixed_cost": bool(payload.get("is_fixed_cost", False)),
        "source_type": payload.get("source_type") or "manual",
        "notes": payload.get("notes") or None,
        "sort_order": int(payload.get("sort_order") or 999),
    }


def create_budget_plan_item_preview(conn: Connection, payload: dict) -> dict:
    normalised = _normalise_plan_payload(conn, payload)
    return {
        "preview_id": new_id("preview"),
        "summary": f"{normalised['name']} · {normalised['plan_month']} · {normalised['cadence']}",
        "warnings": [],
        "payload": normalised,
    }


def confirm_budget_plan_item(conn: Connection, payload: dict) -> dict:
    normalised = _normalise_plan_payload(conn, payload)
    existing = conn.execute(
        """
        SELECT plan_item_id FROM budget_plan_items
        WHERE plan_month=? AND category_id=? AND lower(name)=lower(?)
          AND COALESCE(monthly_amount_chf, '')=COALESCE(?, '')
          AND COALESCE(annual_amount_chf, '')=COALESCE(?, '')
          AND cadence=? AND source_type=? AND is_active=1
        ORDER BY created_at LIMIT 1
        """,
        (
            normalised["plan_month"],
            normalised["category_id"],
            normalised["name"],
            normalised["monthly_amount_chf"],
            normalised["annual_amount_chf"],
            normalised["cadence"],
            normalised["source_type"],
        ),
    ).fetchone()
    if existing:
        audit_id = record_audit_event(
            conn,
            source="vue_dashboard",
            action="idempotent_confirm",
            entity_type="budget_plan_item",
            entity_id=str(existing["plan_item_id"]),
            new_values={"deduplicated": True, "name": normalised["name"], "plan_month": normalised["plan_month"]},
            created_by="user",
        )
        conn.commit()
        return {"status": "confirmed", "entity_id": str(existing["plan_item_id"]), "audit_id": audit_id, "message": "Budgetplan bereits vorhanden"}
    ts = now()
    conn.execute(
        """
        INSERT INTO budget_plan_items(plan_item_id, plan_month, category_id, name, monthly_amount_chf, annual_amount_chf, cadence, is_fixed_cost, source_type, notes, sort_order, is_active, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, ?, ?)
        """,
        (
            normalised["plan_item_id"],
            normalised["plan_month"],
            normalised["category_id"],
            normalised["name"],
            normalised["monthly_amount_chf"],
            normalised["annual_amount_chf"],
            normalised["cadence"],
            1 if normalised["is_fixed_cost"] else 0,
            normalised["source_type"],
            normalised["notes"],
            normalised["sort_order"],
            ts,
            ts,
        ),
    )
    audit_id = record_audit_event(conn, source="vue_dashboard", action="create", entity_type="budget_plan_item", entity_id=normalised["plan_item_id"], new_values={k: v for k, v in normalised.items() if k != "plan_item_id"}, created_by="user")
    conn.commit()
    return {"status": "confirmed", "entity_id": normalised["plan_item_id"], "audit_id": audit_id, "message": "Budgetplan gespeichert"}


def list_budget_plan_items(conn: Connection, *, month: str | None = None, include_archived: bool = True) -> list[dict]:
    clauses: list[str] = []
    params: list[object] = []
    if month:
        clauses.append("p.plan_month=?")
        params.append(month)
    if not include_archived:
        clauses.append("p.is_active=1")
    where = "WHERE " + " AND ".join(clauses) if clauses else ""
    rows = conn.execute(
        f"""
        SELECT p.*, c.name AS category_name, c.category_type
        FROM budget_plan_items p
        JOIN budget_categories c ON c.category_id=p.category_id
        {where}
        ORDER BY p.plan_month DESC, c.sort_order, p.name
        """,
        params,
    ).fetchall()
    return [row_to_dict(row) | {"is_active": bool(row["is_active"]), "is_fixed_cost": bool(row["is_fixed_cost"])} for row in rows]


def list_income_plan_items(conn: Connection, *, year: str = "2026", include_archived: bool = False) -> list[dict]:
    rows = conn.execute(
        """
        SELECT p.*, c.name AS category_name, c.category_type
        FROM budget_plan_items p
        JOIN budget_categories c ON c.category_id=p.category_id
        WHERE c.category_type='income' AND substr(p.plan_month,1,4)=?
          AND (? OR p.is_active=1)
        ORDER BY p.is_active DESC, p.sort_order, c.sort_order, p.name
        """,
        (year, 1 if include_archived else 0),
    ).fetchall()
    result: list[dict] = []
    for row in rows:
        item = row_to_dict(row) | {"is_active": bool(row["is_active"]), "is_fixed_cost": bool(row["is_fixed_cost"])}
        monthly, annual, monthly_average = _income_projection(item)
        meta = _parse_notes(item.get("notes"))
        actual_row = conn.execute(
            """
            SELECT COALESCE(SUM(amount_chf),0) AS actual_ytd
            FROM budget_transactions
            WHERE status='confirmed' AND transaction_type='income'
              AND category_id=? AND substr(transaction_date,1,4)=?
            """,
            (item["category_id"], year),
        ).fetchone()
        actual_ytd = Decimal(str(actual_row["actual_ytd"] or "0")) if actual_row else Decimal("0")
        forecast_year = Decimal(annual)
        deviation_year = actual_ytd - forecast_year
        item.update({
            "monthly_value_chf": monthly,
            "annual_value_chf": annual,
            "monthly_average_chf": monthly_average,
            "actual_income_year_to_date_chf": _fmt(actual_ytd),
            "forecast_income_year_chf": _fmt(forecast_year),
            "deviation_year_chf": _fmt(deviation_year),
            "status_label": "Noch keine Planwerte" if not forecast_year else ("Im Plan" if actual_ytd >= Decimal("0") else "Prüfen"),
            "person_source": meta.get("person") or meta.get("source") or "Haushalt",
            "employer_source": meta.get("employer") or meta.get("source") or "",
            "income_type": meta.get("income_type") or meta.get("type") or "Sonstige Einnahme",
            "target_month": meta.get("target_month") or meta.get("month") or ("monatlich" if item.get("cadence") == "monthly" else ""),
            "is_template": meta.get("template") == "true",
        })
        result.append(item)
    return result


def list_income_seed_candidates(conn: Connection, *, year: str = "2026") -> list[dict]:
    rows = conn.execute(
        """
        SELECT * FROM budget_seed_candidates
        WHERE status IN ('pending','needs_review','edited','accepted')
          AND (candidate_type IN ('income_plan','budget_plan') OR lower(COALESCE(proposed_name,'') || ' ' || COALESCE(proposed_parent_label,'') || ' ' || COALESCE(notes,'')) LIKE '%lohn%' OR lower(COALESCE(proposed_name,'') || ' ' || COALESCE(proposed_parent_label,'') || ' ' || COALESCE(notes,'')) LIKE '%gratifikation%' OR lower(COALESCE(proposed_name,'') || ' ' || COALESCE(proposed_parent_label,'') || ' ' || COALESCE(notes,'')) LIKE '%einnah%')
          AND (COALESCE(proposed_period_type,'')='' OR COALESCE(proposed_period_type,'') LIKE ? OR COALESCE(source_sheet,'') LIKE ?)
        ORDER BY requires_review DESC, source_sheet, source_row_or_range
        """,
        (f"%{year}%", f"%{year}%"),
    ).fetchall()
    return [row_to_dict(r) | {"requires_review": bool(r["requires_review"])} for r in rows]


def preview_income_plan_templates(conn: Connection, *, year: str = "2026") -> dict:
    existing = {str(r["name"]).lower() for r in conn.execute("""
        SELECT p.name
        FROM budget_plan_items p
        JOIN budget_categories c ON c.category_id=p.category_id
        WHERE substr(p.plan_month,1,4)=? AND p.is_active=1 AND c.category_type='income'
        """, (year,)).fetchall()}
    templates = [
        {
            "name": spec["name"],
            "person": spec["person"],
            "employer": spec.get("employer") or "",
            "income_type": spec["income_type"],
            "cadence": spec["cadence"],
            "target_month": spec["target_month"],
            "already_exists": spec["name"].lower() in existing or any(str(legacy).lower() in existing for legacy in spec.get("legacy_names", [])),
        }
        for spec in INCOME_TEMPLATE_SPECS
    ]
    return {"preview_id": new_id("preview"), "year": year, "template_count": len(templates), "create_count": sum(1 for t in templates if not t["already_exists"]), "templates": templates, "requires_explicit_confirm": True, "warnings": ["Templates enthalten bewusst keine Planbeträge."]}


def confirm_income_plan_templates(conn: Connection, *, year: str = "2026") -> dict:
    preview = preview_income_plan_templates(conn, year=year)
    ts = now()
    created = skipped = 0
    renamed = 0
    created_ids: list[str] = []
    for idx, spec in enumerate(INCOME_TEMPLATE_SPECS, start=1):
        if conn.execute("""
            SELECT 1
            FROM budget_plan_items p
            JOIN budget_categories c ON c.category_id=p.category_id
            WHERE substr(p.plan_month,1,4)=? AND lower(p.name)=lower(?) AND p.is_active=1 AND c.category_type='income'
            """, (year, spec["name"])).fetchone():
            skipped += 1
            continue
        legacy = _find_active_income_plan_by_names(conn, year, [str(n) for n in spec.get("legacy_names", [])]) if spec.get("legacy_names") else None
        if legacy:
            meta = _parse_notes(legacy.get("notes")) | {"person": str(spec["person"]), "income_type": str(spec["income_type"]), "target_month": str(spec["target_month"]), "template": "true"}
            if spec.get("employer"):
                meta["employer"] = str(spec["employer"])
            conn.execute("UPDATE budget_plan_items SET name=?, notes=?, updated_at=? WHERE plan_item_id=?", (spec["name"], _notes_from_meta(meta), ts, legacy["plan_item_id"]))
            record_audit_event(conn, source="vue_dashboard", action="income_template_renamed", entity_type="budget_plan_item", entity_id=str(legacy["plan_item_id"]), old_values={"name": legacy["name"]}, new_values={"name": spec["name"], "employer": spec.get("employer")}, created_by="user")
            renamed += 1
            skipped += 1
            continue
        category_id = _ensure_income_category(conn, str(spec["category"]), sort_order=100 + idx)
        plan_id = new_id("bplan")
        meta = {"person": str(spec["person"]), "income_type": str(spec["income_type"]), "target_month": str(spec["target_month"]), "template": "true"}
        if spec.get("employer"):
            meta["employer"] = str(spec["employer"])
        notes = _notes_from_meta(meta)
        conn.execute(
            """
            INSERT INTO budget_plan_items(plan_item_id, plan_month, category_id, name, monthly_amount_chf, annual_amount_chf, cadence, is_fixed_cost, source_type, notes, sort_order, is_active, created_at, updated_at)
            VALUES (?, ?, ?, ?, NULL, NULL, ?, 0, 'system', ?, ?, 1, ?, ?)
            """,
            (plan_id, f"{year}-01", category_id, spec["name"], spec["cadence"], notes, idx * 10, ts, ts),
        )
        record_audit_event(conn, source="vue_dashboard", action="income_template_created", entity_type="budget_plan_item", entity_id=plan_id, new_values={"name": spec["name"], "year": year, "has_amount": False}, created_by="user")
        created_ids.append(plan_id)
        created += 1
    audit_id = record_audit_event(conn, source="vue_dashboard", action="income_templates_seeded", entity_type="budget_plan_item", entity_id=f"income-templates:{year}", new_values={"created_count": created, "renamed_count": renamed, "skipped_count": skipped, "template_count": preview["template_count"]}, created_by="user")
    conn.commit()
    return {"status": "confirmed", "created_count": created, "renamed_count": renamed, "skipped_count": skipped, "entity_ids": created_ids, "audit_id": audit_id, "message": "Income templates seeded without amounts"}


def preview_update_income_plan_item(conn: Connection, plan_item_id: str, payload: dict) -> dict:
    row = conn.execute("SELECT * FROM budget_plan_items WHERE plan_item_id=?", (plan_item_id,)).fetchone()
    if not row:
        raise HTTPException(status_code=404, detail="budget plan item not found")
    merged = row_to_dict(row) | {k: v for k, v in payload.items() if k in {"plan_month", "category_id", "name", "monthly_amount_chf", "annual_amount_chf", "cadence", "source_type", "notes", "sort_order"}}
    if payload.get("person") or payload.get("income_type") or payload.get("target_month"):
        meta = _parse_notes(row["notes"])
        for key in ["person", "income_type", "target_month"]:
            if payload.get(key):
                meta[key] = str(payload[key])
        merged["notes"] = _notes_from_meta(meta, payload.get("extra_note"))
    normalised = _normalise_plan_payload(conn, merged)
    monthly, annual, monthly_average = _income_projection(normalised)
    meta = _parse_notes(normalised.get("notes"))
    return {
        "preview_id": new_id("preview"),
        "summary": f"Einkunft aktualisieren: {normalised['name']}",
        "warnings": [],
        "payload": normalised | {"plan_item_id": plan_item_id},
        "requires_explicit_confirm": True,
        "review": {
            "name": normalised["name"],
            "income_type": meta.get("income_type") or "Einkunft",
            "monthly_value_chf": monthly,
            "annual_value_chf": annual,
            "monthly_average_chf": monthly_average,
            "target_month": meta.get("target_month") or ("monatlich" if normalised["cadence"] == "monthly" else ""),
            "cadence": normalised["cadence"],
        },
    }


def confirm_update_income_plan_item(conn: Connection, plan_item_id: str, payload: dict) -> dict:
    row = conn.execute("SELECT * FROM budget_plan_items WHERE plan_item_id=?", (plan_item_id,)).fetchone()
    if not row:
        raise HTTPException(status_code=404, detail="budget plan item not found")
    preview = preview_update_income_plan_item(conn, plan_item_id, payload)
    normalised = preview["payload"]
    ts = now()
    conn.execute(
        """
        UPDATE budget_plan_items
        SET plan_month=?, category_id=?, name=?, monthly_amount_chf=?, annual_amount_chf=?, cadence=?, source_type=?, notes=?, sort_order=?, updated_at=?
        WHERE plan_item_id=?
        """,
        (normalised["plan_month"], normalised["category_id"], normalised["name"], normalised.get("monthly_amount_chf"), normalised.get("annual_amount_chf"), normalised["cadence"], normalised.get("source_type") or "manual", normalised.get("notes"), normalised.get("sort_order") or 999, ts, plan_item_id),
    )
    audit_id = record_audit_event(conn, source="vue_dashboard", action="income_plan_updated", entity_type="budget_plan_item", entity_id=plan_item_id, old_values=row_to_dict(row), new_values=normalised, created_by="user")
    conn.commit()
    return {"status": "updated", "entity_id": plan_item_id, "audit_id": audit_id, "message": "Einkunft aktualisiert"}


def archive_budget_plan_item(conn: Connection, plan_item_id: str) -> dict:
    row = conn.execute("SELECT * FROM budget_plan_items WHERE plan_item_id=?", (plan_item_id,)).fetchone()
    if not row:
        raise HTTPException(status_code=404, detail="budget plan item not found")
    ts = now()
    conn.execute("UPDATE budget_plan_items SET is_active=0, updated_at=? WHERE plan_item_id=?", (ts, plan_item_id))
    audit_id = record_audit_event(conn, source="vue_dashboard", action="archive", entity_type="budget_plan_item", entity_id=plan_item_id, old_values=row_to_dict(row), new_values={"is_active": 0}, created_by="user")
    conn.commit()
    return {"status": "archived", "entity_id": plan_item_id, "audit_id": audit_id, "message": "Budgetplan archiviert"}


def reorder_income_plan_items(conn: Connection, plan_item_ids: list[str], *, year: str = "2026") -> dict:
    ts = now()
    updated = 0
    old_order: list[dict] = []
    new_order: list[dict] = []
    for idx, plan_id in enumerate(plan_item_ids, start=1):
        row = conn.execute(
            """
            SELECT p.plan_item_id, p.sort_order
            FROM budget_plan_items p
            JOIN budget_categories c ON c.category_id=p.category_id
            WHERE p.plan_item_id=? AND p.is_active=1 AND c.category_type='income' AND substr(p.plan_month,1,4)=?
            """,
            (plan_id, year),
        ).fetchone()
        if not row:
            continue
        old_order.append({"plan_item_id": plan_id, "sort_order": row["sort_order"]})
        new_sort = idx * 10
        new_order.append({"plan_item_id": plan_id, "sort_order": new_sort})
        if int(row["sort_order"] or 0) != new_sort:
            conn.execute("UPDATE budget_plan_items SET sort_order=?, updated_at=? WHERE plan_item_id=?", (new_sort, ts, plan_id))
            updated += 1
    audit_id = record_audit_event(conn, source="vue_dashboard", action="reorder_income_plans", entity_type="budget_plan_item", entity_id=f"income-plans:{year}", old_values={"order": old_order}, new_values={"order": new_order, "updated_count": updated, "year": year}, created_by="user")
    conn.commit()
    return {"status": "reordered", "updated_count": updated, "audit_id": audit_id}