-- Google Fit local archive — SQLite schema (source-agnostic).
--
-- Design note: one normalized store serves BOTH ingestion paths — the Google Fit
-- REST API adapter and the Google Takeout importer (deprecation hedge, see README).
-- Time-series points, workout/sleep sessions, and sleep stages each get a table;
-- every write is an idempotent UPSERT keyed on the natural Fit identity of the row,
-- so re-running a sync over an overlapping window never duplicates data.
--
-- Why SQLite and not InfluxDB: single-user, daily-cadence health data is low volume
-- (thousands of rows/day at most); the Agap/OpenClaw storage doctrine is SQLite-only;
-- and a normalized relational store answers the "sessions + metadata + series" query
-- mix better than a pure TSDB would. Adding an always-on InfluxDB service would be
-- operational cost with no payoff at this scale. (Task listed both as options.)

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

-- Time-series metric points: steps, heart rate, calories, distance, weight, etc.
-- One row per (metric, interval, source). `metric` is our normalized name
-- (e.g. 'steps', 'heart_rate_avg'), decoupled from Google's data type strings.
CREATE TABLE IF NOT EXISTS data_points (
    metric          TEXT    NOT NULL,          -- normalized: steps, heart_rate_avg, calories, ...
    data_type_name  TEXT    NOT NULL,          -- raw Google Fit data type (provenance)
    start_ns        INTEGER NOT NULL,          -- interval start, epoch nanoseconds
    end_ns          INTEGER NOT NULL,          -- interval end, epoch nanoseconds
    start_time      TEXT    NOT NULL,          -- ISO8601 UTC (human/SQL friendly)
    end_time        TEXT    NOT NULL,
    value_int       INTEGER,                   -- populated for integer metrics
    value_float     REAL,                       -- populated for float metrics
    value_str       TEXT,                       -- populated for string/enum metrics
    unit            TEXT,                        -- count, bpm, kcal, m, kg, min, ...
    data_source_id  TEXT    NOT NULL DEFAULT '', -- originating Fit stream (may be '')
    source          TEXT    NOT NULL DEFAULT 'ha', -- ha | takeout
    ingested_at     TEXT    NOT NULL,
    PRIMARY KEY (metric, start_ns, end_ns, data_source_id)
);
CREATE INDEX IF NOT EXISTS idx_dp_metric_time ON data_points (metric, start_ns);
CREATE INDEX IF NOT EXISTS idx_dp_start_time  ON data_points (start_time);

-- Workouts / activities / sleep sessions (com.google.session).
CREATE TABLE IF NOT EXISTS sessions (
    id                TEXT PRIMARY KEY,        -- Fit session id (stable, upsert key)
    name              TEXT,
    description       TEXT,
    activity_type     INTEGER,                 -- Fit activity type enum
    activity_name     TEXT,                    -- resolved label (e.g. 'Running', 'Sleep')
    start_ns          INTEGER NOT NULL,
    end_ns            INTEGER NOT NULL,
    start_time        TEXT    NOT NULL,
    end_time          TEXT    NOT NULL,
    modified_ns       INTEGER,
    application       TEXT,                    -- packageName that wrote the session
    source            TEXT NOT NULL DEFAULT 'ha',
    raw_json          TEXT,                    -- full session payload for reprocessing
    ingested_at       TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sessions_start ON sessions (start_ns);
CREATE INDEX IF NOT EXISTS idx_sessions_type  ON sessions (activity_type);

-- Sleep stage segments (com.google.sleep.segment). Stage enum decoded to a label.
CREATE TABLE IF NOT EXISTS sleep_segments (
    start_ns        INTEGER NOT NULL,
    end_ns          INTEGER NOT NULL,
    start_time      TEXT    NOT NULL,
    end_time        TEXT    NOT NULL,
    stage           INTEGER NOT NULL,          -- raw Fit sleep-stage enum
    stage_name      TEXT    NOT NULL,          -- awake, light, deep, rem, out_of_bed, sleep
    data_source_id  TEXT    NOT NULL DEFAULT '',
    source          TEXT    NOT NULL DEFAULT 'ha',
    ingested_at     TEXT    NOT NULL,
    PRIMARY KEY (start_ns, end_ns, data_source_id)
);
CREATE INDEX IF NOT EXISTS idx_sleep_start ON sleep_segments (start_ns);

-- Incremental-sync cursor per stream. `last_synced_ns` is the high-water mark
-- (max end_ns fetched); the next run resumes from there minus a small overlap.
CREATE TABLE IF NOT EXISTS sync_state (
    stream_key      TEXT PRIMARY KEY,          -- metric name, 'sessions', or 'sleep'
    last_synced_ns  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 later read staleness).
CREATE TABLE IF NOT EXISTS ingest_runs (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    started_at      TEXT NOT NULL,
    finished_at     TEXT,
    status          TEXT,                      -- ok | error
    points_upserted INTEGER DEFAULT 0,
    sessions_upserted INTEGER DEFAULT 0,
    segments_upserted INTEGER DEFAULT 0,
    error           TEXT
);