-- lib/schema.sql — Esquema completo del panel (SQLite).
-- Coincide exactamente con el contrato (API_CONTRACT.md §"Esquema SQLite").
-- Todas las sentencias usan IF NOT EXISTS para que install.php sea idempotente.

-- Administradores del panel (login web).
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
);

-- Dispositivos (identidad = MAC). Una fila por TV/box.
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
);

-- Servidores DNS (URLs base de servidores Xtream gestionadas por el admin).
-- Una playlist Xtream se compone de un DNS (url) + username + password.
CREATE TABLE IF NOT EXISTS dns (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    title      TEXT    NOT NULL,
    url        TEXT    NOT NULL,
    created_at TEXT    NOT NULL
);

-- Servidores VPN (OpenVPN). El admin pega/sube el .ovpn; host/protocolo/auth se
-- autodetectan. La app baja los habilitados (api/device/vpn) e importa el perfil.
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
);

-- Playlists por dispositivo (Xtream o M3U).
-- Para 'xtream': url = la URL del DNS elegido + username/password.
-- Para 'm3u':    url = URL directa de la lista (username/password vacíos).
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',   -- 'xtream' | '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
);

-- Credenciales del modelo reseller: playlists creadas por el admin que
-- cualquier dispositivo puede reclamar haciendo login con username/password.
-- Resuelven a un DNS (dns_id) + las credenciales para el login Xtream.
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
);

-- Códigos de activación (numéricos). Cada código lleva las credenciales Xtream
-- embebidas (username/password/dns_id) que el dispositivo recibe al canjearlo.
-- permanent=1: multiuso, no expira (sirve de login del usuario). Si no, single-use
-- y expira en 'expire'. protected=1: la playlist resultante oculta sus datos Xtream.
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
);

-- Ajustes globales clave/valor (default_theme, apk_url, app_version, branding…).
CREATE TABLE IF NOT EXISTS settings (
    key   TEXT PRIMARY KEY,
    value TEXT
);

-- Idiomas ofrecidos por la app.
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
);

-- Anuncios de menú: enlaces/paneles promocionales que la app muestra.
-- Se exponen en config/login (menu_ads[]) y en GET /api/ads, ordenados por sort.
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
);

-- Ligas deportivas (catálogo) para el panel de Deportes.
-- league_id es el identificador externo opcional (proveedor de datos).
CREATE TABLE IF NOT EXISTS leagues (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT    NOT NULL,
    league_id  TEXT,
    created_at TEXT    NOT NULL
);

-- Eventos deportivos: partido con fecha/hora y canales de TV donde se emite.
-- channels = lista de nombres de canal separados por comas (o JSON array).
-- Se exponen en GET /api/sports filtrados a los próximos N días.
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 '',   -- YYYY-MM-DD
    event_time TEXT    NOT NULL DEFAULT '',   -- HH:MM
    channels   TEXT    NOT NULL DEFAULT '',   -- "Canal 1, Canal 2"
    sort       INTEGER NOT NULL DEFAULT 0,
    created_at TEXT    NOT NULL
);

-- Índices de apoyo para búsquedas frecuentes.
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_sport_events_date    ON sport_events(event_date, event_time);
-- Nota: idx_playlists_userpass (sobre playlists.username/password) se crea en
-- install.php DESPUÉS de las migraciones de columnas, porque en bases antiguas
-- esas columnas no existen hasta el ALTER TABLE.
