-- lib/schema.sql — Clean full schema (sports badges included)

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS admins (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    username      TEXT    NOT NULL UNIQUE,
    password_hash TEXT    NOT NULL,
    created_at    TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS devices (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    account_name  TEXT,
    mac           TEXT    NOT NULL UNIQUE,
    device_id     TEXT,
    model         TEXT,
    device_key    TEXT    UNIQUE,
    license_key   TEXT,
    registered    INTEGER NOT NULL DEFAULT 0,
    theme         INTEGER NOT NULL DEFAULT 0,
    login_theme   INTEGER NOT NULL DEFAULT 0,
    sub_trial     INTEGER NOT NULL DEFAULT 1,
    sub_expire    TEXT,
    plan_id       TEXT,
    price         TEXT,
    locked        INTEGER NOT NULL DEFAULT 0,
    parent_pin    TEXT    NOT NULL DEFAULT '0000',
    parent_synced INTEGER NOT NULL DEFAULT 1,
    login_title   TEXT,
    login_content TEXT,
    noti_title    TEXT,
    noti_content  TEXT,
    app_version   TEXT,
    last_seen     TEXT,
    created_at    TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS dns (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    title      TEXT    NOT NULL,
    url        TEXT    NOT NULL,
    created_at TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS vpn_servers (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    name          TEXT    NOT NULL,
    country       TEXT    NOT NULL DEFAULT '',
    flag          TEXT    NOT NULL DEFAULT '',
    host          TEXT    NOT NULL DEFAULT '',
    protocol      TEXT    NOT NULL DEFAULT 'udp',
    ovpn          TEXT    NOT NULL,
    requires_auth INTEGER NOT NULL DEFAULT 0,
    username      TEXT    NOT NULL DEFAULT '',
    password      TEXT    NOT NULL DEFAULT '',
    enabled       INTEGER NOT NULL DEFAULT 1,
    created_at    TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS playlists (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    device_id  INTEGER NOT NULL,
    name       TEXT    NOT NULL,
    type       TEXT    NOT NULL DEFAULT 'm3u',
    url        TEXT    NOT NULL,
    username   TEXT,
    password   TEXT,
    protected  INTEGER NOT NULL DEFAULT 0,
    sort       INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS credentials (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    username   TEXT    NOT NULL,
    password   TEXT    NOT NULL,
    dns_id     INTEGER,
    name       TEXT,
    protected  INTEGER NOT NULL DEFAULT 0,
    expire     TEXT,
    created_at TEXT    NOT NULL,
    FOREIGN KEY (dns_id) REFERENCES dns(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS activation_codes (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    code       TEXT    NOT NULL UNIQUE,
    username   TEXT,
    password   TEXT,
    dns_id     INTEGER,
    name       TEXT,
    protected  INTEGER NOT NULL DEFAULT 0,
    permanent  INTEGER NOT NULL DEFAULT 0,
    expire     TEXT,
    use_count  INTEGER NOT NULL DEFAULT 0,
    device_id  INTEGER,
    created_at TEXT    NOT NULL,
    used_at    TEXT,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE SET NULL,
    FOREIGN KEY (dns_id)    REFERENCES dns(id)     ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS settings (
    key   TEXT PRIMARY KEY,
    value TEXT
);

CREATE TABLE IF NOT EXISTS languages (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    code    TEXT    NOT NULL UNIQUE,
    name    TEXT    NOT NULL,
    enabled INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS menu_ads (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    title      TEXT    NOT NULL,
    url        TEXT    NOT NULL,
    sort       INTEGER NOT NULL DEFAULT 0,
    created_at TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS leagues (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT    NOT NULL,
    league_id  TEXT,
    created_at TEXT    NOT NULL,
    sport_type TEXT    NOT NULL DEFAULT 'soccer'
);

CREATE TABLE IF NOT EXISTS sport_events (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    league       TEXT    NOT NULL DEFAULT '',
    home         TEXT    NOT NULL DEFAULT '',
    away         TEXT    NOT NULL DEFAULT '',
    event_date   TEXT    NOT NULL DEFAULT '',
    event_time   TEXT    NOT NULL DEFAULT '',
    channels     TEXT    NOT NULL DEFAULT '',
    sort         INTEGER NOT NULL DEFAULT 0,
    created_at   TEXT    NOT NULL,
    source       TEXT,
    ext_id       TEXT,
    category     TEXT,
    home_badge   TEXT    DEFAULT '',
    away_badge   TEXT    DEFAULT '',
    league_badge TEXT    DEFAULT '',
    status       TEXT    DEFAULT '',
    score        TEXT    DEFAULT '',
    sport_type   TEXT    NOT NULL DEFAULT 'soccer'
);

CREATE INDEX IF NOT EXISTS idx_devices_mac          ON devices(mac);
CREATE INDEX IF NOT EXISTS idx_devices_device_key   ON devices(device_key);
CREATE INDEX IF NOT EXISTS idx_playlists_device     ON playlists(device_id);
CREATE INDEX IF NOT EXISTS idx_codes_code           ON activation_codes(code);
CREATE INDEX IF NOT EXISTS idx_creds_userpass       ON credentials(username, password);
CREATE INDEX IF NOT EXISTS idx_menu_ads_sort        ON menu_ads(sort);
CREATE INDEX IF NOT EXISTS idx_leagues_name         ON leagues(name);
CREATE INDEX IF NOT EXISTS idx_sport_events_date    ON sport_events(event_date, event_time);
CREATE INDEX IF NOT EXISTS idx_activation_codes_code ON activation_codes(code);
