"""Context-builder query functions for the Overview tab (Chapter 08).

Every function here reads request_stats_hourly / request_stats_daily /
referrer_stats_daily / browser_stats_daily — never log_entries — per
Ch03 rule 6 / Ch08's own data-source rule.
"""
from __future__ import annotations

from datetime import date

from sqlalchemy import case, func

from app.extensions import db
from app.models.browser_stats import BrowserStatsDaily
from app.models.referrer_stats import ReferrerStatsDaily
from app.models.request_stats import RequestStatsDaily, RequestStatsHourly
from app.utils.dates import day_bounds

# ASSUMPTION (flagged in Ch08): Ch08 doesn't give a numeric threshold for
# "hourly vs daily depending on range width" — picked 3 days.
HOURLY_GRANULARITY_THRESHOLD_DAYS = 3


def get_kpis(from_date: date, to_date: date) -> dict:
    """All six Ch08 KPI-card fields, plus peak-day (folded in — Ch11 has
    no dedicated route for it and Ch08 calls for only a 'simple max-lookup').
    """
    row = (
        db.session.query(
            func.coalesce(func.sum(RequestStatsDaily.count), 0).label("total_requests"),
            func.coalesce(func.sum(RequestStatsDaily.unique_ips), 0).label("unique_ips_sum"),
            func.coalesce(func.sum(RequestStatsDaily.bytes_sum), 0).label("total_bandwidth"),
            func.coalesce(func.sum(RequestStatsDaily.error_count), 0).label("total_errors"),
        )
        .filter(RequestStatsDaily.date >= from_date, RequestStatsDaily.date <= to_date)
        .one()
    )
    num_days = (to_date - from_date).days + 1
    avg_response_size = (row.total_bandwidth / row.total_requests) if row.total_requests else 0.0
    error_rate_pct = (row.total_errors / row.total_requests * 100) if row.total_requests else 0.0
    avg_requests_per_day = row.total_requests / num_days if num_days else 0.0

    peak_row = (
        db.session.query(RequestStatsDaily.date, RequestStatsDaily.count)
        .filter(RequestStatsDaily.date >= from_date, RequestStatsDaily.date <= to_date)
        .order_by(RequestStatsDaily.count.desc())
        .first()
    )

    return {
        "total_requests": row.total_requests,
        # APPROXIMATION (flagged in Ch08): sum of daily unique_ips over-counts
        # repeat visitors across days.
        "unique_ips": row.unique_ips_sum,
        "total_bandwidth_bytes": row.total_bandwidth,
        "avg_response_size_bytes": round(avg_response_size, 1),
        "error_rate_pct": round(error_rate_pct, 2),
        "avg_requests_per_day": round(avg_requests_per_day, 1),
        "peak_day": {"date": peak_row.date.isoformat(), "count": peak_row.count} if peak_row else None,
    }


def get_traffic_chart_series(from_date: date, to_date: date) -> dict:
    span_days = (to_date - from_date).days + 1
    if span_days <= HOURLY_GRANULARITY_THRESHOLD_DAYS:
        start, end = day_bounds(from_date, to_date)
        rows = (
            db.session.query(RequestStatsHourly.date_hour, func.sum(RequestStatsHourly.count).label("count"))
            .filter(RequestStatsHourly.date_hour >= start, RequestStatsHourly.date_hour < end)
            .group_by(RequestStatsHourly.date_hour)
            .order_by(RequestStatsHourly.date_hour)
            .all()
        )
        return {"granularity": "hourly", "series": [{"t": r.date_hour.isoformat(), "count": r.count} for r in rows]}

    rows = (
        db.session.query(RequestStatsDaily.date, RequestStatsDaily.count)
        .filter(RequestStatsDaily.date >= from_date, RequestStatsDaily.date <= to_date)
        .order_by(RequestStatsDaily.date)
        .all()
    )
    return {"granularity": "daily", "series": [{"t": r.date.isoformat(), "count": r.count} for r in rows]}


def get_status_code_breakdown(from_date: date, to_date: date) -> dict:
    start, end = day_bounds(from_date, to_date)
    bucket = case(
        (RequestStatsHourly.status_code < 300, "2xx"),
        (RequestStatsHourly.status_code < 400, "3xx"),
        (RequestStatsHourly.status_code < 500, "4xx"),
        else_="5xx",
    )
    rows = (
        db.session.query(bucket.label("bucket"), func.sum(RequestStatsHourly.count).label("count"))
        .filter(RequestStatsHourly.date_hour >= start, RequestStatsHourly.date_hour < end)
        .group_by("bucket")
        .all()
    )
    breakdown = {"2xx": 0, "3xx": 0, "4xx": 0, "5xx": 0}
    for r in rows:
        breakdown[r.bucket] = r.count
    return breakdown


def get_top_urls(from_date: date, to_date: date, page: int, per_page: int) -> tuple[list[list], int]:
    """Top URLs by hits. NOTE (flagged in Ch08): only available within the
    ~90-day hourly retention window (Ch06) — request_stats_daily has no
    path column, so a wider range returns nothing here.
    """
    start, end = day_bounds(from_date, to_date)
    hits = func.sum(RequestStatsHourly.count)
    errors = func.sum(case((RequestStatsHourly.status_code >= 400, RequestStatsHourly.count), else_=0))
    bytes_sum = func.sum(RequestStatsHourly.bytes_sent_sum)

    base_query = (
        db.session.query(RequestStatsHourly.path, hits.label("hits"), bytes_sum.label("bytes_sum"), errors.label("errors"))
        .filter(RequestStatsHourly.date_hour >= start, RequestStatsHourly.date_hour < end)
        .group_by(RequestStatsHourly.path)
    )
    total = base_query.count()
    rows = base_query.order_by(hits.desc()).offset((page - 1) * per_page).limit(per_page).all()

    results = [
        [
            r.path,
            r.hits,
            round(r.bytes_sum / r.hits, 1) if r.hits else 0.0,
            round(r.errors / r.hits * 100, 2) if r.hits else 0.0,
        ]
        for r in rows
    ]
    return results, total


def get_top_referrers(from_date: date, to_date: date, page: int, per_page: int) -> tuple[list[list], int]:
    """Domain-bucketed referrers (Method A, Ch08 follow-up)."""
    hits = func.sum(ReferrerStatsDaily.count)
    base_query = (
        db.session.query(ReferrerStatsDaily.referrer_domain, hits.label("hits"))
        .filter(ReferrerStatsDaily.date >= from_date, ReferrerStatsDaily.date <= to_date)
        .group_by(ReferrerStatsDaily.referrer_domain)
    )
    total = base_query.count()
    rows = base_query.order_by(hits.desc()).offset((page - 1) * per_page).limit(per_page).all()
    return [[r.referrer_domain, r.hits] for r in rows], total


def get_browser_breakdown(from_date: date, to_date: date) -> dict:
    """Human-only (bots excluded at rollup-write time, Ch08)."""
    browser_rows = (
        db.session.query(BrowserStatsDaily.browser, func.sum(BrowserStatsDaily.count).label("count"))
        .filter(BrowserStatsDaily.date >= from_date, BrowserStatsDaily.date <= to_date)
        .group_by(BrowserStatsDaily.browser)
        .order_by(func.sum(BrowserStatsDaily.count).desc())
        .all()
    )
    os_rows = (
        db.session.query(BrowserStatsDaily.os, func.sum(BrowserStatsDaily.count).label("count"))
        .filter(BrowserStatsDaily.date >= from_date, BrowserStatsDaily.date <= to_date)
        .group_by(BrowserStatsDaily.os)
        .order_by(func.sum(BrowserStatsDaily.count).desc())
        .all()
    )
    return {
        "by_browser": [{"name": r.browser, "count": r.count} for r in browser_rows],
        "by_os": [{"name": r.os, "count": r.count} for r in os_rows],
    }