"""Bounded read-only supplement projections for Dashboard V5."""
from __future__ import annotations

import sqlite3
from collections import defaultdict
from datetime import date
from typing import Any

from dashboard_v5.nutrition_contract import NUTRIENT_CONTRACTS

MAX_SUPPLEMENT_ROWS = 5000


def _exists(connection: sqlite3.Connection, table: str) -> bool:
    return connection.execute("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?", (table,)).fetchone() is not None


def supplement_nutrient_totals(connection: sqlite3.Connection, start: date, end: date) -> dict[str, dict[str, dict[str, Any]]]:
    if not _exists(connection, "supplement_intakes"):
        return {}
    rows = list(connection.execute(
        """SELECT substr(occurred_at,1,10) day,nutrient_key,amount,unit,assignment_reliability
           FROM supplement_intakes
           WHERE status='administered' AND substr(occurred_at,1,10)>=? AND substr(occurred_at,1,10)<=?
           ORDER BY occurred_at,id LIMIT ?""",
        (start.isoformat(), end.isoformat(), MAX_SUPPLEMENT_ROWS + 1),
    ))
    if len(rows) > MAX_SUPPLEMENT_ROWS:
        raise ValueError("supplement_row_limit")
    grouped: dict[str, dict[str, dict[str, Any]]] = defaultdict(dict)
    values: dict[tuple[str, str], list[tuple[float, str]]] = defaultdict(list)
    for day, key, amount, unit, reliability in rows:
        contract = NUTRIENT_CONTRACTS.get(str(key or ""))
        try:
            number = float(amount)
        except (TypeError, ValueError):
            continue
        if contract is None or unit != contract.unit or number < 0:
            continue
        values[(str(day), contract.key)].append((number, str(reliability or "unknown")))
    for (day, key), entries in sorted(values.items()):
        contract = NUTRIENT_CONTRACTS[key]
        grouped[day][key] = {
            "value": round(sum(item[0] for item in entries), contract.precision),
            "unit": contract.unit,
            "intake_count": len(entries),
            "contains_estimate": any(item[1] == "estimated" for item in entries),
            "source": "supplement_intakes",
        }
    return dict(grouped)


def supplements(connection: sqlite3.Connection, start: date, end: date) -> dict[str, Any]:
    planned: list[dict[str, Any]] = []
    actual: dict[str, list[dict[str, Any]]] = {key: [] for key in ("administered", "missed", "corrected")}
    if _exists(connection, "supplement_plans"):
        rows = list(connection.execute(
            """SELECT product,brand_variant,nutrient_key,amount,unit,schedule_type,weekdays,
                      interval_days,start_date,end_date,composition_source,assignment_reliability,notes
               FROM supplement_plans
               WHERE start_date<=? AND COALESCE(NULLIF(end_date,''),?)>=?
               ORDER BY start_date,id LIMIT ?""",
            (end.isoformat(), end.isoformat(), start.isoformat(), MAX_SUPPLEMENT_ROWS + 1),
        ))
        if len(rows) > MAX_SUPPLEMENT_ROWS:
            raise ValueError("supplement_row_limit")
        for row in rows:
            contract = NUTRIENT_CONTRACTS.get(str(row[2] or ""))
            if contract is None or row[4] != contract.unit:
                continue
            planned.append({
                "product": row[0], "brand_variant": row[1] or "", "nutrient": contract.label,
                "nutrient_key": contract.key, "amount": row[3], "unit": row[4],
                "schedule_type": row[5], "weekdays": row[6] or "", "interval_days": row[7],
                "start_date": row[8], "end_date": row[9], "composition_source": row[10],
                "assignment_reliability": row[11], "note": row[12] or "",
            })
    if _exists(connection, "supplement_intakes"):
        rows = list(connection.execute(
            """SELECT product,brand_variant,nutrient_key,amount,unit,status,occurred_at,
                      composition_source,assignment_reliability,notes
               FROM supplement_intakes
               WHERE substr(occurred_at,1,10)>=? AND substr(occurred_at,1,10)<=?
               ORDER BY occurred_at,id LIMIT ?""",
            (start.isoformat(), end.isoformat(), MAX_SUPPLEMENT_ROWS + 1),
        ))
        if len(rows) > MAX_SUPPLEMENT_ROWS:
            raise ValueError("supplement_row_limit")
        for row in rows:
            contract = NUTRIENT_CONTRACTS.get(str(row[2] or ""))
            status = str(row[5] or "")
            if contract is None or row[4] != contract.unit or status not in actual:
                continue
            actual[status].append({
                "product": row[0], "brand_variant": row[1] or "", "nutrient": contract.label,
                "nutrient_key": contract.key, "amount": row[3], "unit": row[4], "status": status,
                "occurred_at": row[6], "date": str(row[6])[:10], "composition_source": row[7],
                "assignment_reliability": row[8], "note": row[9] or "",
            })
    return {
        "contract_version": "supplement_documentation_v1",
        "planned": planned,
        **actual,
        "statement": "Dokumentierte Planung und tatsächliche Einnahme bleiben getrennt. Keine Dosierungsempfehlung.",
        "truncated": False,
    }