-- newsfeed initial schema (SQLite).
--
-- Conventions for this datastore:
--   * ids            TEXT  — hyphenated UUIDv4
--   * timestamps     TEXT  — RFC3339 / ISO8601 (UTC)
--   * booleans       INTEGER — 0/1
--   * json blobs     TEXT  — serde_json arrays/objects
--
-- Every user-owned row carries user_id and cascades on user deletion, because each
-- user is wholly in control of — and isolated within — their own feed.

CREATE TABLE users (
    id            TEXT PRIMARY KEY,
    username      TEXT NOT NULL UNIQUE,
    email         TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    created_at    TEXT NOT NULL
);

CREATE TABLE sessions (
    id         TEXT PRIMARY KEY,
    user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash TEXT NOT NULL UNIQUE,
    created_at TEXT NOT NULL,
    expires_at TEXT NOT NULL
);
CREATE INDEX idx_sessions_user ON sessions(user_id);

CREATE TABLE api_tokens (
    id           TEXT PRIMARY KEY,
    user_id      TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name         TEXT NOT NULL,
    prefix       TEXT NOT NULL,
    token_hash   TEXT NOT NULL UNIQUE,
    created_at   TEXT NOT NULL,
    last_used_at TEXT,
    revoked_at   TEXT
);
CREATE INDEX idx_api_tokens_user ON api_tokens(user_id);

CREATE TABLE sources (
    id             TEXT PRIMARY KEY,
    user_id        TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    kind           TEXT NOT NULL,               -- 'rss' | 'agentic'
    name           TEXT NOT NULL,
    url            TEXT,
    weight         REAL NOT NULL DEFAULT 1.0,
    enabled        INTEGER NOT NULL DEFAULT 1,
    last_polled_at TEXT,
    created_at     TEXT NOT NULL,
    UNIQUE(user_id, name)
);
CREATE INDEX idx_sources_user ON sources(user_id);

CREATE TABLE interests (
    id         TEXT PRIMARY KEY,
    user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    label      TEXT NOT NULL,
    weight     REAL NOT NULL,
    created_at TEXT NOT NULL,
    UNIQUE(user_id, label)
);
CREATE INDEX idx_interests_user ON interests(user_id);

CREATE TABLE items (
    id           TEXT PRIMARY KEY,
    user_id      TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    source_id    TEXT REFERENCES sources(id) ON DELETE SET NULL,
    external_id  TEXT NOT NULL,
    url          TEXT,
    title        TEXT NOT NULL,
    summary      TEXT,
    author       TEXT,
    tags         TEXT NOT NULL DEFAULT '[]',   -- json array of strings
    media        TEXT NOT NULL DEFAULT '[]',   -- json array of Media
    published_at TEXT,
    ingested_at  TEXT NOT NULL,
    state        TEXT NOT NULL DEFAULT 'candidate',
    score        REAL NOT NULL DEFAULT 0.0,
    UNIQUE(user_id, external_id)
);
-- Feed reads order by score within a user's live items.
CREATE INDEX idx_items_feed ON items(user_id, state, score DESC, id DESC);

CREATE TABLE signals (
    id         TEXT PRIMARY KEY,
    user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    item_id    TEXT NOT NULL REFERENCES items(id) ON DELETE CASCADE,
    action     TEXT NOT NULL,                  -- 'view' | 'click' | 'save' | 'dismiss'
    created_at TEXT NOT NULL
);
CREATE INDEX idx_signals_item ON signals(item_id);