"""transit's GTFS static-schedule refresh, from docs/project-plan.md Phase 19.

Downloads Vorarlberg's own published GTFS static feed (VVV/VMOBIL, sourced via
Austria's national aggregator at mobilitaetsdaten.gv.at — real, existing
infrastructure, not something this repo generates) and keeps only the
stops/routes/trips relevant to the household's configured stop(s), in a small local
SQLite DB `server.py` queries. A full national/regional GTFS zip is large and mostly
irrelevant to one household; filtering at sync time is what keeps `server.py`'s
lookups fast and the dataset small, not a general-purpose transit database.

Oneshot + systemd timer (weekly — GTFS schedules are published a season/year at a
time, not daily), same shape as digest-engine/trash-calendar.
"""

from __future__ import annotations

import csv
import io
import logging
import os
import sqlite3
import sys
import urllib.request
import zipfile
from pathlib import Path

LOG = logging.getLogger("transit-sync")

DB_PATH = Path(os.environ.get("TRANSIT_DB_PATH", "/data/transit.db"))


def _stop_names() -> list[str]:
    return [s.strip().lower() for s in os.environ.get("GTFS_STOP_NAMES", "").split(",") if s.strip()]


def _read_csv(zf: zipfile.ZipFile, name: str) -> list[dict]:
    try:
        with zf.open(name) as fh:
            text = io.TextIOWrapper(fh, encoding="utf-8-sig")
            return list(csv.DictReader(text))
    except KeyError:
        LOG.warning("transit-sync: %s not present in the GTFS feed, skipping", name)
        return []


def init_db(conn: sqlite3.Connection) -> None:
    conn.executescript(
        """
        DROP TABLE IF EXISTS stops;
        DROP TABLE IF EXISTS routes;
        DROP TABLE IF EXISTS trips;
        DROP TABLE IF EXISTS stop_times;
        DROP TABLE IF EXISTS calendar;
        DROP TABLE IF EXISTS calendar_dates;
        CREATE TABLE stops (stop_id TEXT PRIMARY KEY, stop_name TEXT);
        CREATE TABLE routes (route_id TEXT PRIMARY KEY, short_name TEXT, long_name TEXT);
        CREATE TABLE trips (trip_id TEXT PRIMARY KEY, route_id TEXT, service_id TEXT, headsign TEXT);
        CREATE TABLE stop_times (trip_id TEXT, stop_id TEXT, departure_time TEXT);
        CREATE TABLE calendar (service_id TEXT PRIMARY KEY, monday INT, tuesday INT, wednesday INT,
            thursday INT, friday INT, saturday INT, sunday INT, start_date TEXT, end_date TEXT);
        CREATE TABLE calendar_dates (service_id TEXT, date TEXT, exception_type INT);
        CREATE INDEX idx_stop_times_stop ON stop_times(stop_id);
        CREATE INDEX idx_trips_service ON trips(service_id);
        """
    )


def sync() -> int:
    feed_url = os.environ.get("GTFS_FEED_URL", "").strip()
    if not feed_url:
        LOG.error("transit-sync: GTFS_FEED_URL is not set — see transit.env.example and README.md")
        return 1

    wanted_names = _stop_names()
    if not wanted_names:
        LOG.error("transit-sync: GTFS_STOP_NAMES is not set — nothing to filter down to")
        return 1

    LOG.info("transit-sync: downloading %s", feed_url)
    try:
        req = urllib.request.Request(feed_url, headers={"User-Agent": "smartesthome-transit/1"})
        with urllib.request.urlopen(req, timeout=120) as resp:
            data = resp.read()
    except Exception:
        LOG.error("transit-sync: could not download GTFS_FEED_URL", exc_info=True)
        return 1

    try:
        zf = zipfile.ZipFile(io.BytesIO(data))
    except zipfile.BadZipFile:
        LOG.error("transit-sync: downloaded file is not a valid GTFS zip")
        return 1

    all_stops = _read_csv(zf, "stops.txt")
    matched_stops = [
        s for s in all_stops
        if any(name in (s.get("stop_name") or "").lower() for name in wanted_names)
    ]
    if not matched_stops:
        LOG.error(
            "transit-sync: none of GTFS_STOP_NAMES (%s) matched any stop_name in the feed — "
            "check spelling against the feed's own stops.txt",
            ", ".join(wanted_names),
        )
        return 1
    matched_stop_ids = {s["stop_id"] for s in matched_stops}
    LOG.info("transit-sync: matched %d stop(s): %s", len(matched_stops), [s["stop_name"] for s in matched_stops])

    all_stop_times = _read_csv(zf, "stop_times.txt")
    relevant_stop_times = [st for st in all_stop_times if st.get("stop_id") in matched_stop_ids]
    relevant_trip_ids = {st["trip_id"] for st in relevant_stop_times}

    all_trips = _read_csv(zf, "trips.txt")
    relevant_trips = [t for t in all_trips if t.get("trip_id") in relevant_trip_ids]
    relevant_route_ids = {t["route_id"] for t in relevant_trips}
    relevant_service_ids = {t["service_id"] for t in relevant_trips}

    all_routes = _read_csv(zf, "routes.txt")
    relevant_routes = [r for r in all_routes if r.get("route_id") in relevant_route_ids]

    all_calendar = _read_csv(zf, "calendar.txt")
    relevant_calendar = [c for c in all_calendar if c.get("service_id") in relevant_service_ids]

    all_calendar_dates = _read_csv(zf, "calendar_dates.txt")
    relevant_calendar_dates = [c for c in all_calendar_dates if c.get("service_id") in relevant_service_ids]

    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    try:
        init_db(conn)
        conn.executemany(
            "INSERT OR REPLACE INTO stops VALUES (?, ?)",
            [(s["stop_id"], s.get("stop_name", "")) for s in matched_stops],
        )
        conn.executemany(
            "INSERT OR REPLACE INTO routes VALUES (?, ?, ?)",
            [(r["route_id"], r.get("route_short_name", ""), r.get("route_long_name", "")) for r in relevant_routes],
        )
        conn.executemany(
            "INSERT OR REPLACE INTO trips VALUES (?, ?, ?, ?)",
            [(t["trip_id"], t.get("route_id", ""), t.get("service_id", ""), t.get("trip_headsign", "")) for t in relevant_trips],
        )
        conn.executemany(
            "INSERT INTO stop_times VALUES (?, ?, ?)",
            [(st["trip_id"], st["stop_id"], st.get("departure_time", "")) for st in relevant_stop_times],
        )
        conn.executemany(
            "INSERT OR REPLACE INTO calendar VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            [
                (
                    c["service_id"], int(c.get("monday", 0) or 0), int(c.get("tuesday", 0) or 0),
                    int(c.get("wednesday", 0) or 0), int(c.get("thursday", 0) or 0),
                    int(c.get("friday", 0) or 0), int(c.get("saturday", 0) or 0),
                    int(c.get("sunday", 0) or 0), c.get("start_date", ""), c.get("end_date", ""),
                )
                for c in relevant_calendar
            ],
        )
        conn.executemany(
            "INSERT INTO calendar_dates VALUES (?, ?, ?)",
            [(c["service_id"], c.get("date", ""), int(c.get("exception_type", 0) or 0)) for c in relevant_calendar_dates],
        )
        conn.commit()
    finally:
        conn.close()

    LOG.info(
        "transit-sync: stored %d stop_times across %d trips for %d stop(s)",
        len(relevant_stop_times), len(relevant_trips), len(matched_stops),
    )
    return 0


def main() -> int:
    logging.basicConfig(
        level=os.environ.get("LOG_LEVEL", "INFO").upper(),
        format="%(asctime)s %(levelname)-7s %(name)s: %(message)s",
    )
    return sync()


if __name__ == "__main__":
    sys.exit(main())
