"""
The sources and streams the store knows about, defined in code and upserted on
`init`. Adding a stream is adding an entry here plus an ingester — Layer 1 then
watches it automatically.

expected_cadence_hours is how often the source publishes, not how often we
happen to poll it; latency_hours is how far behind "now" its newest data
legitimately is (Open-Meteo's reanalysis archive trails real time by ~5 days).
Layer 1 judges freshness against both.
"""
import sqlite3

DAY = 24
SOURCES = [
    # id, family, provider, licence, expected_cadence_hours, latency_hours
    ("geoboundaries", "location", "geoBoundaries (William & Mary geoLab)", "CC BY 4.0", 365 * DAY, 0),
    ("open_meteo_archive", "weather", "Open-Meteo (ECMWF ERA5 reanalysis)", "CC BY 4.0 data; API free tier is non-commercial", DAY, 7 * DAY),
    ("open_meteo_forecast", "weather", "Open-Meteo (ECMWF IFS and others)", "CC BY 4.0 data; API free tier is non-commercial", DAY, 0),
    ("overture_places", "places", "Overture Maps Foundation", "CDLA Permissive 2.0", 31 * DAY, 0),
    ("calendar", "calendar", "VenThrive (computed)", "n/a", 365 * DAY, 0),
    ("news_pipeline", "policy", "VenThrive news pipeline (ADR-0015)", "per-source", 7 * DAY, 0),
]

WEATHER_VARIABLES = [
    # Open-Meteo daily variable, unit
    ("temperature_2m_max", "°C"),
    ("temperature_2m_min", "°C"),
    ("precipitation_sum", "mm"),
    ("wind_speed_10m_max", "km/h"),
    # Low humidity with a dry north-easterly is the harmattan signature, so this
    # is the stream harmattan onset/strength is derived from.
    ("relative_humidity_2m_mean", "%"),
]

STREAMS = [
    # id, family, metric, unit, kind, native_level, cadence, source_id
    *[(f"weather.{v}", "weather", v, unit, "driver", "state", "daily", "open_meteo_archive") for v, unit in WEATHER_VARIABLES],
]


def seed(con: sqlite3.Connection) -> None:
    for sid, family, provider, licence, cadence, latency in SOURCES:
        con.execute(
            """insert into sources (id, family, provider, licence, expected_cadence_hours, latency_hours)
               values (?, ?, ?, ?, ?, ?)
               on conflict (id) do update set family = excluded.family, provider = excluded.provider,
                 licence = excluded.licence, expected_cadence_hours = excluded.expected_cadence_hours,
                 latency_hours = excluded.latency_hours""",
            (sid, family, provider, licence, cadence, latency),
        )
    for row in STREAMS:
        con.execute(
            """insert into streams (id, family, metric, unit, kind, native_level, cadence, source_id)
               values (?, ?, ?, ?, ?, ?, ?, ?)
               on conflict (id) do update set family = excluded.family, metric = excluded.metric,
                 unit = excluded.unit, kind = excluded.kind, native_level = excluded.native_level,
                 cadence = excluded.cadence, source_id = excluded.source_id""",
            row,
        )