-- Cockpit · NuvioTV panel — SQLite schema
-- Engine: SQLite 3 (WAL). All timestamps stored as unix epoch seconds (INTEGER).
--
-- Fresh installs get this schema directly. Existing databases are upgraded in
-- Database::migrate() (users-table rebuild + additive tables), so the CREATE
-- statements here all use IF NOT EXISTS and stay upgrade-safe.

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

-- ---------------------------------------------------------------------------
-- Panel operators (web admin login).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admins (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    username      TEXT    NOT NULL UNIQUE,
    password_hash TEXT    NOT NULL,
    totp_secret   TEXT,
    display_name  TEXT,
    is_active     INTEGER NOT NULL DEFAULT 1,
    last_login_at INTEGER,
    created_at    INTEGER NOT NULL,
    updated_at    INTEGER NOT NULL
);

-- ---------------------------------------------------------------------------
-- Portals = Xtream Codes servers the app can pick from (portals.php list).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS portals (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    name          TEXT    NOT NULL,
    url           TEXT    NOT NULL,
    is_active     INTEGER NOT NULL DEFAULT 1,
    is_default    INTEGER NOT NULL DEFAULT 0,
    sort_order    INTEGER NOT NULL DEFAULT 0,
    created_at    INTEGER NOT NULL,
    updated_at    INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_portals_active ON portals(is_active, sort_order);

-- ---------------------------------------------------------------------------
-- End-users. Two kinds, distinguished by auth_type:
--   'xtream' -> validated live against a portal's player_api.php (portal_id set)
--   'local'  -> username/password stored here (password_hash set, portal_id null)
-- id is stable/deterministic so a returning user keeps their config.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id              TEXT    PRIMARY KEY,
    auth_type       TEXT    NOT NULL DEFAULT 'xtream',   -- xtream | local
    username        TEXT    NOT NULL,
    password_hash   TEXT,                                 -- local only (Argon2id)
    portal_id       INTEGER REFERENCES portals(id) ON DELETE CASCADE,   -- xtream only
    display_name    TEXT,
    email           TEXT    NOT NULL,
    server_url      TEXT,
    status          TEXT    NOT NULL DEFAULT 'active',    -- active | disabled
    expires_at      INTEGER,                               -- account expiry (both kinds)
    is_seeded       INTEGER NOT NULL DEFAULT 0,
    last_login_at   INTEGER,
    last_device     TEXT,
    login_count     INTEGER NOT NULL DEFAULT 0,
    created_at      INTEGER NOT NULL,
    updated_at      INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_users_portal ON users(portal_id);
-- The auth_type/username partial unique indexes are created by the migrator
-- (Migrator::ensureUsersIndexes) after the users table is guaranteed to be in
-- its current shape, so applying this file over a pre-local database is safe.

-- ---------------------------------------------------------------------------
-- Per-user, per-profile addon list  (api/addons)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS addons (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id     TEXT    NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    profile_id  INTEGER NOT NULL DEFAULT 1,
    url         TEXT    NOT NULL,
    name        TEXT,
    enabled     INTEGER NOT NULL DEFAULT 1,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    created_at  INTEGER NOT NULL,
    updated_at  INTEGER NOT NULL,
    UNIQUE (user_id, profile_id, url)
);
CREATE INDEX IF NOT EXISTS idx_addons_lookup ON addons(user_id, profile_id, sort_order);

-- ---------------------------------------------------------------------------
-- Per-user, per-profile plugin repositories  (api/plugins)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS plugins (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id     TEXT    NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    profile_id  INTEGER NOT NULL DEFAULT 1,
    url         TEXT    NOT NULL,
    name        TEXT,
    enabled     INTEGER NOT NULL DEFAULT 1,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    repo_type   TEXT,
    created_at  INTEGER NOT NULL,
    updated_at  INTEGER NOT NULL,
    UNIQUE (user_id, profile_id, url)
);
CREATE INDEX IF NOT EXISTS idx_plugins_lookup ON plugins(user_id, profile_id, sort_order);

-- ---------------------------------------------------------------------------
-- GLOBAL catalog: applied to every active user, on every profile, merged in at
-- fetch time. A per-user row for the same URL overrides the global one.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS global_addons (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    url         TEXT    NOT NULL UNIQUE,
    name        TEXT,
    enabled     INTEGER NOT NULL DEFAULT 1,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    created_at  INTEGER NOT NULL,
    updated_at  INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS global_plugins (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    url         TEXT    NOT NULL UNIQUE,
    name        TEXT,
    enabled     INTEGER NOT NULL DEFAULT 1,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    repo_type   TEXT,
    created_at  INTEGER NOT NULL,
    updated_at  INTEGER NOT NULL
);

-- ---------------------------------------------------------------------------
-- Legacy per-user seed templates (superseded by the global catalog). Retained
-- so existing rows can be migrated into the global tables on upgrade.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS defaults (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    kind        TEXT    NOT NULL,
    url         TEXT    NOT NULL,
    name        TEXT,
    enabled     INTEGER NOT NULL DEFAULT 1,
    sort_order  INTEGER NOT NULL DEFAULT 0,
    repo_type   TEXT,
    created_at  INTEGER NOT NULL,
    updated_at  INTEGER NOT NULL,
    UNIQUE (kind, url)
);

-- ---------------------------------------------------------------------------
-- Login / auth audit trail.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS login_audit (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id      TEXT,
    username     TEXT,
    portal_id    INTEGER,
    ip           TEXT,
    device_name  TEXT,
    user_agent   TEXT,
    outcome      TEXT NOT NULL,      -- success | invalid | portal_down | disabled | expired | error
    detail       TEXT,
    created_at   INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_audit_time ON login_audit(created_at);
CREATE INDEX IF NOT EXISTS idx_audit_user ON login_audit(user_id, created_at);

-- ---------------------------------------------------------------------------
-- Key/value runtime settings.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    key         TEXT PRIMARY KEY,
    value       TEXT,
    updated_at  INTEGER NOT NULL
);