#!/usr/bin/env python3
"""
queries.py — Agrégations lues depuis la base SQLite pour l'API FastAPI.

Lecture seule : ce module n'a jamais besoin de config.json (qui contient les
secrets Azure) — il ne connaît que la base, alimentée par collector/collect.py.
"""

from __future__ import annotations

import os
import sqlite3
from datetime import date
from pathlib import Path

DEFAULT_DB_PATH = "cost_dashboard.db"
SCHEMA_PATH = Path(__file__).parent.parent / "db" / "schema.sql"


def _resolve_db_path(db_path: str | None = None) -> str:
    return db_path or os.environ.get("COST_DASHBOARD_DB") or DEFAULT_DB_PATH


def init_db(db_path: str | None = None) -> None:
    """Crée le fichier SQLite et applique le schéma s'il n'existe pas déjà.
    Idempotent — appelé au démarrage de l'API pour que le dashboard reste
    utilisable (vide) même avant la première collecte."""
    path = _resolve_db_path(db_path)
    Path(path).parent.mkdir(parents=True, exist_ok=True)

    conn = sqlite3.connect(path)
    try:
        conn.executescript(SCHEMA_PATH.read_text())
        conn.commit()
    finally:
        conn.close()


def get_connection(db_path: str | None = None) -> sqlite3.Connection:
    path = _resolve_db_path(db_path)
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    return conn


# ----------------------------------------------------------------------
# Helpers
# ----------------------------------------------------------------------

def _last_n_months(n: int, today: date | None = None) -> list[str]:
    """Les n derniers mois (format 'YYYY-MM'), du plus ancien au plus récent,
    mois courant inclus."""
    today = today or date.today()
    y, m = today.year, today.month
    months = []
    for _ in range(n):
        months.append(f"{y:04d}-{m:02d}")
        m -= 1
        if m == 0:
            m, y = 12, y - 1
    return list(reversed(months))


def _merged_monthly(
    conn: sqlite3.Connection,
    since_month: str,
    provider: str | None = None,
    tenant_name: str | None = None,
) -> dict[tuple[str, str, str], float]:
    """Total par (provider, tenant, mois) depuis `since_month` (inclus,
    format 'YYYY-MM'). Quand un mois a à la fois des relevés 'daily' et
    'monthly' (import historique), les 'daily' priment — ils sont plus
    précis et évitent le double comptage."""
    where = ["status = 'ok'", "period_start >= ?"]
    params: list[str] = [f"{since_month}-01"]
    if provider:
        where.append("provider = ?")
        params.append(provider)
    if tenant_name:
        where.append("tenant_name = ?")
        params.append(tenant_name)

    rows = conn.execute(
        f"""
        SELECT provider, tenant_name, substr(period_start, 1, 7) AS month,
               granularity, SUM(total_amount) AS amount
        FROM collections
        WHERE {' AND '.join(where)}
        GROUP BY provider, tenant_name, month, granularity
        """,
        params,
    ).fetchall()

    merged: dict[tuple[str, str, str], float] = {}
    have_daily: set[tuple[str, str, str]] = set()
    for r in rows:
        if r["granularity"] == "daily":
            key = (r["provider"], r["tenant_name"], r["month"])
            merged[key] = r["amount"]
            have_daily.add(key)
    for r in rows:
        if r["granularity"] == "monthly":
            key = (r["provider"], r["tenant_name"], r["month"])
            if key not in have_daily:
                merged[key] = r["amount"]
    return merged


def _latest_daily_status(conn: sqlite3.Connection) -> dict[tuple[str, str], dict]:
    """Dernier relevé journalier (le plus récent period_start) par tenant,
    quel que soit son statut — pour détecter les échecs de collecte."""
    rows = conn.execute(
        """
        SELECT provider, tenant_name, status, error_message, collected_at, period_start
        FROM collections
        WHERE granularity = 'daily'
        ORDER BY period_start ASC
        """
    ).fetchall()
    latest: dict[tuple[str, str], dict] = {}
    for r in rows:
        latest[(r["provider"], r["tenant_name"])] = dict(r)
    return latest


def list_tenants(conn: sqlite3.Connection) -> list[dict]:
    rows = conn.execute(
        "SELECT DISTINCT provider, tenant_name AS name FROM collections ORDER BY provider, tenant_name"
    ).fetchall()
    return [dict(r) for r in rows]


# ----------------------------------------------------------------------
# Vue globale
# ----------------------------------------------------------------------

def overview(conn: sqlite3.Connection, months: int = 12) -> dict:
    month_keys = _last_n_months(months)
    merged = _merged_monthly(conn, month_keys[0])
    latest_status = _latest_daily_status(conn)

    current_month_key = month_keys[-1]
    previous_month_key = month_keys[-2] if len(month_keys) >= 2 else None

    providers_totals = {"aws": [0.0] * months, "azure": [0.0] * months}
    tenants = []

    for t in list_tenants(conn):
        provider, name = t["provider"], t["name"]

        monthly = []
        for i, mk in enumerate(month_keys):
            amount = merged.get((provider, name, mk))
            monthly.append(round(amount, 2) if amount is not None else 0.0)
            if amount is not None:
                providers_totals[provider][i] += amount

        current = merged.get((provider, name, current_month_key), 0.0)
        previous = merged.get((provider, name, previous_month_key), 0.0) if previous_month_key else 0.0
        delta_pct = ((current - previous) / previous * 100) if previous else None

        status_info = latest_status.get((provider, name))

        tenants.append({
            "provider": provider,
            "name": name,
            "current_month": round(current, 2),
            "previous_month": round(previous, 2),
            "delta_pct": round(delta_pct, 1) if delta_pct is not None else None,
            "monthly": monthly,
            "status": status_info["status"] if status_info else None,
            "error_message": status_info["error_message"] if status_info else None,
            "last_collected_at": status_info["collected_at"] if status_info else None,
        })

    tenants.sort(key=lambda t: t["current_month"], reverse=True)
    tenants_error = [t for t in tenants if t["status"] == "error"]

    return {
        "months": month_keys,
        "providers_totals": {k: [round(v, 2) for v in vals] for k, vals in providers_totals.items()},
        "tenants": tenants,
        "kpis": {
            "total_current_month": round(sum(t["current_month"] for t in tenants), 2),
            "total_aws_current_month": round(providers_totals["aws"][-1], 2),
            "total_azure_current_month": round(providers_totals["azure"][-1], 2),
            "tenants_error": [{"provider": t["provider"], "name": t["name"]} for t in tenants_error],
        },
        "currency": "EUR",
    }


# ----------------------------------------------------------------------
# Détail tenant
# ----------------------------------------------------------------------

def tenant_detail(conn: sqlite3.Connection, provider: str, name: str, range_days: int = 30) -> dict:
    latest_status = _latest_daily_status(conn).get((provider, name))

    daily_rows = conn.execute(
        """
        SELECT period_start, total_amount, currency
        FROM collections
        WHERE provider = ? AND tenant_name = ? AND granularity = 'daily' AND status = 'ok'
        ORDER BY period_start ASC
        """,
        (provider, name),
    ).fetchall()

    has_monthly_data = False

    if range_days == 365:
        # Vue "1 an" : par mois, pas par jour — la collecte quotidienne ne
        # remonte pas assez loin pour ça, l'historique au-delà vient du
        # backfill/import (granularity='monthly'). Pas de comparaison à la
        # période précédente : l'historique dispo dépasse rarement 12 mois.
        month_keys = _last_n_months(12)
        merged = _merged_monthly(conn, month_keys[0], provider=provider, tenant_name=name)
        amounts = [merged.get((provider, name, mk), 0.0) for mk in month_keys]
        has_monthly_data = any(amounts)

        period = {
            "mode": "monthly",
            "range_days": None,
            "months": month_keys,
            "amounts": [round(a, 2) for a in amounts],
            "current_total": round(sum(amounts), 2),
            "previous_total": None,
            "delta_pct": None,
            "has_previous": False,
        }
    else:
        current_rows = daily_rows[-range_days:] if range_days else []
        previous_rows = (
            daily_rows[-2 * range_days:-range_days]
            if range_days and len(daily_rows) > range_days
            else []
        )

        current_total = sum(r["total_amount"] for r in current_rows)
        previous_total = sum(r["total_amount"] for r in previous_rows)
        delta_pct = ((current_total - previous_total) / previous_total * 100) if previous_total else None

        period = {
            "mode": "daily",
            "range_days": range_days,
            "days": [r["period_start"] for r in current_rows],
            "amounts": [round(r["total_amount"], 2) for r in current_rows],
            "current_total": round(current_total, 2),
            "previous_total": round(previous_total, 2),
            "delta_pct": round(delta_pct, 1) if delta_pct is not None else None,
            "has_previous": len(previous_rows) > 0,
        }

    currency = daily_rows[-1]["currency"] if daily_rows else None
    if currency is None:
        row = conn.execute(
            """
            SELECT currency FROM collections
            WHERE provider = ? AND tenant_name = ? AND status = 'ok'
            ORDER BY period_start DESC LIMIT 1
            """,
            (provider, name),
        ).fetchone()
        currency = row["currency"] if row else "EUR"

    services: dict[str, float] = {}
    service_window = daily_rows[-30:]
    if service_window:
        start_bound = service_window[0]["period_start"]
        rows = conn.execute(
            """
            SELECT sc.service_name AS service_name, SUM(sc.amount) AS amount
            FROM service_costs sc
            JOIN collections c ON c.id = sc.collection_id
            WHERE c.provider = ? AND c.tenant_name = ? AND c.granularity = 'daily'
              AND c.status = 'ok' AND c.period_start >= ?
            GROUP BY sc.service_name
            ORDER BY amount DESC
            """,
            (provider, name, start_bound),
        ).fetchall()
        services = {r["service_name"]: round(r["amount"], 2) for r in rows}

    since_year = f"{date.today().year}-01"
    ytd_merged = _merged_monthly(conn, since_year, provider=provider, tenant_name=name)
    ytd_total = round(sum(ytd_merged.values()), 2)

    return {
        "tenant": {"provider": provider, "name": name},
        "currency": currency,
        "status": latest_status,
        "period": period,
        "services": services,
        "ytd_total": ytd_total,
        "has_data": bool(daily_rows),
        "has_monthly_data": has_monthly_data,
    }


def services_for_days(conn: sqlite3.Connection, provider: str, name: str, days: list[str]) -> dict[str, float]:
    """Répartition par service sur un sous-ensemble arbitraire de jours
    (sélection de barres sur le graphe journalier, mode 7j/30j)."""
    if not days:
        return {}
    placeholders = ",".join("?" * len(days))
    rows = conn.execute(
        f"""
        SELECT sc.service_name AS service_name, SUM(sc.amount) AS amount
        FROM service_costs sc
        JOIN collections c ON c.id = sc.collection_id
        WHERE c.provider = ? AND c.tenant_name = ? AND c.granularity = 'daily' AND c.status = 'ok'
          AND c.period_start IN ({placeholders})
        GROUP BY sc.service_name
        """,
        (provider, name, *days),
    ).fetchall()
    return dict(sorted(((r["service_name"], round(r["amount"], 2)) for r in rows), key=lambda kv: -kv[1]))


def services_for_months(conn: sqlite3.Connection, provider: str, name: str, months: list[str]) -> dict[str, float]:
    """Répartition par service sur un sous-ensemble arbitraire de mois
    (sélection de barres sur le graphe "1 an"). Comme _merged_monthly,
    préfère la granularité 'daily' à 'monthly' pour les mois où les deux
    existent, pour ne pas rater un mois couvert par la collecte quotidienne
    mais jamais backfillé."""
    if not months:
        return {}
    placeholders = ",".join("?" * len(months))

    daily_rows = conn.execute(
        f"""
        SELECT substr(c.period_start, 1, 7) AS month, sc.service_name AS service_name, SUM(sc.amount) AS amount
        FROM service_costs sc
        JOIN collections c ON c.id = sc.collection_id
        WHERE c.provider = ? AND c.tenant_name = ? AND c.granularity = 'daily' AND c.status = 'ok'
          AND substr(c.period_start, 1, 7) IN ({placeholders})
        GROUP BY month, sc.service_name
        """,
        (provider, name, *months),
    ).fetchall()

    months_with_daily = {r["month"] for r in daily_rows}
    remaining_months = [m for m in months if m not in months_with_daily]

    monthly_rows = []
    if remaining_months:
        placeholders2 = ",".join("?" * len(remaining_months))
        monthly_rows = conn.execute(
            f"""
            SELECT substr(c.period_start, 1, 7) AS month, sc.service_name AS service_name, SUM(sc.amount) AS amount
            FROM service_costs sc
            JOIN collections c ON c.id = sc.collection_id
            WHERE c.provider = ? AND c.tenant_name = ? AND c.granularity = 'monthly' AND c.status = 'ok'
              AND substr(c.period_start, 1, 7) IN ({placeholders2})
            GROUP BY month, sc.service_name
            """,
            (provider, name, *remaining_months),
        ).fetchall()

    totals: dict[str, float] = {}
    for r in list(daily_rows) + list(monthly_rows):
        totals[r["service_name"]] = totals.get(r["service_name"], 0.0) + r["amount"]

    return dict(sorted(((k, round(v, 2)) for k, v in totals.items()), key=lambda kv: -kv[1]))


# ----------------------------------------------------------------------
# Détail par ressource (drawer sur un service)
# ----------------------------------------------------------------------

def resource_detail(conn: sqlite3.Connection, provider: str, name: str, service_name: str) -> dict:
    """Détail par ressource pour un service donné d'un tenant — alimenté par
    collect_tenant_resources (collector/collect.py), cliché glissant sur
    RESOURCE_WINDOW_DAYS jours. `status` distingue 'ok' (des ressources sont
    listées), 'unavailable' (ex: Resource IDs non activé côté AWS),
    'error' et 'never_collected' (pas encore de run de collecte depuis la
    mise à jour du schéma)."""
    status_row = conn.execute(
        "SELECT status, message, collected_at FROM resource_collection_status WHERE provider = ? AND tenant_name = ?",
        (provider, name),
    ).fetchone()

    if status_row is None:
        return {
            "status": "never_collected",
            "message": None,
            "collected_at": None,
            "period_start": None,
            "period_end": None,
            "resources": [],
        }

    resources = []
    period_start = period_end = None
    if status_row["status"] == "ok":
        bounds = conn.execute(
            "SELECT period_start, period_end FROM resource_costs WHERE provider = ? AND tenant_name = ? LIMIT 1",
            (provider, name),
        ).fetchone()
        if bounds:
            period_start, period_end = bounds["period_start"], bounds["period_end"]

        rows = conn.execute(
            """
            SELECT resource_id, resource_name, amount
            FROM resource_costs
            WHERE provider = ? AND tenant_name = ? AND service_name = ?
            ORDER BY amount DESC
            """,
            (provider, name, service_name),
        ).fetchall()
        resources = [
            {"resource_id": r["resource_id"], "resource_name": r["resource_name"], "amount": round(r["amount"], 2)}
            for r in rows
        ]

    return {
        "status": status_row["status"],
        "message": status_row["message"],
        "collected_at": status_row["collected_at"],
        "period_start": period_start,
        "period_end": period_end,
        "resources": resources,
    }