"""Generate a self-contained, offline HTML analytics dashboard.
The dashboard is a single HTML file (no external CDNs, no server runtime
besides the API) that embeds the warehouse data as JSON and renders it with a
small dependency-free JavaScript renderer. It reads directly from the DuckDB
warehouse file and can be regenerated any time the warehouse is rebuilt.
Usage:
python -m scripts.build_dashboard [--out path/to/dashboard.html]
The FastAPI app serves this file at ``GET /dashboard``.
"""
from __future__ import annotations
import argparse
import datetime as _dt
import json
import sys
from pathlib import Path
from typing import Any
import duckdb
from config.logging import logger
from config.settings import settings
def _rows(con: duckdb.DuckDBPyConnection, sql: str) -> list[dict[str, Any]]:
try:
return con.execute(sql).fetch_df().to_dict(orient="records")
except duckdb.Error:
return []
def collect_data() -> dict[str, Any]:
"""Query the warehouse for every dashboard panel."""
if not settings.duckdb_path.exists():
return {"error": "Warehouse not built. Run `make run` (pipeline) first."}
with duckdb.connect(str(settings.duckdb_path), read_only=True) as con:
kpis = _rows(
con,
"""
SELECT
(SELECT COUNT(*) FROM fact_flights) AS total_flights,
(SELECT COUNT(*) FROM fact_flights WHERE status = 'cancelled') AS cancelled,
(SELECT ROUND(AVG(delay_minutes), 1) FROM fact_flights WHERE status != 'cancelled')
AS avg_delay,
(SELECT COUNT(*) FROM dim_airport) AS airports,
(SELECT COUNT(*) FROM dim_airline) AS airlines,
(SELECT COUNT(*) FROM dim_date) AS days_covered
""",
)
top_airports = _rows(
con,
"""
SELECT airport_icao, total_flights, avg_delay_minutes, on_time_rate
FROM gold_airport_metrics
ORDER BY total_flights DESC LIMIT 10
""",
)
airlines = _rows(
con,
"""
SELECT airline_icao, airline_name, total_flights, avg_delay_minutes, on_time_rate
FROM gold_airline_rankings
ORDER BY total_flights DESC LIMIT 10
""",
)
delays = _rows(
con, "SELECT status, flight_count, avg_delay_minutes FROM gold_delay_analysis"
)
weather = _rows(
con,
"""
SELECT weather_condition, flight_count, avg_delay_minutes, avg_temperature_c
FROM gold_weather_impact ORDER BY flight_count DESC LIMIT 8
""",
)
seasonal = _rows(
con,
"""
SELECT flight_date, hour_of_day, flight_count, avg_delay_minutes
FROM gold_seasonal_trends ORDER BY flight_date, hour_of_day
""",
)
fuel = _rows(
con,
"SELECT date, region, price_per_litre FROM gold_fuel_price_series ORDER BY date",
)
return {
"generated_at": _dt.datetime.now(_dt.UTC).isoformat(),
"kpis": kpis[0] if kpis else {},
"top_airports": top_airports,
"airlines": airlines,
"delays": delays,
"weather": weather,
"seasonal": seasonal,
"fuel": fuel,
}
# ── HTML template (vanilla JS + inline SVG, zero dependencies) ───────────────
_TEMPLATE = """
Air Traffic Analytics — Self-Contained Dashboard