-- Mood archive — SQLite schema.
--
-- mood.alogins.net (container `moodtracker`, /home/alvis/moodtracker) is a small
-- self-built Flask app with its own SQLite DB. It has no token-based API — its
-- only auth is a session-cookie login (/login with AUTH_USER/AUTH_PASS) guarding
-- /api/log, /api/history, /api/entry/<id>. Its DB file, however, is directly
-- readable on the host (world-readable, 644) at
-- /home/alvis/moodtracker/data/mood.db. This archiver reads that file straight
-- (via a read-only bind mount) instead of scraping the HTTP session API — no
-- credential handling needed, and it is immune to any future change in the
-- moodtracker app's auth scheme.
--
-- Why SQLite (not InfluxDB): single-user, few-entries-per-day mood logging is
-- tiny volume; Agap storage doctrine is SQLite-first (see googlefit/schema.sql
-- for the same reasoning). Mirrors that service's shape: idempotent upserts,
-- a sync cursor, an ingest-run audit log.

PRAGMA journal_mode = WAL;

-- One row per moodtracker entry. PK is the *source* row id (moodtracker's own
-- autoincrement id) + source name, so re-syncing never duplicates and a future
-- second mood source (were one ever added) can't collide ids with this one.
CREATE TABLE IF NOT EXISTS mood_entries (
    source          TEXT    NOT NULL DEFAULT 'moodtracker',
    source_id       INTEGER NOT NULL,          -- moodtracker entries.id
    ts              TEXT    NOT NULL,          -- ISO8601 UTC, as recorded by moodtracker
    mood            INTEGER NOT NULL,          -- 1-5 scale used by moodtracker
    tags            TEXT    NOT NULL DEFAULT '[]', -- JSON array of tag strings
    note            TEXT,
    affirmation     TEXT,
    ingested_at     TEXT    NOT NULL,
    PRIMARY KEY (source, source_id)
);
CREATE INDEX IF NOT EXISTS idx_mood_ts ON mood_entries (ts);
CREATE INDEX IF NOT EXISTS idx_mood_mood ON mood_entries (mood);

-- Incremental-sync cursor per stream (one stream today: 'moodtracker_entries').
-- last_synced_id is the high-water mark on source_id; each run re-checks a
-- small overlap of already-synced ids too (cheap, guards against any future
-- edit capability moodtracker doesn't have today).
CREATE TABLE IF NOT EXISTS sync_state (
    stream_key      TEXT PRIMARY KEY,
    last_synced_id  INTEGER NOT NULL DEFAULT 0,
    last_run_at     TEXT,
    last_status     TEXT,                      -- ok | error
    last_error      TEXT
);

-- Audit log of ingestion runs (observability; Zabbix can read staleness later).
CREATE TABLE IF NOT EXISTS ingest_runs (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    started_at      TEXT NOT NULL,
    finished_at     TEXT,
    status          TEXT,                      -- ok | error
    entries_upserted INTEGER DEFAULT 0,
    error           TEXT
);