-- ============================================================
--  e-management history database  (SQLite)
--  Model: star schema — one dimension (points) + one fact
--  table (readings) as a long/narrow time-series, plus a
--  poll log (snapshots) and a site lookup. Charting is done
--  off the views at the bottom.
-- ============================================================
PRAGMA journal_mode = WAL;     -- concurrent readers while the poller writes
PRAGMA foreign_keys = ON;

-- ---- dimension: every distinct measurement point --------------------------
CREATE TABLE IF NOT EXISTS points (
    point_id   INTEGER PRIMARY KEY,
    key        TEXT    NOT NULL UNIQUE,     -- 'l1.ch1004.r0'
    layer      INTEGER NOT NULL,            -- 1 | 2
    channel    INTEGER NOT NULL,            -- 1004
    register   INTEGER NOT NULL,            -- 0
    zdroj      TEXT,                        -- API key '1;1004;0;0'
    sid        INTEGER,                     -- station id
    name       TEXT,                        -- popis-cze, e.g. '01_ee_KGJ1_vykon_procenta'
    unit       TEXT,                        -- jednotky
    mult       REAL    DEFAULT 1,           -- nasobitel (value = raw * mult)
    decimals   INTEGER DEFAULT 0,           -- poc-dm
    typ        TEXT,                        -- analog | binar | textbit
    site_code  TEXT,                        -- '01_ee'  (derived from name)
    device     TEXT,                        -- 'KGJ1'   (derived from name)
    metric     TEXT,                        -- 'vykon_procenta' (derived from name)
    created_at TEXT    DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS ix_points_site   ON points(site_code);
CREATE INDEX IF NOT EXISTS ix_points_device ON points(device);

-- ---- site / company lookup ------------------------------------------------
CREATE TABLE IF NOT EXISTS sites (
    site_code TEXT PRIMARY KEY,
    company   TEXT,
    location  TEXT
);

-- ---- fact: raw readings, one row per (point, server measurement time) ------
--  Dedup key (point_id, ts): the server timestamps each value with its true
--  measurement time, so re-polling the same value never creates a duplicate.
CREATE TABLE IF NOT EXISTS readings (
    point_id INTEGER NOT NULL REFERENCES points(point_id),
    ts       TEXT    NOT NULL,              -- server time 'YYYY-MM-DD HH:MM:SS'
    raw      REAL    NOT NULL,
    PRIMARY KEY (point_id, ts)
) WITHOUT ROWID;
CREATE INDEX IF NOT EXISTS ix_readings_ts ON readings(ts);

-- ---- poll log (provenance / collection audit) -----------------------------
CREATE TABLE IF NOT EXISTS snapshots (
    snapshot_id INTEGER PRIMARY KEY,
    fetched_at  TEXT DEFAULT (datetime('now')),  -- local wall-clock of the fetch
    server_date TEXT,                             -- r.date from the response
    source      TEXT,                             -- 'bat' | 'realtime' | 'baseline' | 'import'
    n_new       INTEGER                           -- new readings inserted this poll
);

-- ============================================================
--  Views for charting / dashboards
-- ============================================================

-- every reading, joined to metadata, with scaled value + labels
CREATE VIEW IF NOT EXISTS v_readings AS
SELECT r.point_id, p.key, p.name, p.unit, p.typ,
       p.site_code, p.device, p.metric,
       r.ts,
       CAST(strftime('%s', r.ts) AS INTEGER) AS ts_epoch,
       r.raw,
       r.raw * COALESCE(p.mult, 1)           AS value
FROM readings r
JOIN points p USING (point_id);

-- latest value per point (for a live snapshot / current dashboard)
CREATE VIEW IF NOT EXISTS v_latest AS
SELECT p.point_id, p.key, p.name, p.unit, p.typ,
       p.site_code, p.device, p.metric, p.sid,
       last.ts,
       last.raw,
       last.raw * COALESCE(p.mult, 1) AS value
FROM points p
JOIN (
    SELECT point_id, ts, raw
    FROM readings r1
    WHERE ts = (SELECT MAX(ts) FROM readings r2 WHERE r2.point_id = r1.point_id)
) last ON last.point_id = p.point_id;

-- named points only (drops the 11 unlabeled status/config registers)
CREATE VIEW IF NOT EXISTS v_named AS
SELECT * FROM v_readings WHERE name IS NOT NULL AND name <> '';

-- hourly average per point (handy for long-range charts)
CREATE VIEW IF NOT EXISTS v_hourly AS
SELECT point_id,
       strftime('%Y-%m-%d %H:00:00', ts) AS hour,
       AVG(raw) AS raw_avg,
       MIN(raw) AS raw_min,
       MAX(raw) AS raw_max,
       COUNT(*) AS n
FROM readings
GROUP BY point_id, hour;
