-- Central verification DB (Phase 1: dashboard pipeline).
-- Ingest reads a folder of videos + sibling export-JSONs and fills these tables.

CREATE TABLE IF NOT EXISTS projects (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL UNIQUE,
    source_path TEXT NOT NULL,              -- local folder now; mounted NAS share on the VM
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS videos (
    id                 SERIAL PRIMARY KEY,
    project_id         INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    file_name          TEXT NOT NULL,
    rel_path           TEXT NOT NULL,
    has_json           BOOLEAN NOT NULL DEFAULT FALSE,
    width              INTEGER,
    height             INTEGER,
    fps                DOUBLE PRECISION,
    frame_count        INTEGER,
    annotation_count   INTEGER NOT NULL DEFAULT 0,
    annotation_time_ms BIGINT  NOT NULL DEFAULT 0,
    primary_annotator  TEXT NOT NULL DEFAULT '',
    -- 'pending'  = no JSON yet (not annotated)
    -- 'annotated'= has annotations from the NAS JSON (awaiting verification)
    -- 'verified' = a worker pushed a verified pass (Phase 3)
    status             TEXT NOT NULL DEFAULT 'pending',
    annotated_at       TIMESTAMPTZ,          -- max(annotation.created_at), used for throughput
    raw_json           JSONB,                -- full imported export doc (for Phase 3 pull)
    ingested_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (project_id, rel_path)
);

CREATE INDEX IF NOT EXISTS idx_videos_project ON videos(project_id);
CREATE INDEX IF NOT EXISTS idx_videos_status  ON videos(project_id, status);

CREATE TABLE IF NOT EXISTS annotations (
    id            SERIAL PRIMARY KEY,
    video_id      INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
    frame_number  INTEGER NOT NULL,
    label         TEXT NOT NULL,
    side          TEXT NOT NULL DEFAULT '',
    shape_type    TEXT NOT NULL DEFAULT 'bbox',
    vertices      JSONB,
    annotated_by  TEXT NOT NULL DEFAULT '',
    review_status TEXT NOT NULL DEFAULT 'none',
    remark        TEXT NOT NULL DEFAULT '',
    subclass      TEXT NOT NULL DEFAULT '',
    created_at    TEXT NOT NULL DEFAULT ''
);

CREATE INDEX IF NOT EXISTS idx_annotations_video ON annotations(video_id);
CREATE INDEX IF NOT EXISTS idx_annotations_user  ON annotations(annotated_by);

-- ---------------------------------------------------------------------------
-- Phase 3: worker pull/push (claim → verify → push) + token auth.
-- These run on every startup; ALTER ... IF NOT EXISTS keeps them idempotent and
-- migrates existing Postgres volumes without a separate migration step.
-- ---------------------------------------------------------------------------

-- Provisioned workers/admins. Tokens are never stored in clear: token_hash is
-- the lowercase hex SHA-256 of the issued token. Admin provisions these.
CREATE TABLE IF NOT EXISTS users (
    id           SERIAL PRIMARY KEY,
    username     TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL DEFAULT '',
    token_hash   TEXT NOT NULL,
    role         TEXT NOT NULL DEFAULT 'ml_support',   -- 'ml_support' | 'admin'
    active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_users_token ON users(token_hash);

-- Roles were renamed worker → ml_support (only two roles now: ml_support | admin).
-- Idempotent: migrate existing rows + the column default on already-provisioned DBs.
UPDATE users SET role='ml_support' WHERE role='worker';
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'ml_support';

-- Claiming is orthogonal to the annotation `status` above: a worker claims an
-- 'annotated' video (exclusive lease), verifies it, then pushes → 'verified'.
ALTER TABLE videos ADD COLUMN IF NOT EXISTS claimed_by       TEXT;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS claimed_at       TIMESTAMPTZ;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS lease_expires_at TIMESTAMPTZ;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS completed_by     TEXT;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS completed_at     TIMESTAMPTZ;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS verify_time_ms   BIGINT NOT NULL DEFAULT 0;

-- A video is claimable when annotated and either unclaimed or its lease lapsed.
CREATE INDEX IF NOT EXISTS idx_videos_claim ON videos(project_id, claimed_by, lease_expires_at);

-- Imported baseline: how many annotations the video started a pass with (NAS-ingested
-- count, or the worker's claim-time count on push). vs annotation_count it reveals how
-- many the worker added/deleted during verification.
ALTER TABLE videos ADD COLUMN IF NOT EXISTS imported_count INTEGER NOT NULL DEFAULT 0;

-- "Raise hand" signal: anyone (ml_support or admin) can raise a hand on a video to
-- start a conversation; anyone can lower it. Orthogonal to the remark thread.
ALTER TABLE videos ADD COLUMN IF NOT EXISTS hand_raised    BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS hand_raised_by TEXT;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS hand_raised_at TIMESTAMPTZ;

-- "Ignore" flag: admins can mark a video as ignored (excluded from the workflow);
-- the dashboard/desktop cross out the row. Admin-only to set.
ALTER TABLE videos ADD COLUMN IF NOT EXISTS ignored    BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS ignored_by TEXT;

-- Admin assignment: which user a video is assigned to (advisory owner). Orthogonal to
-- claim/status; drives the 'assigned' workflow state until someone claims it.
ALTER TABLE videos ADD COLUMN IF NOT EXISTS assigned_to TEXT;
CREATE INDEX IF NOT EXISTS idx_videos_assigned ON videos(project_id, assigned_to);

-- Audit + analytics: every claim/release/push/auto_release/ingest.
CREATE TABLE IF NOT EXISTS video_events (
    id        SERIAL PRIMARY KEY,
    video_id  INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
    username  TEXT NOT NULL DEFAULT '',
    event     TEXT NOT NULL,   -- 'claim'|'release'|'push'|'auto_release'|'reopen'
    at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    meta      JSONB NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX IF NOT EXISTS idx_events_video ON video_events(video_id);
CREATE INDEX IF NOT EXISTS idx_events_at    ON video_events(at);

-- ---------------------------------------------------------------------------
-- Clients own projects (one client → many projects). Admins create these from
-- the dashboard; each project points at a NAS folder (`source_path`).
-- Idempotent: safe to re-run on every startup.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS clients (
    id         SERIAL PRIMARY KEY,
    name       TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- projects gains a client owner; name becomes unique PER client
-- (Microsoft/India and Google/India can both exist).
ALTER TABLE projects ADD COLUMN IF NOT EXISTS client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE;

-- One-time backfill: only if a client-less project exists, park it under a 'default' client.
-- Guarded so a deleted 'default' is NOT resurrected on every boot.
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM projects WHERE client_id IS NULL) THEN
    INSERT INTO clients (name) VALUES ('default') ON CONFLICT (name) DO NOTHING;
    UPDATE projects SET client_id = (SELECT id FROM clients WHERE name='default') WHERE client_id IS NULL;
  END IF;
END $$;

-- Swap the old global UNIQUE(name) for UNIQUE(client_id, name). DROP IF EXISTS is idempotent;
-- ADD CONSTRAINT is guarded by a DO-block (Postgres has no ADD CONSTRAINT IF NOT EXISTS).
ALTER TABLE projects DROP CONSTRAINT IF EXISTS projects_name_key;
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname='projects_client_name_key') THEN
    ALTER TABLE projects ADD CONSTRAINT projects_client_name_key UNIQUE (client_id, name);
  END IF;
END $$;
CREATE INDEX IF NOT EXISTS idx_projects_client ON projects(client_id);

-- How to reach a project's source folder. 'local' = scan source_path directly (a path inside the
-- container, incl. compose-managed NFS volumes); 'nfs' = mount NFS_HOST:source_path on demand
-- (the NAS host/IP lives only in the NFS_HOST env, never here).
ALTER TABLE projects ADD COLUMN IF NOT EXISTS source_kind TEXT NOT NULL DEFAULT 'local';

-- ---------------------------------------------------------------------------
-- Audit log of org-level actions (client/project created, sync dir changed,
-- synced, user created) with timestamp + actor.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_events (
    id       SERIAL PRIMARY KEY,
    at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    username TEXT NOT NULL DEFAULT '',
    action   TEXT NOT NULL,   -- 'client_created'|'project_created'|'project_source_changed'|'synced'|'user_created'
    detail   JSONB NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX IF NOT EXISTS idx_audit_at ON audit_events(at);

-- ---------------------------------------------------------------------------
-- Per-video remark thread. Admins and workers both append; everyone sees the
-- same thread (synced). Each line is attributed to its author + timestamp.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS video_remarks (
    id         SERIAL PRIMARY KEY,
    video_id   INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
    username   TEXT NOT NULL,
    body       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_video_remarks_video ON video_remarks(video_id, created_at);

-- ---------------------------------------------------------------------------
-- Project membership: which users are picked for a project. Admins manage this.
-- Non-admins only see projects they're a member of; videos can only be assigned
-- to members.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS project_members (
    project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    username   TEXT NOT NULL,
    added_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (project_id, username)
);
CREATE INDEX IF NOT EXISTS idx_project_members_user ON project_members(username);

-- ---------------------------------------------------------------------------
-- Project timer: a stopwatch for "active project time". Admins start/stop/reset it;
-- a push (verify/re-verify) auto-resumes it; 72h of inactivity auto-stops it (idle
-- time isn't counted). elapsed = accumulated + (running ? now - started_at : 0).
-- ---------------------------------------------------------------------------
ALTER TABLE projects ADD COLUMN IF NOT EXISTS time_running        BOOLEAN NOT NULL DEFAULT TRUE;
ALTER TABLE projects ADD COLUMN IF NOT EXISTS time_accumulated_ms BIGINT  NOT NULL DEFAULT 0;
ALTER TABLE projects ADD COLUMN IF NOT EXISTS time_started_at     TIMESTAMPTZ;
ALTER TABLE projects ADD COLUMN IF NOT EXISTS last_activity_at    TIMESTAMPTZ;
-- Backfill: running projects count from their creation; seed last activity.
UPDATE projects SET time_started_at = created_at  WHERE time_running AND time_started_at IS NULL;
UPDATE projects SET last_activity_at = created_at WHERE last_activity_at IS NULL;

-- ---------------------------------------------------------------------------
-- Folders to consider for verification. When a project has ANY rows here, only
-- videos in those folders are shown/counted; empty = consider all folders.
-- Admins add/modify/delete the set.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS project_folders (
    project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    folder     TEXT NOT NULL,   -- directory part of rel_path ('' = project root)
    PRIMARY KEY (project_id, folder)
);

-- ---------------------------------------------------------------------------
-- GPS pipeline (map view + chainage). A background sweep extracts each video's
-- GPS track with exiftool (dashcam Doc{N} samples), one video at a time:
--   gps_status: 'pending' → 'ok' (track stored) | 'none' (no GPS metadata)
--                        | 'error' (exiftool/mount failure, see gps_error)
-- The RAW track is immutable once scanned; a kept "Viterbi" correction goes to
-- gps_track_corrected (map/chainage prefer it, raw is never modified).
-- Track format: [[lat, lon, t_s], ...] (t_s = seconds from first sample).
-- ---------------------------------------------------------------------------
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_status     TEXT NOT NULL DEFAULT 'pending';
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_track      JSONB;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_len_m      DOUBLE PRECISION;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_error      TEXT;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_scanned_at TIMESTAMPTZ;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_track_corrected JSONB;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_corrected_by    TEXT;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_corrected_at    TIMESTAMPTZ;
-- Human-readable summary of what the correction did ("2 spikes · start stitched …").
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_corrected_note  TEXT;
-- Data-quality + continuity metadata from the raw scan: absolute epoch of the
-- first/last GPS fix (lets consecutive files stitch across a missing start),
-- raw sample count and the largest inter-fix gap (dropout visibility).
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_start_epoch  DOUBLE PRECISION;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_end_epoch    DOUBLE PRECISION;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_sample_count INTEGER;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_max_gap_s    DOUBLE PRECISION;
-- Real media duration read from the container by the exiftool scan. Load-bearing for
-- the stitch guard: an inter-file gap is only "missing start" when the track covers
-- LESS than the file's duration — otherwise the camera simply wasn't recording.
ALTER TABLE videos ADD COLUMN IF NOT EXISTS duration_s DOUBLE PRECISION;
CREATE INDEX IF NOT EXISTS idx_videos_gps ON videos(gps_status) WHERE gps_status = 'pending';

-- ---------------------------------------------------------------------------
-- Road types: admin-defined per-project labels (LHS/RHS/MCW/Servicelane, …)
-- assigned to videos. Injected into the export/claim JSON as `road_type` and
-- re-stamped on push (server-authoritative).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS project_road_types (
    project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    name       TEXT NOT NULL,
    PRIMARY KEY (project_id, name)
);
ALTER TABLE videos ADD COLUMN IF NOT EXISTS road_type TEXT NOT NULL DEFAULT '';

-- ---------------------------------------------------------------------------
-- Chainage (linear referencing with piecewise-linear calibration).
-- chainage_points = admin-entered client calibration pairs (lat/lon ↔ chainage).
-- Optional road_type tag scopes a point to one calibration group (LHS vs RHS are
-- separate routes); '' = usable by any group.
-- Per video:
--   sr_chainage_*  = ACTUAL measured position along the GPS route (immutable,
--                    regenerated only from GPS — never admin-editable)
--   chainage_*     = client-calibrated values (error distributed between
--                    consecutive control points)
--   route_seq      = position of the video along the calibrated route
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS chainage_points (
    id         SERIAL PRIMARY KEY,
    project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    lat        DOUBLE PRECISION NOT NULL,
    lon        DOUBLE PRECISION NOT NULL,
    chainage_m DOUBLE PRECISION NOT NULL,
    note       TEXT NOT NULL DEFAULT '',
    road_type  TEXT NOT NULL DEFAULT '',
    created_by TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_chainage_points_project ON chainage_points(project_id);
-- origin marks generated points ('quick' = the two-point quick assign) so they can
-- be replaced on the next quick run regardless of the (freely editable) note text.
ALTER TABLE chainage_points ADD COLUMN IF NOT EXISTS origin TEXT NOT NULL DEFAULT '';
UPDATE chainage_points SET origin='quick' WHERE origin='' AND note LIKE 'quick:%';
-- The full result of the last recompute (route/spans/per-video/warnings JSON) so
-- any admin who signs in later sees the existing computed data, not a recompute ask.
ALTER TABLE chainage_calibrations ADD COLUMN IF NOT EXISTS summary JSONB;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS chainage_start_m    DOUBLE PRECISION;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS chainage_end_m      DOUBLE PRECISION;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS sr_chainage_start_m DOUBLE PRECISION;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS sr_chainage_end_m   DOUBLE PRECISION;
ALTER TABLE videos ADD COLUMN IF NOT EXISTS route_seq           INTEGER;

-- The piecewise-linear calibration itself (per project + group), persisted at
-- recompute time as sorted [s_m, chainage_m] anchors. Needed to evaluate chainage at
-- ANY route position later — e.g. frame-exact per-annotation chainage in exports.
CREATE TABLE IF NOT EXISTS chainage_calibrations (
    project_id  INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    road_type   TEXT NOT NULL DEFAULT '',
    anchors     JSONB NOT NULL,
    route_len_m DOUBLE PRECISION NOT NULL DEFAULT 0,
    computed_by TEXT NOT NULL DEFAULT '',
    computed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (project_id, road_type)
);