from __future__ import annotations

from decimal import Decimal
from sqlite3 import Connection

from jarvis_finance.audit.log import record_audit_event
from jarvis_finance.quality.alerts import create_alert

from .rates import upsert_fx_rate


def _dec(value: object) -> Decimal:
    return Decimal(str(value))


def get_fx_rate_to_chf(conn: Connection | None, currency: str, rate_date: str) -> Decimal:
    currency = (currency or "").upper().strip()
    if currency == "CHF":
        return Decimal("1")
    if conn is None:
        raise ValueError(f"Missing FX rate for {currency}/CHF on {rate_date}")
    row = conn.execute(
        """
        SELECT rate FROM fx_rates
        WHERE base_currency = ? AND quote_currency = 'CHF' AND rate_date = ?
        ORDER BY created_at DESC LIMIT 1
        """,
        (currency, rate_date),
    ).fetchone()
    if row:
        return _dec(row["rate"])
    create_alert(
        conn,
        priority="kritisch",
        category="fx",
        rule_id="missing_fx",
        message=f"Missing FX rate for {currency}/CHF on {rate_date}",
        evidence={"currency": currency, "rate_date": rate_date},
    )
    conn.commit()
    raise ValueError(f"Missing FX rate for {currency}/CHF on {rate_date}")


def convert_to_chf(amount: Decimal, currency: str, rate_date: str, conn: Connection | None) -> Decimal:
    return amount * get_fx_rate_to_chf(conn, currency, rate_date)


def set_manual_fx_override(
    conn: Connection,
    base_currency: str,
    rate_date: str,
    rate: Decimal,
    *,
    note: str,
) -> str:
    if not note.strip():
        raise ValueError("manual FX override requires a note")
    fx_rate_id = upsert_fx_rate(
        conn,
        base_currency=base_currency.upper().strip(),
        quote_currency="CHF",
        rate_date=rate_date,
        rate=rate,
        provider="manual_override",
        rate_type="historical_override",
        quality_status="ok",
    )
    audit_id = record_audit_event(
        conn,
        source="manual_fx_override",
        action="manual_fx_override",
        entity_type="fx_rate",
        entity_id=fx_rate_id,
        new_values={"base_currency": base_currency.upper().strip(), "quote_currency": "CHF", "rate_date": rate_date, "rate": str(rate)},
        user_text_note=note,
        confirmed=True,
        created_by="system",
    )
    conn.commit()
    return audit_id