import json
import os
import sqlite3
import time
from contextlib import contextmanager
from datetime import datetime, timezone


BASE_DIR = os.path.dirname(os.path.dirname(__file__))
CACHE_DIR = os.path.join(BASE_DIR, ".cache")
DB_PATH = os.path.join(CACHE_DIR, "route_cache.sqlite3")


def _enabled():
    try:
        import config
        return bool(config._cfg.get("route_cache", {}).get("enabled", True))
    except Exception:
        return True


def _ttl_seconds():
    try:
        import config
        days = int(config._cfg.get("route_cache", {}).get("days", 30))
    except Exception:
        days = 30
    return max(days, 1) * 86400


def flight_date(data, fallback_ts=None):
    data = data or {}
    for field in ("time_scheduled_departure", "time_real_departure"):
        value = data.get(field)
        try:
            if value not in (None, ""):
                return datetime.fromtimestamp(float(value), timezone.utc).strftime("%Y-%m-%d")
        except (TypeError, ValueError, OSError):
            pass
    stamp = int(fallback_ts or time.time())
    return datetime.fromtimestamp(stamp, timezone.utc).strftime("%Y-%m-%d")


def _create_schema(conn):
    conn.execute("""
        CREATE TABLE IF NOT EXISTS route_cache (
            callsign TEXT NOT NULL,
            flight_date TEXT NOT NULL,
            data_json TEXT NOT NULL,
            source TEXT NOT NULL DEFAULT '',
            created_by TEXT NOT NULL DEFAULT 'api',
            parent_flight_date TEXT NOT NULL DEFAULT '',
            last_match_type TEXT NOT NULL DEFAULT '',
            first_seen INTEGER NOT NULL,
            last_seen INTEGER NOT NULL,
            last_used INTEGER NOT NULL DEFAULT 0,
            hit_count INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (callsign, flight_date)
        )
    """)
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_route_cache_last_seen "
        "ON route_cache(last_seen)"
    )
    conn.execute(
        "CREATE INDEX IF NOT EXISTS idx_route_cache_callsign "
        "ON route_cache(callsign, flight_date DESC)"
    )


def _migrate_schema(conn):
    columns = conn.execute("PRAGMA table_info(route_cache)").fetchall()
    if not columns:
        _create_schema(conn)
        return
    if any(row[1] == "flight_date" for row in columns):
        existing = {row[1] for row in columns}
        additions = (
            ("created_by", "TEXT NOT NULL DEFAULT 'api'"),
            ("parent_flight_date", "TEXT NOT NULL DEFAULT ''"),
            ("last_match_type", "TEXT NOT NULL DEFAULT ''"),
            ("last_used", "INTEGER NOT NULL DEFAULT 0"),
        )
        for name, definition in additions:
            if name not in existing:
                conn.execute(
                    f"ALTER TABLE route_cache ADD COLUMN {name} {definition}"
                )
        conn.execute(
            """
            UPDATE route_cache
            SET created_by = 'schedule_match',
                source = 'Unknown legacy provider'
            WHERE source LIKE 'RouteDB(schedule)%'
              AND created_by = 'api'
            """
        )
        conn.execute(
            """
            UPDATE route_cache
            SET created_by = 'recent_match',
                source = 'Unknown legacy provider'
            WHERE source LIKE 'RouteDB(history)%'
              AND created_by = 'api'
            """
        )
        _create_schema(conn)
        return

    conn.execute("ALTER TABLE route_cache RENAME TO route_cache_legacy")
    _create_schema(conn)
    rows = conn.execute(
        """
        SELECT callsign, data_json, source, first_seen, last_seen, hit_count
        FROM route_cache_legacy
        """
    ).fetchall()
    for callsign, data_json, source, first_seen, last_seen, hit_count in rows:
        try:
            data = json.loads(data_json or "{}")
        except (TypeError, ValueError):
            data = {}
        instance_date = flight_date(data, first_seen)
        conn.execute(
            """
            INSERT OR REPLACE INTO route_cache
                (callsign, flight_date, data_json, source,
                 first_seen, last_seen, hit_count)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            """,
            (
                (callsign or "").strip().upper(),
                instance_date,
                data_json,
                source or "",
                int(first_seen or time.time()),
                int(last_seen or time.time()),
                int(hit_count or 0),
            ),
        )
    conn.execute("DROP TABLE route_cache_legacy")
    _create_schema(conn)
    print(f"[RouteDB] migrated {len(rows)} routes to callsign + flight date")


def _connect():
    os.makedirs(CACHE_DIR, exist_ok=True)
    conn = sqlite3.connect(DB_PATH, timeout=10)
    _migrate_schema(conn)
    return conn


@contextmanager
def _connection():
    conn = _connect()
    try:
        with conn:
            yield conn
    finally:
        conn.close()


def get_routes(callsign):
    if not _enabled():
        return []
    callsign = (callsign or "").strip().upper()
    if not callsign:
        return []
    now = int(time.time())
    try:
        with _connection() as conn:
            conn.row_factory = sqlite3.Row
            conn.execute(
                "DELETE FROM route_cache WHERE last_seen < ?",
                (now - _ttl_seconds(),),
            )
            rows = conn.execute(
                """
                SELECT callsign, flight_date, data_json, source, created_by,
                       parent_flight_date, last_match_type,
                       first_seen, last_seen, last_used, hit_count
                FROM route_cache
                WHERE callsign = ?
                ORDER BY flight_date DESC, last_seen DESC
                """,
                (callsign,),
            ).fetchall()
            output = []
            for row in rows:
                try:
                    data = json.loads(row["data_json"] or "{}")
                except (TypeError, ValueError):
                    data = {}
                output.append({
                    "callsign": row["callsign"],
                    "flight_date": row["flight_date"],
                    "data": data,
                    "source": row["source"],
                    "created_by": row["created_by"],
                    "parent_flight_date": row["parent_flight_date"],
                    "last_match_type": row["last_match_type"],
                    "first_seen": row["first_seen"],
                    "last_seen": row["last_seen"],
                    "last_used": row["last_used"],
                    "hit_count": row["hit_count"],
                })
            return output
    except Exception as e:
        print(f"[RouteDB] read failed for {callsign}: {e}")
        return []


def get_route(callsign, instance_date=None, match_type="exact"):
    routes = get_routes(callsign)
    if instance_date:
        routes = [row for row in routes if row["flight_date"] == instance_date]
    if not routes:
        return None
    selected = routes[0]
    try:
        record_hit(
            selected["callsign"],
            selected["flight_date"],
            match_type,
        )
    except Exception as e:
        print(f"[RouteDB] hit update failed for {callsign}: {e}")
    return selected["data"]


def record_hit(callsign, instance_date, match_type):
    now = int(time.time())
    try:
        with _connection() as conn:
            conn.execute(
                """
                UPDATE route_cache
                SET last_seen = ?, last_used = ?,
                    last_match_type = ?, hit_count = hit_count + 1
                WHERE callsign = ? AND flight_date = ?
                """,
                (
                    now,
                    now,
                    str(match_type or ""),
                    (callsign or "").strip().upper(),
                    instance_date,
                ),
            )
    except Exception as e:
        print(f"[RouteDB] hit update failed for {callsign}: {e}")


def save_route(
    callsign,
    data,
    source="",
    created_by="api",
    parent_flight_date="",
):
    if not _enabled() or not data:
        return
    callsign = (callsign or "").strip().upper()
    if not callsign:
        return
    now = int(time.time())
    instance_date = flight_date(data, now)
    try:
        payload = json.dumps(data, separators=(",", ":"), sort_keys=True)
        with _connection() as conn:
            row = conn.execute(
                """
                SELECT first_seen, hit_count, source, created_by,
                       parent_flight_date, last_match_type, last_used
                FROM route_cache
                WHERE callsign = ? AND flight_date = ?
                """,
                (callsign, instance_date),
            ).fetchone()
            first_seen = int(row[0]) if row else now
            hit_count = int(row[1]) if row else 0
            original_source = (
                str(source or "")
                or (str(row[2] or "") if row else "")
            )
            stored_created_by = (
                str(created_by or "")
                or (str(row[3] or "") if row else "api")
            )
            stored_parent = (
                str(parent_flight_date or "")
                or (str(row[4] or "") if row else "")
            )
            last_match_type = str(row[5] or "") if row else ""
            last_used = int(row[6] or 0) if row else 0
            conn.execute(
                """
                INSERT OR REPLACE INTO route_cache
                    (callsign, flight_date, data_json, source,
                     created_by, parent_flight_date, last_match_type,
                     first_seen, last_seen, last_used, hit_count)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    callsign,
                    instance_date,
                    payload,
                    original_source,
                    stored_created_by,
                    stored_parent,
                    last_match_type,
                    first_seen,
                    now,
                    last_used,
                    hit_count,
                ),
            )
    except Exception as e:
        print(f"[RouteDB] save failed for {callsign}: {e}")


def delete_route(callsign, instance_date=None):
    callsign = (callsign or "").strip().upper()
    if not callsign:
        return
    try:
        with _connection() as conn:
            if instance_date:
                conn.execute(
                    "DELETE FROM route_cache WHERE callsign = ? AND flight_date = ?",
                    (callsign, instance_date),
                )
            else:
                conn.execute(
                    "DELETE FROM route_cache WHERE callsign = ?",
                    (callsign,),
                )
    except Exception as e:
        print(f"[RouteDB] delete failed for {callsign}: {e}")


def stats():
    try:
        with _connection() as conn:
            count = conn.execute("SELECT COUNT(*) FROM route_cache").fetchone()[0]
            callsigns = conn.execute(
                "SELECT COUNT(DISTINCT callsign) FROM route_cache"
            ).fetchone()[0]
            hits = conn.execute(
                "SELECT COALESCE(SUM(hit_count), 0) FROM route_cache"
            ).fetchone()[0]
            return {
                "count": count,
                "callsigns": callsigns,
                "hits": hits,
                "path": DB_PATH,
            }
    except Exception:
        return {
            "count": 0,
            "callsigns": 0,
            "hits": 0,
            "path": DB_PATH,
        }
