-- Mnemosyne schema
-- Apply once; all statements use IF NOT EXISTS so this is safe to re-run.

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS tasks (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    title        TEXT    NOT NULL,
    notes        TEXT,
    class        TEXT    NOT NULL
                         CHECK(class IN ('monthly_date','monthly_weekday',
                                         'every_n_period','interval','floating')),
    active       INTEGER NOT NULL DEFAULT 1,

    -- monthly_date: fires on this day of every month.
    -- Short-month rule: if day_of_month > last day of month, fire on last day.
    day_of_month   INTEGER,

    -- monthly_weekday: fires on the Nth weekday of every month.
    -- weekday: 0=Monday .. 6=Sunday (ISO weekday - 1)
    -- ordinal:  1-4 for first-fourth; -1 for "last"
    weekday        INTEGER,
    ordinal        INTEGER,

    -- every_n_period: fires every interval_n period_units after anchor_date.
    -- Occurrences are calendar-anchored (not completion-driven).
    interval_n     INTEGER,
    period_unit    TEXT    CHECK(period_unit IN ('day','week','month') OR period_unit IS NULL),
    anchor_date    TEXT,   -- ISO date YYYY-MM-DD

    -- interval: next due = last_completed_at + interval_days.
    -- If never completed, seeds from created_at (so it doesn't scream overdue on day one).
    interval_days  INTEGER,

    -- floating: no fixed date; reminder frequency driven by priority.
    -- high   → appears every day
    -- medium → appears every medium_float_days (see config table, default 3)
    -- low    → appears roughly weekly; which low items show is randomised/rotated
    -- Completing a floating task sets active=0 (archives it), since floating items
    -- are typically one-and-done aspirations.
    priority       TEXT    CHECK(priority IN ('high','medium','low') OR priority IS NULL),

    created_at   TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
    updated_at   TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE TABLE IF NOT EXISTS completions (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    task_id      INTEGER NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    completed_at TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE INDEX IF NOT EXISTS idx_completions_task_id ON completions(task_id);

-- Single-row key/value store for runtime-tunable settings.
-- Populated with defaults below; /settime and future commands update rows here.
CREATE TABLE IF NOT EXISTS config (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

INSERT OR IGNORE INTO config (key, value) VALUES
    ('digest_time',       '06:30'),   -- local HH:MM, adjustable via /settime
    ('timezone',          'UTC'),     -- overridden by mnemosyne.conf at startup
    ('last_digest_sent',  ''),        -- ISO date of last successful digest; guards against dupes
    ('upcoming_horizon',  '7'),       -- days forward shown in Upcoming section
    ('medium_float_days', '3');       -- how often medium-priority floating items surface