"""AIS Guardian persistence — the relational store, safeguards, and coverage.

A GeoPackage is a SQLite database, so everything here that isn't a drawn polygon
is handled with the standard-library `sqlite3` module: the reusable target
groups, the persistent event log, and the Class A/B coverage grid. This keeps
the whole layer testable and free of any GDAL/QGIS dependency for storage.

The one piece that is genuinely QGIS-bound — creating the empty `guardZones`
polygon layer the user draws into — lives in the wiring step, where QGIS itself
produces a guaranteed-valid GeoPackage; the tables here are then added to that
same file (config travels with the geometry).

Layout (under <profile>/ais_nmea_source/ by default, configurable):
  ais_guardian_zones.gpkg     guardZones layer (QGIS) + the tables below
    target_groups(group_id, name, created_utc)
    group_members(group_id, mmsi, cached_name)
    guardian_events(... persistent evidence log ...)
  ais_guardian_coverage.gpkg  (separate file, avoids write-lock contention)
    coverage_cells(cls, cx, cy, n)

Safeguards: rolling timestamped backups, integrity check on open, and
recreate-the-tables-if-missing so an accidental deletion costs data, not a crash.
"""
import os
import csv
import shutil
import sqlite3
import datetime

# The relational store (groups, members, events). The user-drawn guardZones
# SPATIAL layer lives in its own QGIS-owned GeoPackage (ZONE_LAYER_DB) so QGIS
# produces guaranteed-valid spatial metadata; the value-relation form links the
# two across files at the project level.
ZONES_DB = "ais_guardian_store.gpkg"
ZONE_LAYER_DB = "ais_guardian_zones.gpkg"
COVERAGE_DB = "ais_guardian_coverage.gpkg"
ZONES_LAYER = "guardZones"
BACKUP_DIRNAME = "backups"

_ZONES_TABLES = {"target_groups", "group_members", "guardian_events"}
_COVERAGE_TABLES = {"coverage_cells", "receiver_position"}

_SCHEMA_ZONES = """
CREATE TABLE IF NOT EXISTS target_groups (
    group_id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT NOT NULL UNIQUE,
    created_utc TEXT,
    alarm3     INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS group_members (
    group_id    INTEGER NOT NULL,
    mmsi        TEXT NOT NULL,
    cached_name TEXT,
    PRIMARY KEY (group_id, mmsi),
    FOREIGN KEY (group_id) REFERENCES target_groups(group_id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS guardian_events (
    event_id    INTEGER PRIMARY KEY AUTOINCREMENT,
    created_utc TEXT,
    kind        TEXT,
    zone_name   TEXT,
    mmsi        TEXT,
    lon         REAL,
    lat         REAL,
    event_time  REAL,
    enter_time  REAL,
    detail      TEXT
);
"""

_SCHEMA_COVERAGE = """
CREATE TABLE IF NOT EXISTS coverage_cells (
    cls        TEXT NOT NULL,
    cx         INTEGER NOT NULL,
    cy         INTEGER NOT NULL,
    n          INTEGER NOT NULL DEFAULT 0,
    source_pos TEXT NOT NULL DEFAULT '',
    PRIMARY KEY (cls, cx, cy, source_pos)
);
CREATE TABLE IF NOT EXISTS receiver_position (
    id      INTEGER PRIMARY KEY CHECK (id = 1),
    lat     REAL NOT NULL,
    lon     REAL NOT NULL,
    set_utc TEXT
);
"""


def default_base_dir():
    """Default storage dir: the QGIS profile folder (survives upgrades, rarely
    browsed). Falls back to a home-dir folder when QGIS isn't importable."""
    try:
        from qgis.core import QgsApplication
        base = QgsApplication.qgisSettingsDirPath()
    except Exception:
        base = os.path.join(os.path.expanduser("~"), ".qgis_ais_guardian")
    return os.path.join(base, "ais_nmea_source")


def _utc_now():
    """Current UTC time as an ISO-8601 string (second precision) for stamps."""
    return datetime.datetime.now(datetime.timezone.utc).isoformat(timespec="seconds")


class GuardianStore:
    def __init__(self, base_dir=None, keep_backups=5):
        """Resolve the storage folder (default: profile dir), ensure it exists,
        and compute the store / zone-layer / coverage / backup paths."""
        self.base_dir = base_dir or default_base_dir()
        self.keep_backups = keep_backups
        os.makedirs(self.base_dir, exist_ok=True)
        self.zones_path = os.path.join(self.base_dir, ZONES_DB)        # relational store
        self.zone_layer_path = os.path.join(self.base_dir, ZONE_LAYER_DB)  # spatial layer (QGIS-owned)
        self.coverage_path = os.path.join(self.base_dir, COVERAGE_DB)
        self.backup_dir = os.path.join(self.base_dir, BACKUP_DIRNAME)

    # ---- connections -----------------------------------------------------
    def _connect(self, path):
        """Open a SQLite connection with WAL journaling and foreign keys on."""
        con = sqlite3.connect(path)
        con.execute("PRAGMA journal_mode=WAL")     # reduce write-lock contention
        con.execute("PRAGMA foreign_keys=ON")
        return con

    # ---- schema / lifecycle ---------------------------------------------
    def ensure_tables(self):
        """Idempotently create the relational + coverage tables."""
        con = self._connect(self.zones_path)
        con.executescript(_SCHEMA_ZONES)
        self._ensure_column(con, "target_groups", "alarm3", "INTEGER NOT NULL DEFAULT 0")
        con.commit(); con.close()
        con = self._connect(self.coverage_path)
        con.executescript(_SCHEMA_COVERAGE)
        self._migrate_coverage_source(con)
        con.commit(); con.close()

    def _migrate_coverage_source(self, con):
        """Old coverage_cells (keyed cls,cx,cy with no source) -> rebuild with a
        source_pos column, stamping the existing rows with the last known
        receiver position so prior coverage is attributed to where it was heard."""
        try:
            cols = {r[1] for r in con.execute("PRAGMA table_info(coverage_cells)")}
            if not cols or "source_pos" in cols:
                return                              # fresh table or already migrated
            row = con.execute(
                "SELECT lat, lon FROM receiver_position WHERE id=1").fetchone()
            src = f"{row[0]:.5f},{row[1]:.5f}" if row else ""
            con.execute("ALTER TABLE coverage_cells RENAME TO _coverage_old")
            con.executescript(_SCHEMA_COVERAGE)
            con.execute(
                "INSERT INTO coverage_cells(cls,cx,cy,n,source_pos) "
                "SELECT cls,cx,cy,n,? FROM _coverage_old", (src,))
            con.execute("DROP TABLE _coverage_old")
        except Exception:
            pass

    def _ensure_column(self, con, table, column, decl):
        """Add a column to an existing table if it isn't there (light migration)."""
        try:
            cols = {r[1] for r in con.execute(f"PRAGMA table_info({table})")}
            if column not in cols:
                con.execute(f"ALTER TABLE {table} ADD COLUMN {column} {decl}")
        except Exception:
            pass

    def _pragma_ok(self, path):
        """True if the file is a structurally sound SQLite db (not corrupt).
        Does NOT require the managed tables — a valid GeoPackage that QGIS just
        created (guardZones only) is sound; ensure_tables() adds our tables."""
        try:
            con = sqlite3.connect(path)
            row = con.execute("PRAGMA integrity_check").fetchone()
            con.close()
            return bool(row) and row[0] == "ok"
        except Exception:
            return False

    def integrity_ok(self, path, required_tables):
        """True if the file passes SQLite integrity_check AND holds all the
        required tables (used to validate a backup before restoring it)."""
        if not os.path.exists(path):
            return False
        try:
            con = sqlite3.connect(path)
            row = con.execute("PRAGMA integrity_check").fetchone()
            names = {r[0] for r in con.execute(
                "SELECT name FROM sqlite_master WHERE type='table'")}
            con.close()
            return bool(row) and row[0] == "ok" and required_tables.issubset(names)
        except Exception:
            return False

    def ensure_with_safeguards(self):
        """Open the store defensively. Genuine corruption (failed integrity
        check) -> restore newest good backup, else recreate. A sound file that
        merely lacks our managed tables is fine — ensure_tables() fills them,
        which is also how we attach to a GeoPackage QGIS created for the zones.
        Never raises on a missing/corrupt file."""
        status = {}
        for key, path, req in (("zones", self.zones_path, _ZONES_TABLES),
                               ("coverage", self.coverage_path, _COVERAGE_TABLES)):
            if not os.path.exists(path):
                status[key] = "created"
            elif self._pragma_ok(path):
                status[key] = "ok"                  # sound; tables filled below
            else:
                restored = self.restore_latest_backup(path)
                status[key] = "restored-from-backup" if restored else "recreated"
        self.ensure_tables()                 # idempotent; creates any missing tables
        return status

    # ---- backups ---------------------------------------------------------
    def backup(self, which="zones"):
        """Write a timestamped backup of the zones or coverage db (consistent
        even under WAL via the SQLite backup API), pruning old ones. Returns the
        backup path or None if the source file doesn't exist."""
        path = self.zones_path if which == "zones" else self.coverage_path
        if not os.path.exists(path):
            return None
        os.makedirs(self.backup_dir, exist_ok=True)
        stamp = datetime.datetime.now().strftime("%Y%m%d-%H%M%S")
        dest = os.path.join(self.backup_dir, f"{os.path.basename(path)}.{stamp}.bak")
        try:
            # sqlite backup API gives a consistent copy even if WAL is active
            src = sqlite3.connect(path)
            dst = sqlite3.connect(dest)
            with dst:
                src.backup(dst)
            src.close(); dst.close()
        except Exception:
            shutil.copy2(path, dest)
        self._prune_backups(os.path.basename(path))
        return dest

    def _prune_backups(self, base_name):
        """Keep only the newest `keep_backups` backups for a given db file."""
        if not os.path.isdir(self.backup_dir):
            return
        baks = sorted(
            (f for f in os.listdir(self.backup_dir)
             if f.startswith(base_name + ".") and f.endswith(".bak")),
            reverse=True)
        for stale in baks[self.keep_backups:]:
            try:
                os.remove(os.path.join(self.backup_dir, stale))
            except OSError:
                pass

    def restore_latest_backup(self, path):
        """Copy the newest *integrity-valid* backup over a corrupt db file.
        Returns True on success, False if no usable backup exists."""
        if not os.path.isdir(self.backup_dir):
            return False
        base_name = os.path.basename(path)
        baks = sorted(
            (f for f in os.listdir(self.backup_dir)
             if f.startswith(base_name + ".") and f.endswith(".bak")),
            reverse=True)
        for cand in baks:
            full = os.path.join(self.backup_dir, cand)
            req = _ZONES_TABLES if base_name == ZONES_DB else _COVERAGE_TABLES
            if self.integrity_ok(full, req):
                try:
                    shutil.copy2(full, path)
                    return True
                except OSError:
                    continue
        return False

    # ---- target groups ---------------------------------------------------
    def create_group(self, name):
        """Insert a new target group and return its new group_id."""
        con = self._connect(self.zones_path)
        try:
            cur = con.execute(
                "INSERT INTO target_groups(name, created_utc) VALUES (?,?)",
                (name, _utc_now()))
            con.commit()
            return cur.lastrowid
        finally:
            con.close()

    def list_groups(self):
        """All target groups as [(group_id, name)], ordered by name."""
        con = self._connect(self.zones_path)
        try:
            return [(r[0], r[1]) for r in con.execute(
                "SELECT group_id, name FROM target_groups ORDER BY name")]
        finally:
            con.close()

    def list_groups_full(self):
        """[(group_id, name, alarm3_mode)] where alarm3_mode is
        0=off, 1=alarm on getting under way (to dynamic), 2=alarm on
        mooring/anchoring (to static)."""
        con = self._connect(self.zones_path)
        try:
            return [(r[0], r[1], int(r[2] or 0)) for r in con.execute(
                "SELECT group_id, name, alarm3 FROM target_groups ORDER BY name")]
        finally:
            con.close()

    def set_group_alarm3(self, group_id, mode):
        """mode: 0 off, 1 to-dynamic (getting under way), 2 to-static (mooring)."""
        con = self._connect(self.zones_path)
        try:
            con.execute("UPDATE target_groups SET alarm3=? WHERE group_id=?",
                        (int(mode), group_id))
            con.commit()
        finally:
            con.close()

    def delete_group(self, group_id):
        """Delete a target group; its members cascade-delete via the FK."""
        con = self._connect(self.zones_path)
        try:
            con.execute("DELETE FROM target_groups WHERE group_id=?", (group_id,))
            con.commit()
        finally:
            con.close()

    def add_member(self, group_id, mmsi, cached_name=None):
        """Add an MMSI to a group (or refresh its cached name if already there)."""
        con = self._connect(self.zones_path)
        try:
            con.execute(
                "INSERT INTO group_members(group_id, mmsi, cached_name) "
                "VALUES (?,?,?) ON CONFLICT(group_id, mmsi) "
                "DO UPDATE SET cached_name=excluded.cached_name",
                (group_id, str(mmsi), cached_name))
            con.commit()
        finally:
            con.close()

    def remove_member(self, group_id, mmsi):
        """Remove one MMSI from a group."""
        con = self._connect(self.zones_path)
        try:
            con.execute("DELETE FROM group_members WHERE group_id=? AND mmsi=?",
                        (group_id, str(mmsi)))
            con.commit()
        finally:
            con.close()

    def list_members(self, group_id):
        """Members of a group as [(mmsi, cached_name)], ordered by MMSI."""
        con = self._connect(self.zones_path)
        try:
            return [(r[0], r[1]) for r in con.execute(
                "SELECT mmsi, cached_name FROM group_members WHERE group_id=? "
                "ORDER BY mmsi", (group_id,))]
        finally:
            con.close()

    def group_members_map(self):
        """{group_id: set(mmsi)} — feeds GuardianEngine.set_group_members()."""
        con = self._connect(self.zones_path)
        try:
            out = {}
            for gid, mmsi in con.execute("SELECT group_id, mmsi FROM group_members"):
                out.setdefault(gid, set()).add(mmsi)
            return out
        finally:
            con.close()

    # ---- event log -------------------------------------------------------
    def log_event(self, event):
        """Append a guardian_core.Event (namedtuple) to the persistent log."""
        con = self._connect(self.zones_path)
        try:
            con.execute(
                "INSERT INTO guardian_events(created_utc, kind, zone_name, mmsi, "
                "lon, lat, event_time, enter_time, detail) VALUES (?,?,?,?,?,?,?,?,?)",
                (_utc_now(), event.kind, event.zone_name, event.mmsi,
                 event.lon, event.lat, event.time, event.enter_time, event.detail))
            con.commit()
        finally:
            con.close()

    def read_events(self, limit=500):
        """Most recent `limit` event-log rows as dicts, newest first."""
        con = self._connect(self.zones_path)
        try:
            con.row_factory = sqlite3.Row
            rows = con.execute(
                "SELECT * FROM guardian_events ORDER BY event_id DESC LIMIT ?",
                (limit,)).fetchall()
            return [dict(r) for r in rows]
        finally:
            con.close()

    def export_events_csv(self, out_path):
        """Write the entire event log to a CSV file (chronological); returns the
        number of rows written. Used by the Event-log tab's Export button."""
        con = self._connect(self.zones_path)
        try:
            con.row_factory = sqlite3.Row
            rows = con.execute(
                "SELECT * FROM guardian_events ORDER BY event_id").fetchall()
            cols = (["event_id", "created_utc", "kind", "zone_name", "mmsi",
                     "lon", "lat", "event_time", "enter_time", "detail"]
                    if not rows else rows[0].keys())
            with open(out_path, "w", newline="", encoding="utf-8") as fh:
                w = csv.writer(fh)
                w.writerow(cols)
                for r in rows:
                    w.writerow([r[c] for c in cols])
            return len(rows)
        finally:
            con.close()

    # ---- coverage --------------------------------------------------------
    def save_coverage(self, accumulator):
        """Upsert the current Class A/B grid, stamped with the accumulator's
        current source position. Old-source cells already in the table are left
        untouched."""
        src = getattr(accumulator, "source", "") or ""
        con = self._connect(self.coverage_path)
        try:
            rows = []
            for cls, grid in accumulator.cells.items():
                for (cx, cy), n in grid.items():
                    rows.append((cls, cx, cy, n, src))
            con.executemany(
                "INSERT INTO coverage_cells(cls, cx, cy, n, source_pos) "
                "VALUES (?,?,?,?,?) ON CONFLICT(cls, cx, cy, source_pos) "
                "DO UPDATE SET n=excluded.n", rows)
            con.commit()
            return len(rows)
        finally:
            con.close()

    def load_coverage(self, cls, source=""):
        """Cells for one class at one source position -> {(cx,cy): n}."""
        con = self._connect(self.coverage_path)
        try:
            return {(r[0], r[1]): r[2] for r in con.execute(
                "SELECT cx, cy, n FROM coverage_cells WHERE cls=? AND source_pos=?",
                (cls, source or ""))}
        finally:
            con.close()

    def load_all_coverage(self):
        """Every stored cell across all source positions, for the map layer:
        list of (cls, cx, cy, n, source_pos)."""
        con = self._connect(self.coverage_path)
        try:
            return [(r[0], r[1], r[2], r[3], r[4]) for r in con.execute(
                "SELECT cls, cx, cy, n, source_pos FROM coverage_cells")]
        finally:
            con.close()

    def load_coverage_into(self, accumulator):
        """Restore the cells for the accumulator's CURRENT source so it resumes
        accumulating them. Returns the number of cells restored."""
        src = getattr(accumulator, "source", "") or ""
        total = 0
        for cls in ("A", "B"):
            grid = self.load_coverage(cls, src)
            accumulator.cells[cls] = grid
            total += len(grid)
        return total

    # ---- receiver (own) position ----------------------------------------
    def save_own_position(self, lat, lon):
        """Persist the static receiver position set in the 'Vessels nearby' tab.
        Stored in the coverage dataset because the coverage footprint is defined
        relative to where the receiver sits."""
        con = self._connect(self.coverage_path)
        try:
            con.execute(
                "INSERT INTO receiver_position(id, lat, lon, set_utc) "
                "VALUES (1, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET "
                "lat=excluded.lat, lon=excluded.lon, set_utc=excluded.set_utc",
                (float(lat), float(lon), _utc_now()))
            con.commit()
        finally:
            con.close()

    def load_own_position(self):
        """Return (lat, lon) of the stored receiver position, or None."""
        con = self._connect(self.coverage_path)
        try:
            row = con.execute(
                "SELECT lat, lon FROM receiver_position WHERE id=1").fetchone()
            return (row[0], row[1]) if row else None
        finally:
            con.close()
