# SPDX-License-Identifier: GPL-3.0-or-later
"""Read side of the AIS voyage history — query stored tracks back out for the
user, and turn a track into a QGIS project layer.

Two retrieval paths drive the UI:
  * window_track(mmsi, t0)  — the vessel's positions from ``before`` seconds before
    to ``after`` after a moment (e.g. an alarm in the event log): "show me".
  * list_voyages(mmsi) + voyage_track(voyage_id) — pick a past voyage of a vessel
    from the register and draw it.

Both backends (GeoPackage / PostGIS) answer the same three queries. A track comes
from the per-fix POINTS (stage 3); if none are stored it falls back to the voyage
LineString (stage 2). Reads use their OWN short-lived connection — independent of
the recorder's writer — so viewing history never blocks the live feed and works
even while recording is switched off.
"""
import re
import struct

_SAFE_SCHEMA = re.compile(r"^[A-Za-z_][A-Za-z0-9_]*$")


def _blob_envelope(blob):
    """(minx, miny, maxx, maxy) from a GeoPackage geometry blob's XY envelope
    header (cheap — no full WKB decode), or None if it carries no envelope."""
    try:
        if not blob or blob[:2] != b"GP":
            return None
        if ((blob[3] >> 1) & 0x07) != 1:            # flags: XY envelope present?
            return None
        mnx, mxx, mny, mxy = struct.unpack("<4d", blob[8:40])
        return (mnx, mny, mxx, mxy)
    except Exception:                                # noqa: BLE001
        return None


def _parse_wkt_line(wkt):
    """'LINESTRING(3.6 51.4,3.62 51.41)' -> [(3.6, 51.4), (3.62, 51.41)]."""
    if not wkt:
        return []
    i, j = wkt.find("("), wkt.rfind(")")
    if i < 0 or j < 0:
        return []
    out = []
    for pair in wkt[i + 1:j].split(","):
        xy = pair.strip().split()
        if len(xy) >= 2:
            try:
                out.append((float(xy[0]), float(xy[1])))
            except ValueError:
                pass
    return out


class HistoryReader:
    def __init__(self, cfg):
        self.cfg = cfg
        self._pg = cfg is not None and cfg.backend == "postgres"
        if self._pg and cfg is not None and not _SAFE_SCHEMA.match(cfg.pg_schema):
            raise ValueError("invalid schema name: %r" % cfg.pg_schema)

    # ---- availability ---------------------------------------------------- #
    def available(self):
        """(ok, reason). ok=False with a user-facing reason when history can't be
        read (not configured, file missing, no connection chosen)."""
        cfg = self.cfg
        if cfg is None:
            return False, "history is not configured"
        if self._pg:
            if not cfg.pg_conn_name:
                return False, "no PostgreSQL connection selected"
        else:
            import os
            if not os.path.exists(cfg.gpkg_path):
                return False, "no history recorded yet"
        # A configured backend is not enough — the tables must actually exist
        # (e.g. the schema was never provisioned, or was dropped). Without this
        # check a query would raise a raw 'relation ... does not exist' error.
        try:
            if not self._tables_exist():
                return False, "no history has been recorded yet"
        except Exception as exc:                       # noqa: BLE001
            return False, "history store unreachable (%s)" % exc
        return True, ""

    def _tables_exist(self):
        """True if the history tables are present in the active backend."""
        con = self._connect()
        try:
            cur = con.cursor()
            if self._pg:
                cur.execute("SELECT to_regclass(%s)",
                            ("%s.fixes" % self.cfg.pg_schema,))
                if cur.fetchone()[0] is not None:
                    return True
                cur.execute("SELECT to_regclass(%s)",
                            ("%s.voyages" % self.cfg.pg_schema,))
                return cur.fetchone()[0] is not None
            cur.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' "
                        "AND name IN ('fixes', 'voyages')")
            return cur.fetchone()[0] > 0
        finally:
            con.close()

    # ---- connection ------------------------------------------------------ #
    def _connect(self):
        if self._pg:
            import psycopg2
            from .history_config import pg_params_from_qgis
            return psycopg2.connect(**pg_params_from_qgis(self.cfg.pg_conn_name))
        import sqlite3
        return sqlite3.connect(self.cfg.gpkg_path)

    def _t(self, name):
        """Schema-qualified table name for the active backend."""
        return ("%s.%s" % (self.cfg.pg_schema, name)) if self._pg else name

    # ---- queries --------------------------------------------------------- #
    def list_voyages(self, mmsi, limit=300):
        """Voyages of one vessel, newest first: list of dicts (voyage_id, mmsi,
        start_t, end_t, end_reason, name, ship_type, destination)."""
        cols = ("voyage_id", "mmsi", "start_t", "end_t", "end_reason", "name",
                "ship_type", "destination")
        ph = "%s" if self._pg else "?"
        # Postgres stores start_t/end_t as TIMESTAMPTZ; return them as epoch floats
        # so the reader's contract (epoch) is uniform across backends.
        if self._pg:
            sel = ("voyage_id,mmsi,"
                   "EXTRACT(EPOCH FROM start_t)::double precision,"
                   "EXTRACT(EPOCH FROM end_t)::double precision,"
                   "end_reason,name,ship_type,destination")
        else:
            sel = ",".join(cols)
        sql = ("SELECT %s FROM %s WHERE mmsi=%s ORDER BY start_t DESC LIMIT %s"
               % (sel, self._t("voyages"), ph, ph))                  # nosec B608
        con = self._connect()
        try:
            cur = con.cursor()
            cur.execute(sql, (str(mmsi), int(limit)))
            return [dict(zip(cols, row)) for row in cur.fetchall()]
        finally:
            con.close()

    def voyage_track(self, voyage_id):
        """Ordered [(lon, lat), ...] for a voyage — from stored points if present,
        else the stored voyage LineString."""
        con = self._connect()
        try:
            pts = self._points(con, "voyage_id", voyage_id)
            if pts:
                return pts
            return self._stored_line(con, "voyage_id", voyage_id)
        finally:
            con.close()

    def window_track(self, mmsi, t0, before=300.0, after=300.0):
        """Ordered [(lon, lat), ...] for a vessel between t0-before and t0+after —
        from stored points, else the voyage LineString overlapping t0."""
        con = self._connect()
        try:
            ph = "%s" if self._pg else "?"
            lo, hi = t0 - before, t0 + after
            if self._pg:
                sql = ("SELECT ST_X(geom), ST_Y(geom) FROM %s WHERE mmsi=%s AND "
                       "t BETWEEN to_timestamp(%s) AND to_timestamp(%s) ORDER BY t"
                       % (self._t("fixes"), ph, ph, ph))             # nosec B608
                cur = con.cursor()
                cur.execute(sql, (str(mmsi), lo, hi))
                pts = [(float(x), float(y)) for x, y in cur.fetchall()]
            else:
                sql = ("SELECT geom FROM fixes WHERE mmsi=? AND t BETWEEN ? AND ? "
                       "ORDER BY t")
                pts = self._decode_points(con.execute(sql, (str(mmsi), lo, hi)))
            if pts:
                return pts
            # stage-2 fallback: the voyage LineString covering this moment
            return self._voyage_line_at(con, mmsi, t0)
        finally:
            con.close()

    # ---- History-tab queries -------------------------------------------- #
    def history_stats(self):
        """{voyages, vessels, fixes, events, t_min, t_max} for the tab header."""
        con = self._connect()
        try:
            cur = con.cursor()
            out = {}
            for t in ("voyages", "vessels", "fixes", "events"):
                cur.execute("SELECT COUNT(*) FROM %s" % self._t(t))   # nosec B608
                out[t] = cur.fetchone()[0]
            if self._pg:
                cur.execute("SELECT EXTRACT(EPOCH FROM MIN(start_t))::double "  # nosec B608
                            "precision, EXTRACT(EPOCH FROM MAX(COALESCE(end_t,"
                            "start_t)))::double precision FROM %s"
                            % self._t("voyages"))
            else:
                cur.execute("SELECT MIN(start_t), MAX(COALESCE(end_t, start_t)) "
                            "FROM voyages")
            row = cur.fetchone()
            out["t_min"], out["t_max"] = (row[0], row[1]) if row else (None, None)
            return out
        finally:
            con.close()

    def list_all_voyages(self, search=None, t_from=None, t_to=None, bbox=None,
                         limit=2000):
        """Voyages across ALL vessels, newest first, filtered by an optional
        name/MMSI substring, a time window [t_from, t_to] (epoch; overlap), and a
        bounding box (minx, miny, maxx, maxy) the track must cross. Rows are dicts
        (voyage_id, mmsi, name, start_t, end_t, end_reason, destination)."""
        con = self._connect()
        try:
            ph = "%s" if self._pg else "?"
            where, params = [], []
            if search and str(search).strip():
                # case-insensitive substring on both backends (SQLite LIKE is
                # already case-insensitive; PostgreSQL LIKE is not) — lower both
                # sides so 'cosco' matches COSCO / CoSco / …COSCO SHIPPING…
                like = "%" + str(search).strip().lower() + "%"
                where.append("(LOWER(v.mmsi) LIKE " + ph
                             + " OR LOWER(COALESCE(v.name, ve.name)) LIKE " + ph
                             + ")")
                params += [like, like]
            if t_to is not None:
                where.append("v.start_t <= " + (
                    "to_timestamp(" + ph + ")" if self._pg else ph))
                params.append(t_to)
            if t_from is not None:
                if self._pg:
                    where.append("COALESCE(v.end_t, now()) >= to_timestamp(" + ph + ")")
                else:
                    where.append("COALESCE(v.end_t, v.start_t) >= " + ph)
                params.append(t_from)
            if bbox is not None:
                ids = self._voyage_ids_in_bbox(con, bbox)
                if not ids:
                    return []
                where.append("v.voyage_id IN (%s)" % ",".join([ph] * len(ids)))
                params += list(ids)
            if self._pg:
                sel = ("v.voyage_id, v.mmsi, COALESCE(v.name, ve.name), "
                       "EXTRACT(EPOCH FROM v.start_t)::double precision, "
                       "EXTRACT(EPOCH FROM v.end_t)::double precision, "
                       "v.end_reason, v.destination")
            else:
                sel = ("v.voyage_id, v.mmsi, COALESCE(v.name, ve.name), "
                       "v.start_t, v.end_t, v.end_reason, v.destination")
            body = ("SELECT %s FROM %s v LEFT JOIN %s ve ON ve.mmsi = v.mmsi %s "  # nosec B608
                    "ORDER BY v.start_t DESC LIMIT ") % (
                sel, self._t("voyages"), self._t("vessels"),
                ("WHERE " + " AND ".join(where)) if where else "")
            sql = body + ph        # LIMIT placeholder appended AFTER formatting
            params.append(int(limit))
            cur = con.cursor()
            cur.execute(sql, params)
            cols = ("voyage_id", "mmsi", "name", "start_t", "end_t", "end_reason",
                    "destination")
            return [dict(zip(cols, r)) for r in cur.fetchall()]
        finally:
            con.close()

    def vessel_tracks(self, mmsi):
        """[(voyage_id, [(lon,lat),...]), ...] for every voyage of one vessel —
        drives 'show all tracks for this vessel'."""
        out = []
        for v in self.list_voyages(mmsi):
            coords = self.voyage_track(v["voyage_id"])
            if coords and len(coords) >= 2:
                out.append((v["voyage_id"], coords))
        return out

    def _voyage_ids_in_bbox(self, con, bbox):
        """voyage_ids whose stored geometry crosses bbox=(minx,miny,maxx,maxy)."""
        minx, miny, maxx, maxy = bbox
        ids = set()
        if self._pg:
            env = "ST_MakeEnvelope(%s,%s,%s,%s,4326)"
            cur = con.cursor()
            cur.execute("SELECT DISTINCT voyage_id FROM %s WHERE geom && %s"  # nosec B608
                        % (self._t("fixes"), env), (minx, miny, maxx, maxy))
            ids.update(r[0] for r in cur.fetchall())
            cur.execute("SELECT voyage_id FROM %s WHERE geom IS NOT NULL AND "  # nosec B608
                        "geom && %s" % (self._t("voyages"), env),
                        (minx, miny, maxx, maxy))
            ids.update(r[0] for r in cur.fetchall())
        else:
            for tbl in ("fixes", "voyages"):
                for vid, blob in con.execute(
                        "SELECT voyage_id, geom FROM %s WHERE geom IS NOT NULL"  # nosec B608
                        % tbl):
                    e = _blob_envelope(blob)
                    if e and not (e[2] < minx or e[0] > maxx
                                  or e[3] < miny or e[1] > maxy):
                        ids.add(vid)
        return ids

    # ---- helpers --------------------------------------------------------- #
    def _points(self, con, col, val):
        ph = "%s" if self._pg else "?"
        if self._pg:
            sql = ("SELECT ST_X(geom), ST_Y(geom) FROM %s WHERE %s=%s ORDER BY t"
                   % (self._t("fixes"), col, ph))                    # nosec B608
            cur = con.cursor()
            cur.execute(sql, (str(val),))
            return [(float(x), float(y)) for x, y in cur.fetchall()]
        sql = "SELECT geom FROM fixes WHERE %s=? ORDER BY t" % col   # nosec B608
        return self._decode_points(con.execute(sql, (str(val),)))

    def _stored_line(self, con, col, val):
        ph = "%s" if self._pg else "?"
        if self._pg:
            sql = ("SELECT ST_AsText(geom) FROM %s WHERE %s=%s"
                   % (self._t("voyages"), col, ph))                  # nosec B608
            cur = con.cursor()
            cur.execute(sql, (str(val),))
            row = cur.fetchone()
            return _parse_wkt_line(row[0]) if row and row[0] else []
        sql = "SELECT geom FROM voyages WHERE %s=?" % col            # nosec B608
        row = con.execute(sql, (str(val),)).fetchone()
        return self._decode_line(row[0]) if row and row[0] else []

    def _voyage_line_at(self, con, mmsi, t0):
        """LineString of the voyage of `mmsi` that spans t0 (stage-2 fallback)."""
        ph = "%s" if self._pg else "?"
        if self._pg:
            cond = ("mmsi=%s AND start_t<=to_timestamp(%s) AND "
                    "(end_t IS NULL OR end_t>=to_timestamp(%s))" % (ph, ph, ph))
            sql = ("SELECT ST_AsText(geom) FROM %s WHERE %s ORDER BY start_t DESC "
                   "LIMIT 1" % (self._t("voyages"), cond))           # nosec B608
            cur = con.cursor()
            cur.execute(sql, (str(mmsi), t0, t0))
            row = cur.fetchone()
            return _parse_wkt_line(row[0]) if row and row[0] else []
        cond = "mmsi=? AND start_t<=? AND (end_t IS NULL OR end_t>=?)"
        sql = ("SELECT geom FROM voyages WHERE %s ORDER BY start_t DESC LIMIT 1"
               % cond)                                               # nosec B608
        row = con.execute(sql, (str(mmsi), t0, t0)).fetchone()
        return self._decode_line(row[0]) if row and row[0] else []

    @staticmethod
    def _decode_points(rows):
        from .fairway_network import _parse_gpkg_geom
        out = []
        for (blob,) in rows:
            if not blob:
                continue
            try:
                kind, coords = _parse_gpkg_geom(blob)
                if kind == "Point":
                    out.append(coords)
            except Exception:                          # noqa: BLE001  # nosec B110
                pass
        return out

    @staticmethod
    def _decode_line(blob):
        from .fairway_network import _parse_gpkg_geom
        try:
            kind, coords = _parse_gpkg_geom(blob)
            if kind == "LineString":
                return list(coords)
            if kind == "MultiLineString":
                out = []
                for part in coords:
                    out.extend(part)
                return out
        except Exception:                              # noqa: BLE001  # nosec B110
            pass
        return []


# --------------------------------------------------------------------------- #
#  Project-layer builder (QGIS)
# --------------------------------------------------------------------------- #
_TRACK_GROUP = "AIS history"


def add_track_layer(name, coords, color="255,0,200,255"):
    """Add a styled in-memory LineString layer of `coords` [(lon, lat), ...] to
    the project (under an 'AIS history' group) and return it, or None. Needs 2+
    points. Best-effort; a styling hiccup never aborts the add."""
    if not coords or len(coords) < 2:
        return None
    from qgis.core import (QgsVectorLayer, QgsFeature, QgsGeometry, QgsPointXY,
                           QgsProject)
    layer = QgsVectorLayer("LineString?crs=EPSG:4326", name, "memory")
    if not layer.isValid():
        return None
    feat = QgsFeature()
    feat.setGeometry(QgsGeometry.fromPolylineXY(
        [QgsPointXY(x, y) for x, y in coords]))
    layer.dataProvider().addFeatures([feat])
    layer.updateExtents()
    try:
        from qgis.core import QgsLineSymbol, QgsSingleSymbolRenderer
        sym = QgsLineSymbol.createSimple({"line_color": color, "line_width": "0.8"})
        try:                                    # an arrow head shows the direction
            from qgis.core import (QgsMarkerLineSymbolLayer, QgsMarkerSymbol,
                                   QgsUnitTypes)
            ml = QgsMarkerLineSymbolLayer()
            ml.setPlacement(QgsMarkerLineSymbolLayer.CentralPoint)
            head = QgsMarkerSymbol.createSimple({
                "name": "arrowhead", "color": color, "size": "3",
                "angle": "0"})
            ml.setSubSymbol(head)
            sym.appendSymbolLayer(ml)
        except Exception:  # nosec B110
            pass
        layer.setRenderer(QgsSingleSymbolRenderer(sym))
    except Exception:  # nosec B110
        pass

    proj = QgsProject.instance()
    try:
        root = proj.layerTreeRoot()
        grp = root.findGroup(_TRACK_GROUP) or root.insertGroup(0, _TRACK_GROUP)
        proj.addMapLayer(layer, False)
        grp.insertLayer(0, layer)
    except Exception:
        proj.addMapLayer(layer)
    return layer


def clear_track_layers():
    """Remove every track layer drawn under the 'AIS history' group (and the now
    empty group). Returns the number of layers removed. Best-effort."""
    try:
        from qgis.core import QgsProject
    except Exception:                               # noqa: BLE001
        return 0
    proj = QgsProject.instance()
    root = proj.layerTreeRoot()
    grp = root.findGroup(_TRACK_GROUP)
    if grp is None:
        return 0
    ids = [n.layerId() for n in grp.findLayers()]
    for lid in ids:
        try:
            proj.removeMapLayer(lid)
        except Exception:                           # noqa: BLE001  # nosec B110
            pass
    try:
        root.removeChildNode(grp)
    except Exception:                               # noqa: BLE001  # nosec B110
        pass
    return len(ids)
