import json
import os
import sqlite3
from contextlib import contextmanager
from datetime import date, datetime, timedelta


BASE_DIR = os.path.dirname(os.path.dirname(__file__))
DATA_DIR = os.path.join(BASE_DIR, "data")
DB_PATH = os.path.join(DATA_DIR, "flight_history.sqlite3")
LEGACY_PATH = os.path.join(BASE_DIR, "flight_counter.json")
LEGACY_BACKUP_PATH = LEGACY_PATH + ".migrated"
ROUTE_CACHE_PATH = os.path.join(BASE_DIR, ".cache", "route_cache.sqlite3")


def _connect():
    os.makedirs(DATA_DIR, exist_ok=True)
    conn = sqlite3.connect(DB_PATH, timeout=15)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA synchronous=NORMAL")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS flight_events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            seen_at TEXT NOT NULL,
            flight_date TEXT NOT NULL,
            flight_time TEXT NOT NULL,
            hour INTEGER NOT NULL,
            callsign TEXT NOT NULL,
            origin TEXT NOT NULL DEFAULT '',
            destination TEXT NOT NULL DEFAULT '',
            airline TEXT NOT NULL DEFAULT '',
            aircraft_type TEXT NOT NULL DEFAULT '',
            source TEXT NOT NULL DEFAULT '',
            latitude REAL,
            longitude REAL,
            altitude REAL,
            distance REAL,
            UNIQUE(flight_date, callsign)
        )
    """)
    conn.execute("CREATE INDEX IF NOT EXISTS idx_history_date ON flight_events(flight_date)")
    conn.execute("CREATE INDEX IF NOT EXISTS idx_history_seen_at ON flight_events(seen_at)")
    conn.execute("CREATE INDEX IF NOT EXISTS idx_history_callsign ON flight_events(callsign)")
    return conn


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


def _normalise_legacy(data):
    if not isinstance(data, dict):
        return {}
    if "date" in data and "callsigns" in data:
        day = str(data.get("date") or "")
        return {
            day: {
                "date": day,
                "flights": [
                    {"callsign": callsign, "time": "00:00:00", "hour": 0}
                    for callsign in data.get("callsigns", [])
                ],
            }
        }
    return data


def migrate_legacy_json():
    if not os.path.exists(LEGACY_PATH):
        return 0
    try:
        with open(LEGACY_PATH, "r", encoding="utf-8") as f:
            data = _normalise_legacy(json.load(f))
    except (OSError, ValueError):
        return 0

    inserted = 0
    metadata = _route_metadata(
        flight.get("callsign")
        for day_data in data.values()
        if isinstance(day_data, dict)
        for flight in day_data.get("flights", [])
        if isinstance(flight, dict)
    )
    with _connection() as conn:
        for day, day_data in data.items():
            if not isinstance(day_data, dict):
                continue
            for flight in day_data.get("flights", []):
                callsign = str(flight.get("callsign") or "").strip().upper()
                if not callsign:
                    continue
                route = metadata.get(callsign, {})
                flight_time = str(flight.get("time") or "00:00:00")
                try:
                    hour = int(flight.get("hour", flight_time.split(":")[0]))
                except (TypeError, ValueError, IndexError):
                    hour = 0
                cursor = conn.execute(
                    """
                    INSERT OR IGNORE INTO flight_events
                        (seen_at, flight_date, flight_time, hour, callsign, origin, destination,
                         airline, aircraft_type, source)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """,
                    (
                        f"{day} {flight_time}",
                        str(day),
                        flight_time,
                        hour,
                        callsign,
                        str(flight.get("origin") or ""),
                        str(flight.get("dest") or flight.get("destination") or ""),
                        str(flight.get("airline") or route.get("airline") or ""),
                        str(flight.get("aircraft_type") or flight.get("plane") or route.get("aircraft_type") or ""),
                        str(flight.get("source") or route.get("source") or ""),
                    ),
                )
                inserted += cursor.rowcount

    try:
        if not os.path.exists(LEGACY_BACKUP_PATH):
            os.replace(LEGACY_PATH, LEGACY_BACKUP_PATH)
    except OSError:
        pass
    return inserted


def ensure_ready():
    with _connection():
        pass
    migrate_legacy_json()


def _route_metadata(callsigns):
    callsigns = sorted({str(value or "").strip().upper() for value in callsigns if value})
    if not callsigns or not os.path.exists(ROUTE_CACHE_PATH):
        return {}
    placeholders = ",".join("?" for _ in callsigns)
    output = {}
    try:
        from utilities import route_db
        route_db.stats()
        conn = sqlite3.connect(ROUTE_CACHE_PATH, timeout=10)
        conn.row_factory = sqlite3.Row
        try:
            rows = conn.execute(
                f"""
                SELECT callsign, flight_date, data_json, source
                FROM route_cache
                WHERE callsign IN ({placeholders})
                ORDER BY flight_date DESC, last_seen DESC
                """,
                callsigns,
            ).fetchall()
        finally:
            conn.close()
        for row in rows:
            try:
                data = json.loads(row["data_json"] or "{}")
            except (TypeError, ValueError):
                data = {}
            callsign = str(row["callsign"] or "").strip().upper()
            if callsign in output:
                continue
            output[callsign] = {
                "flight_date": row["flight_date"] or "",
                "airline": data.get("airline") or data.get("airline_name") or "",
                "aircraft_type": (
                    data.get("plane")
                    or data.get("plane_type")
                    or data.get("aircraft_type")
                    or ""
                ),
                "source": row["source"] or "",
                "raw": data,
            }
    except (OSError, sqlite3.Error):
        return {}
    return output


def log_flight(callsign, entry=None, retention_days=0, now=None):
    entry = entry or {}
    callsign = str(callsign or "").strip().upper()
    if not callsign:
        return False
    now = now or datetime.now()
    ensure_ready()
    with _connection() as conn:
        cursor = conn.execute(
            """
            INSERT OR IGNORE INTO flight_events (
                seen_at, flight_date, flight_time, hour, callsign, origin, destination,
                airline, aircraft_type, source, latitude, longitude, altitude, distance
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (
                now.strftime("%Y-%m-%d %H:%M:%S"),
                now.strftime("%Y-%m-%d"),
                now.strftime("%H:%M:%S"),
                now.hour,
                callsign,
                str(entry.get("origin") or entry.get("origin_iata") or ""),
                str(entry.get("destination") or entry.get("dest_iata") or ""),
                str(entry.get("airline_name") or entry.get("airline") or ""),
                str(entry.get("plane") or entry.get("aircraft_type") or entry.get("plane_type") or ""),
                str(entry.get("position_source") or entry.get("source") or ""),
                entry.get("plane_latitude", entry.get("latitude")),
                entry.get("plane_longitude", entry.get("longitude")),
                entry.get("altitude"),
                entry.get("distance"),
            ),
        )
        if retention_days and int(retention_days) > 0:
            cutoff = str(date.today() - timedelta(days=int(retention_days)))
            conn.execute("DELETE FROM flight_events WHERE flight_date < ?", (cutoff,))
        return cursor.rowcount > 0


def legacy_log(start_date=None, end_date=None):
    ensure_ready()
    clauses = []
    params = []
    if start_date:
        clauses.append("flight_date >= ?")
        params.append(start_date)
    if end_date:
        clauses.append("flight_date <= ?")
        params.append(end_date)
    where = (" WHERE " + " AND ".join(clauses)) if clauses else ""
    with _connection() as conn:
        rows = conn.execute(
            f"""
            SELECT id, seen_at, flight_date, flight_time, hour, callsign, origin, destination,
                   airline, aircraft_type, source, latitude, longitude, altitude, distance
            FROM flight_events{where}
            ORDER BY seen_at, id
            """,
            params,
        ).fetchall()

    metadata = _route_metadata(row["callsign"] for row in rows)
    output = {}
    for row in rows:
        day = row["flight_date"]
        route = metadata.get(row["callsign"], {})
        item = output.setdefault(day, {
            "date": day,
            "count": 0,
            "flights": [],
            "first_seen": row["flight_time"],
            "last_seen": row["flight_time"],
        })
        item["flights"].append({
            "id": row["id"],
            "seen_at": row["seen_at"],
            "callsign": row["callsign"],
            "time": row["flight_time"],
            "hour": row["hour"],
            "origin": row["origin"],
            "dest": row["destination"],
            "airline": row["airline"] or route.get("airline", ""),
            "aircraft_type": row["aircraft_type"] or route.get("aircraft_type", ""),
            "source": row["source"] or route.get("source", ""),
            "latitude": row["latitude"],
            "longitude": row["longitude"],
            "altitude": row["altitude"],
            "distance": row["distance"],
        })
        item["count"] += 1
        item["first_seen"] = min(item["first_seen"], row["flight_time"])
        item["last_seen"] = max(item["last_seen"], row["flight_time"])
    return output


def daily_summary(start_date=None, end_date=None):
    ensure_ready()
    clauses = []
    params = []
    if start_date:
        clauses.append("flight_date >= ?")
        params.append(start_date)
    if end_date:
        clauses.append("flight_date <= ?")
        params.append(end_date)
    where = (" WHERE " + " AND ".join(clauses)) if clauses else ""
    with _connection() as conn:
        rows = conn.execute(
            f"""
            SELECT flight_date AS date, COUNT(*) AS count,
                   MIN(flight_time) AS first_seen, MAX(flight_time) AS last_seen
            FROM flight_events{where}
            GROUP BY flight_date ORDER BY flight_date
            """,
            params,
        ).fetchall()
        hourly = conn.execute(
            f"""
            SELECT flight_date, hour, COUNT(*) AS count
            FROM flight_events{where}
            GROUP BY flight_date, hour
            """,
            params,
        ).fetchall()
    by_day = {row["date"]: [0] * 24 for row in rows}
    for row in hourly:
        by_day[row["flight_date"]][int(row["hour"])] = row["count"]
    return [{
        "date": row["date"],
        "count": row["count"],
        "by_hour": by_day[row["date"]],
        "first_seen": row["first_seen"],
        "last_seen": row["last_seen"],
    } for row in rows]


def callsign_summary():
    ensure_ready()
    with _connection() as conn:
        rows = conn.execute(
            """
            SELECT callsign, COUNT(*) AS seen_count,
                   MIN(seen_at) AS first_seen, MAX(seen_at) AS last_seen
            FROM flight_events
            GROUP BY callsign
            """
        ).fetchall()
    return [dict(row) for row in rows]


def event_raw(event_id):
    ensure_ready()
    try:
        event_id = int(event_id)
    except (TypeError, ValueError):
        return None
    with _connection() as conn:
        row = conn.execute(
            "SELECT * FROM flight_events WHERE id = ?",
            (event_id,),
        ).fetchone()
    if not row:
        return None
    event = dict(row)
    route = _route_metadata([event.get("callsign")]).get(event.get("callsign"), {})
    if not event.get("airline"):
        event["airline"] = route.get("airline", "")
    if not event.get("aircraft_type"):
        event["aircraft_type"] = route.get("aircraft_type", "")
    if not event.get("source"):
        event["source"] = route.get("source", "")
    event["route_cache"] = route.get("raw")
    return event


def delete_range(start_date, end_date):
    ensure_ready()
    with _connection() as conn:
        cursor = conn.execute(
            "DELETE FROM flight_events WHERE flight_date BETWEEN ? AND ?",
            (start_date, end_date),
        )
        return cursor.rowcount


def totals():
    ensure_ready()
    with _connection() as conn:
        row = conn.execute(
            """
            SELECT COUNT(*) AS events, COUNT(DISTINCT flight_date) AS days,
                   MIN(flight_date) AS first_date, MAX(flight_date) AS last_date
            FROM flight_events
            """
        ).fetchone()
    return dict(row)
