from __future__ import annotations

import sqlite3
from datetime import datetime, timedelta
from pathlib import Path
from zoneinfo import ZoneInfo

from sqlmodel import Session, select

from tracker.active_program import resolve_active_program
from tracker.config import TrackerConfig
from tracker.models import BodyWeightLog, Exercise, ProgramExercise, WorkoutLog
from tracker.planner_schemas import (
    CalendarEventBrief,
    CalendarViewResponse,
    DailyWeighinResponse,
    DeleteEventResponse,
    FreeBlock,
    PantryItem,
    PantryResponse,
    RecipeBrief,
    RecipeSearchResponse,
    ScheduleEventInput,
    ScheduledEvent,
    ScheduleWorkoutsInput,
    ShoppingListItem,
    TrackerStateResponse,
)
from tracker.progression import suggest_next
from tracker.training_service import latest_body_weight_kg
from tracker.workout_completion import find_last_completed_workout
from tracker.workout_day_text import build_program_day_summary_text
from tracker.scheduler import (
    create_daily_weighin_reminder,
    create_event,
    find_free_blocks,
    get_calendar_service,
    list_events,
    schedule_workouts,
    schedule_workouts_at_fixed_times,
)

CHICAGO_TZ = ZoneInfo("America/Chicago")


def read_only_connect(db_path: Path) -> sqlite3.Connection:
    return sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)


def fetch_calendar_view(days_ahead: int) -> CalendarViewResponse:
    service = get_calendar_service()
    now = datetime.now(tz=CHICAGO_TZ)
    time_max = now + timedelta(days=days_ahead)

    calendars = service.calendarList().list().execute().get("items", [])
    all_events: list[CalendarEventBrief] = []
    for cal in calendars:
        cal_id = cal["id"]
        cal_name = cal.get("summary", cal_id)
        events = list_events(service, cal_id, now, time_max)
        for ev in events:
            start_raw = ev.get("start", {})
            start_str = start_raw.get("dateTime", start_raw.get("date", ""))
            end_raw = ev.get("end", {})
            end_str = end_raw.get("dateTime", end_raw.get("date", ""))
            all_events.append(CalendarEventBrief(
                event_id=ev.get("id", ""),
                summary=ev.get("summary", "(no title)"),
                start=start_str,
                end=end_str,
                calendar=cal_name,
            ))

    all_events.sort(key=lambda e: e.start)

    free = find_free_blocks(service, now, time_max, 30)
    free_blocks = [
        FreeBlock(
            start=s.isoformat(),
            end=e.isoformat(),
            duration_minutes=int((e - s).total_seconds() / 60),
        )
        for s, e in free
    ]

    return CalendarViewResponse(events=all_events, free_blocks=free_blocks)


def run_schedule_workouts(
    session: Session,
    cfg: TrackerConfig,
    body: ScheduleWorkoutsInput,
) -> tuple[list[ScheduledEvent] | None, str | None]:
    service = get_calendar_service()

    program = resolve_active_program(session, cfg)
    if program is None:
        return None, "No program found"

    pe_list = session.exec(
        select(ProgramExercise)
        .where(ProgramExercise.program_id == program.program_id),
    ).all()
    day_labels = sorted({pe.day_label for pe in pe_list})

    exercise_descriptions: dict[str, str] = {}
    for day in day_labels:
        day_exercises = sorted(
            [pe for pe in pe_list if pe.day_label == day],
            key=lambda pe: pe.order_index,
        )
        exercise_descriptions[day] = build_program_day_summary_text(
            day_exercises,
            session,
            tracker_base_url=cfg.tracker_app_url,
        )

    target_calendar = body.calendar_id or cfg.workout_calendar_id or cfg.default_calendar_id

    if body.workout_starts_iso is not None:
        event_ids_result, sched_err = schedule_workouts_at_fixed_times(
            service,
            target_calendar,
            program.name,
            day_labels,
            body.workout_starts_iso,
            body.duration_minutes,
            exercise_descriptions,
            body.allow_overlap_with_existing,
        )
        if sched_err is not None:
            return None, sched_err
        event_ids = event_ids_result or []
    else:
        event_ids = schedule_workouts(
            service,
            target_calendar,
            program.name,
            day_labels,
            body.days_ahead,
            body.duration_minutes,
            exercise_descriptions,
        )

    created: list[ScheduledEvent] = []
    for eid in event_ids:
        ev = service.events().get(
            calendarId=target_calendar, eventId=eid,
        ).execute()
        start_raw = ev.get("start", {})
        end_raw = ev.get("end", {})
        created.append(ScheduledEvent(
            event_id=eid,
            summary=ev.get("summary", ""),
            start=start_raw.get("dateTime", start_raw.get("date", "")),
            end=end_raw.get("dateTime", end_raw.get("date", "")),
        ))

    return created, None


def run_schedule_event(cfg: TrackerConfig, body: ScheduleEventInput) -> ScheduledEvent:
    service = get_calendar_service()

    start_dt = datetime.fromisoformat(body.start)
    end_dt = datetime.fromisoformat(body.end)

    target_calendar = body.calendar_id or cfg.default_calendar_id

    event_id = create_event(
        service, target_calendar,
        body.summary, start_dt, end_dt, body.description,
    )

    return ScheduledEvent(
        event_id=event_id,
        summary=body.summary,
        start=body.start,
        end=body.end,
    )


def delete_calendar_event(cfg: TrackerConfig, event_id: str) -> DeleteEventResponse:
    service = get_calendar_service()
    service.events().delete(
        calendarId=cfg.default_calendar_id, eventId=event_id,
    ).execute()
    return DeleteEventResponse(deleted=event_id)


def create_daily_weighin(cfg: TrackerConfig) -> DailyWeighinResponse:
    service = get_calendar_service()
    event_id = create_daily_weighin_reminder(service, cfg.default_calendar_id)
    return DailyWeighinResponse(event_id=event_id)


def fetch_pantry(cfg: TrackerConfig, user_id: int) -> PantryResponse:
    conn = read_only_connect(cfg.user_db_path)
    rows = conn.execute(
        "SELECT display_text, quantity, unit FROM user_pantry_items WHERE user_id = ?",
        (user_id,),
    ).fetchall()
    conn.close()

    items = [PantryItem(display_text=r[0], quantity=r[1], unit=r[2]) for r in rows]
    return PantryResponse(user_id=user_id, item_count=len(items), items=items)


def compute_recipe_nutrition(
    recipe_ids: list[int],
    query_conn: sqlite3.Connection,
    cache_conn: sqlite3.Connection,
) -> dict[int, tuple[int | None, float | None]]:
    if not recipe_ids:
        return {}

    placeholders = ",".join("?" for _ in recipe_ids)
    ingredient_rows = query_conn.execute(
        f"SELECT recipe_id, fdc_id, grams_per_serving FROM recipe_ingredients WHERE recipe_id IN ({placeholders}) AND fdc_id > 0",
        recipe_ids,
    ).fetchall()

    fdc_ids = list({row[1] for row in ingredient_rows})
    if not fdc_ids:
        return {}

    fdc_placeholders = ",".join("?" for _ in fdc_ids)
    nutrient_rows = cache_conn.execute(
        f"SELECT i.fdc_id, n.nutrient_name, n.amount_per_100g "
        f"FROM ingredients i "
        f"JOIN ingredient_nutrients n ON i.ingredient_id = n.ingredient_id "
        f"WHERE i.fdc_id IN ({fdc_placeholders}) "
        f"AND ((n.nutrient_name = 'Energy' AND n.unit_name = 'kcal') OR (n.nutrient_name = 'Protein' AND n.unit_name = 'g'))",
        fdc_ids,
    ).fetchall()

    nutrient_map: dict[int, dict[str, float]] = {}
    for fdc_id, name, amount in nutrient_rows:
        nutrient_map.setdefault(fdc_id, {})[name] = amount

    recipe_nutrition: dict[int, tuple[float, float]] = {}
    for recipe_id, fdc_id, grams in ingredient_rows:
        if grams is None or grams <= 0:
            continue
        nutrients = nutrient_map.get(fdc_id)
        if nutrients is None:
            continue
        cals, prot = recipe_nutrition.get(recipe_id, (0.0, 0.0))
        cals += grams * nutrients.get("Energy", 0.0) / 100.0
        prot += grams * nutrients.get("Protein", 0.0) / 100.0
        recipe_nutrition[recipe_id] = (cals, prot)

    return {
        rid: (round(cals) if cals > 0 else None, round(prot, 1) if prot > 0 else None)
        for rid, (cals, prot) in recipe_nutrition.items()
    }


def search_recipes(
    cfg: TrackerConfig,
    q: str,
    max_active_minutes: int | None,
    max_total_minutes: int | None,
    min_calories: int | None,
    max_calories: int | None,
    min_protein: float | None,
    limit: int,
) -> RecipeSearchResponse:
    query_conn = read_only_connect(cfg.query_db_path)
    cache_conn = read_only_connect(cfg.cache_db_path)

    query_lower = q.lower()
    sql = "SELECT recipe_id, title, active_minutes, total_minutes, satiety FROM recipe_search_doc WHERE title_lower LIKE ? OR full_text_lower LIKE ?"
    params: list[str | int] = [f"%{query_lower}%", f"%{query_lower}%"]

    if max_active_minutes is not None:
        sql += " AND active_minutes IS NOT NULL AND active_minutes <= ?"
        params.append(max_active_minutes)
    if max_total_minutes is not None:
        sql += " AND total_minutes IS NOT NULL AND total_minutes <= ?"
        params.append(max_total_minutes)

    fetch_limit = limit * 5 if (min_calories or max_calories or min_protein) else limit
    sql += " ORDER BY satiety DESC NULLS LAST LIMIT ?"
    params.append(fetch_limit)

    rows = query_conn.execute(sql, params).fetchall()

    recipe_ids = [r[0] for r in rows]
    nutrition = compute_recipe_nutrition(recipe_ids, query_conn, cache_conn)

    query_conn.close()
    cache_conn.close()

    results: list[RecipeBrief] = []
    for r in rows:
        rid = r[0]
        cals, prot = nutrition.get(rid, (None, None))

        if min_calories is not None and (cals is None or cals < min_calories):
            continue
        if max_calories is not None and (cals is None or cals > max_calories):
            continue
        if min_protein is not None and (prot is None or prot < min_protein):
            continue

        results.append(RecipeBrief(
            recipe_id=rid, title=r[1], active_minutes=r[2],
            total_minutes=r[3], satiety=r[4],
            calories_per_serving=cals, protein_per_serving=prot,
        ))
        if len(results) >= limit:
            break

    return RecipeSearchResponse(query=q, results=results, total=len(results))


def build_tracker_state(session: Session, cfg: TrackerConfig) -> TrackerStateResponse:
    cutoff = datetime.now() - timedelta(days=30)
    weights = session.exec(
        select(BodyWeightLog)
        .where(BodyWeightLog.logged_at >= cutoff)
        .order_by(BodyWeightLog.logged_at),
    ).all()

    current_weight: float | None = None
    weight_trend: list[dict[str, float | str]] = []
    if weights:
        current_weight = weights[-1].weight_kg
        weight_trend = [
            {"date": w.logged_at.strftime("%Y-%m-%d"), "kg": w.weight_kg}
            for w in weights
        ]

    last_day: str | None = None
    last_date: str | None = None
    program_name: str | None = None

    program = resolve_active_program(session, cfg)
    if program is not None:
        last_day, last_date = find_last_completed_workout(session, program.program_id)
        program_name = program.name
    progression_summary: list[dict[str, str | float | None]] = []
    if program is not None:
        pe_list = session.exec(
            select(ProgramExercise)
            .where(ProgramExercise.program_id == program.program_id)
            .order_by(ProgramExercise.day_label, ProgramExercise.order_index),
        ).all()

        bw_kg = latest_body_weight_kg(session)
        for pe in pe_list:
            exercise = session.get(Exercise, pe.exercise_id)
            if exercise is None:
                continue
            logs = session.exec(
                select(WorkoutLog)
                .where(WorkoutLog.exercise_id == pe.exercise_id)
                .where(WorkoutLog.program_id == program.program_id)
                .where(WorkoutLog.day_label == pe.day_label)
                .order_by(WorkoutLog.logged_at.desc())
                .limit(pe.target_sets),
            ).all()
            suggestion = suggest_next(
                pe,
                logs,
                exercise,
                bw_kg,
                calibration_bench_lb_per_hand=cfg.calibration_dumbbell_bench_lb_per_hand,
                calibration_squat_lb_per_hand=cfg.calibration_dumbbell_squat_lb_per_hand,
                dumbbell_weight_quantum_lb=cfg.dumbbell_weight_quantum_lb,
            )
            progression_summary.append({
                "day": pe.day_label,
                "exercise": exercise.name,
                "suggested_weight": suggestion.weight_kg,
                "suggested_reps": suggestion.target_reps,
                "note": suggestion.note,
                "load_reference": suggestion.load_reference,
            })

    return TrackerStateResponse(
        current_weight_kg=current_weight,
        weight_trend=weight_trend,
        last_workout_day=last_day,
        last_workout_date=last_date,
        program_name=program_name,
        progression_summary=progression_summary,
    )


def build_shopping_gap(cfg: TrackerConfig, ingredients: str, user_id: int) -> list[ShoppingListItem]:
    conn = read_only_connect(cfg.user_db_path)
    rows = conn.execute(
        "SELECT LOWER(display_text) FROM user_pantry_items WHERE user_id = ?",
        (user_id,),
    ).fetchall()
    conn.close()

    pantry_lower = {r[0] for r in rows}
    ingredient_list = [i.strip() for i in ingredients.split(",") if i.strip()]

    result: list[ShoppingListItem] = []
    for ing in ingredient_list:
        in_pantry = any(ing.lower() in p or p in ing.lower() for p in pantry_lower)
        result.append(ShoppingListItem(ingredient=ing, in_pantry=in_pantry))

    return result