<?php
// =============================================================================
// SQLite (PDO) connection + self-populating schema/seed.
//
// First call to panel_db() opens (or creates) panel.db, applies the schema if
// the tables don't exist yet, and inserts the default config row + admin user.
// =============================================================================

require_once __DIR__ . '/config.php';

function panel_db() {
    static $pdo = null;
    if ($pdo !== null) {
        return $pdo;
    }

    $dir = dirname(PANEL_DB_PATH);
    if (!is_dir($dir)) {
        @mkdir($dir, 0775, true);
    }

    try {
        $pdo = new PDO('sqlite:' . PANEL_DB_PATH);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $pdo->exec('PRAGMA foreign_keys = ON');
        $pdo->exec('PRAGMA journal_mode = WAL');
        $pdo->exec('PRAGMA synchronous  = NORMAL');
    } catch (PDOException $e) {
        throw new RuntimeException('database connection failed: ' . $e->getMessage());
    }

    db_ensure_schema($pdo);
    db_ensure_seed($pdo);

    // Apply the panel's configured timezone exactly once per request (the
    // outer `if ($pdo !== null) return $pdo;` caches the connection, so this
    // block also runs only on first init). Both admin pages (via bootstrap.php)
    // and webview endpoints (which skip bootstrap to preserve iframe headers)
    // hit panel_db() before rendering anything date-formatted.
    try {
        $tz_row = $pdo->query('SELECT timezone FROM config WHERE id = 1')->fetch();
        $tz = trim((string)($tz_row['timezone'] ?? ''));
        if ($tz === '' || @date_default_timezone_set($tz) === false) {
            date_default_timezone_set('UTC');
        }
    } catch (Throwable $e) {
        date_default_timezone_set('UTC');
    }

    return $pdo;
}

// ---------------------------------------------------------------------------
// Schema — single config row, no tenant scoping anywhere
// ---------------------------------------------------------------------------
function db_ensure_schema(PDO $pdo) {
    $statements = [
        // ----- ADMIN USERS -----
        "CREATE TABLE IF NOT EXISTS admin_users (
            id              INTEGER PRIMARY KEY AUTOINCREMENT,
            username        TEXT NOT NULL UNIQUE,
            password_hash   TEXT NOT NULL,
            role            TEXT NOT NULL DEFAULT 'admin',
            created_at      INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER)),
            last_login_at   INTEGER
        )",

        // ----- CONFIG (single row enforced by CHECK constraint) -----
        // Holds all libapksec/bootstrap identity constants AND the AppInfoModel
        // branding fields. Exactly one row, id = 1.
        "CREATE TABLE IF NOT EXISTS config (
            id                      INTEGER PRIMARY KEY CHECK (id = 1),
            display_name            TEXT    NOT NULL DEFAULT 'Master Panel',
            slug                    TEXT    NOT NULL DEFAULT '',
            customer_id             TEXT    NOT NULL DEFAULT '',
            pkg_name                TEXT    NOT NULL DEFAULT '',
            cert_sha256_hex         TEXT    NOT NULL DEFAULT '',
            libapksec_auth_key_hex  TEXT    NOT NULL DEFAULT '',
            libapksec_build_id      TEXT    NOT NULL DEFAULT '',
            target_server_key_hex   TEXT    NOT NULL DEFAULT '''',
            bootstrap_auth_key_hex  TEXT    NOT NULL DEFAULT '',
            bootstrap_build_id      TEXT    NOT NULL DEFAULT '',
            panel_base_url          TEXT    NOT NULL DEFAULT '',
            theme                   TEXT    NOT NULL DEFAULT '1',
            noti_title              TEXT    NOT NULL DEFAULT '',
            noti_content            TEXT    NOT NULL DEFAULT '',
            home_url1               TEXT    NOT NULL DEFAULT '',
            qr_url                  TEXT    NOT NULL DEFAULT '',
            subtitle_url            TEXT    NOT NULL DEFAULT '',
            apk_link                TEXT    NOT NULL DEFAULT '',
            app_version             TEXT    NOT NULL DEFAULT '4.3',
            is_ibo                  INTEGER NOT NULL DEFAULT 0,
            is_vu                   INTEGER NOT NULL DEFAULT 0,
            updated_at              INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
        )",

        // ----- DEVICES -----
        "CREATE TABLE IF NOT EXISTS devices (
            id                INTEGER PRIMARY KEY AUTOINCREMENT,
            mac_address       TEXT    NOT NULL UNIQUE,
            android_id        TEXT,
            app_device_id     TEXT,
            device_key        TEXT NOT NULL UNIQUE,
            app_type          TEXT,
            app_version       TEXT,
            is_google_pay     INTEGER NOT NULL DEFAULT 0,
            first_seen_at     INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER)),
            last_seen_at      INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
        )",
        "CREATE INDEX IF NOT EXISTS idx_devices_mac ON devices(mac_address)",

        "CREATE TABLE IF NOT EXISTS subscriptions (
            device_id        INTEGER PRIMARY KEY REFERENCES devices(id) ON DELETE CASCADE,
            plan_id          TEXT    DEFAULT '',
            price            TEXT    DEFAULT '',
            expired_date     TEXT    DEFAULT '',
            is_trial         INTEGER NOT NULL DEFAULT 0,
            lock             INTEGER NOT NULL DEFAULT 0,
            updated_at       INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
        )",
        "CREATE TABLE IF NOT EXISTS parent_control (
            device_id        INTEGER PRIMARY KEY REFERENCES devices(id) ON DELETE CASCADE,
            pin_code         TEXT    DEFAULT '',
            parent_control   TEXT    DEFAULT '',
            parent_synced    INTEGER NOT NULL DEFAULT 1,
            updated_at       INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
        )",

        // ----- PLAYLISTS -----
        "CREATE TABLE IF NOT EXISTS playlists (
            id               INTEGER PRIMARY KEY AUTOINCREMENT,
            device_id        INTEGER NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
            public_id        TEXT    NOT NULL,
            name             TEXT    NOT NULL,
            type             TEXT    NOT NULL DEFAULT 'xtream',
            url              TEXT    NOT NULL,
            username         TEXT    DEFAULT '',
            password         TEXT    DEFAULT '',
            is_protected     TEXT    NOT NULL DEFAULT '0',
            sort_order       INTEGER NOT NULL DEFAULT 0,
            created_at       INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER)),
            updated_at       INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER)),
            UNIQUE (device_id, public_id)
        )",
        "CREATE INDEX IF NOT EXISTS idx_playlists_device ON playlists(device_id, sort_order)",

        // ----- PORTALS -----
        "CREATE TABLE IF NOT EXISTS portals (
            id               INTEGER PRIMARY KEY AUTOINCREMENT,
            public_id        TEXT    NOT NULL UNIQUE,
            name             TEXT    NOT NULL,
            type             TEXT    NOT NULL DEFAULT 'xtream',
            url              TEXT    NOT NULL,
            is_protected     TEXT    NOT NULL DEFAULT '0',
            sort_order       INTEGER NOT NULL DEFAULT 0,
            enabled          INTEGER NOT NULL DEFAULT 1
        )",
        "CREATE INDEX IF NOT EXISTS idx_portals_order ON portals(enabled, sort_order)",

        // ----- VPN PROFILES -----
        "CREATE TABLE IF NOT EXISTS vpn_profiles (
            id               INTEGER PRIMARY KEY AUTOINCREMENT,
            name             TEXT    NOT NULL,
            provider         TEXT    NOT NULL DEFAULT 'Other',
            country          TEXT    NOT NULL DEFAULT '',
            state            TEXT    NOT NULL DEFAULT '',
            url              TEXT    NOT NULL DEFAULT '',
            ovpn             TEXT    NOT NULL,
            username         TEXT    NOT NULL,
            password         TEXT    NOT NULL,
            sort_order       INTEGER NOT NULL DEFAULT 0,
            enabled          INTEGER NOT NULL DEFAULT 1
        )",
        "CREATE INDEX IF NOT EXISTS idx_vpns_order ON vpn_profiles(enabled, sort_order)",

        // ----- LOGS -----
        "CREATE TABLE IF NOT EXISTS guard_key_log (
            id               INTEGER PRIMARY KEY AUTOINCREMENT,
            req_pkg          TEXT,
            req_cert         TEXT,
            req_ts           INTEGER,
            req_s            TEXT,
            req_s_valid      INTEGER,
            resp_k_hex       TEXT,
            ip_address       TEXT,
            user_agent       TEXT,
            created_at       INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
        )",
        "CREATE INDEX IF NOT EXISTS idx_guard_key_log_time ON guard_key_log(created_at)",

        "CREATE TABLE IF NOT EXISTS bootstrap_log (
            id               INTEGER PRIMARY KEY AUTOINCREMENT,
            req_pkg          TEXT,
            req_cert         TEXT,
            req_ts           INTEGER,
            req_nonce        TEXT,
            req_s            TEXT,
            req_s_valid      INTEGER,
            resp_payload_hex TEXT,
            ip_address       TEXT,
            created_at       INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
        )",
        "CREATE INDEX IF NOT EXISTS idx_bootstrap_log_time ON bootstrap_log(created_at)",

        "CREATE TABLE IF NOT EXISTS panel_request_log (
            id               INTEGER PRIMARY KEY AUTOINCREMENT,
            device_id        INTEGER REFERENCES devices(id) ON DELETE SET NULL,
            method           TEXT,
            path             TEXT,
            headers          TEXT,
            inner_json       TEXT,
            response_status  INTEGER,
            response_body    TEXT,
            ip_address       TEXT,
            created_at       INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
        )",
        "CREATE INDEX IF NOT EXISTS idx_panel_log_time   ON panel_request_log(created_at)",
        "CREATE INDEX IF NOT EXISTS idx_panel_log_device ON panel_request_log(device_id, created_at)",

        // ----- WEBVIEW SETTINGS (key/value bag for TMDB key, sports provider,
        // slideshow interval, etc — everything the admin pages on the WebViews
        // section save) -----
        "CREATE TABLE IF NOT EXISTS webview_settings (
            key              TEXT PRIMARY KEY,
            value            TEXT NOT NULL DEFAULT '',
            updated_at       INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
        )",

        // ----- WEBVIEW FRAMES (Framed View layouts) -----
        "CREATE TABLE IF NOT EXISTS webview_frames (
            id               INTEGER PRIMARY KEY AUTOINCREMENT,
            layout           INTEGER NOT NULL DEFAULT 1,
            slot_index       INTEGER NOT NULL DEFAULT 0,
            embedded_url     TEXT    NOT NULL DEFAULT '',
            scale_mode       TEXT    NOT NULL DEFAULT 'fit',
            sort             INTEGER NOT NULL DEFAULT 0,
            updated_at       INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
        )",

        // ----- WEBVIEW SLIDESHOWS (Media Slideshow images) -----
        "CREATE TABLE IF NOT EXISTS webview_slideshows (
            id               INTEGER PRIMARY KEY AUTOINCREMENT,
            image_url        TEXT    NOT NULL,
            duration_ms      INTEGER NOT NULL DEFAULT 5000,
            sort             INTEGER NOT NULL DEFAULT 0,
            enabled          INTEGER NOT NULL DEFAULT 1,
            created_at       INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
        )",

        // ----- SPORTS LEAGUES -----
        "CREATE TABLE IF NOT EXISTS sports_leagues (
            id                  INTEGER PRIMARY KEY AUTOINCREMENT,
            source              TEXT    NOT NULL DEFAULT 'manual',
            source_league_id    TEXT,
            name                TEXT    NOT NULL,
            country             TEXT    NOT NULL DEFAULT '',
            logo_url            TEXT    NOT NULL DEFAULT '',
            sort                INTEGER NOT NULL DEFAULT 0,
            enabled             INTEGER NOT NULL DEFAULT 1,
            updated_at          INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
        )",
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_sports_leagues_source ON sports_leagues(source, source_league_id)",

        // ----- SPORTS EVENTS -----
        // override_json holds admin manual edits that survive a feed sync:
        // any field present here replaces the feed value at read time.
        "CREATE TABLE IF NOT EXISTS sports_events (
            id                  INTEGER PRIMARY KEY AUTOINCREMENT,
            source              TEXT    NOT NULL DEFAULT 'manual',
            source_event_id     TEXT,
            league_id           INTEGER REFERENCES sports_leagues(id) ON DELETE CASCADE,
            home_team           TEXT    NOT NULL,
            away_team           TEXT    NOT NULL,
            home_badge_url      TEXT    NOT NULL DEFAULT '',
            away_badge_url      TEXT    NOT NULL DEFAULT '',
            kickoff_ts          INTEGER,
            status              TEXT    NOT NULL DEFAULT 'UPCOMING',
            home_score          INTEGER,
            away_score          INTEGER,
            channels_csv        TEXT    NOT NULL DEFAULT '',
            override_json       TEXT    NOT NULL DEFAULT '{}',
            updated_at          INTEGER NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER))
        )",
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_sports_events_source ON sports_events(source, source_event_id)",
        "CREATE INDEX IF NOT EXISTS idx_sports_events_kickoff ON sports_events(kickoff_ts)",
        "CREATE INDEX IF NOT EXISTS idx_sports_events_league  ON sports_events(league_id, kickoff_ts)",

        // ----- SPORTS FEED CACHE (raw responses keyed by provider+endpoint) -----
        "CREATE TABLE IF NOT EXISTS sports_feed_cache (
            provider         TEXT    NOT NULL,
            endpoint         TEXT    NOT NULL,
            fetched_at       INTEGER NOT NULL,
            payload_json     TEXT    NOT NULL,
            PRIMARY KEY (provider, endpoint)
        )",

        // ----- SPORTS SYNC LOG (cron audit) -----
        "CREATE TABLE IF NOT EXISTS sports_sync_log (
            id                  INTEGER PRIMARY KEY AUTOINCREMENT,
            provider            TEXT    NOT NULL,
            started_at          INTEGER NOT NULL,
            finished_at         INTEGER,
            leagues_upserted    INTEGER NOT NULL DEFAULT 0,
            events_upserted     INTEGER NOT NULL DEFAULT 0,
            ok                  INTEGER NOT NULL DEFAULT 0,
            error               TEXT
        )",
        "CREATE INDEX IF NOT EXISTS idx_sports_sync_log_time ON sports_sync_log(started_at)",

        // Slot table — retained for schema stability; not populated in this
        // variant.
        "CREATE TABLE IF NOT EXISTS slots (
            id              TEXT PRIMARY KEY,
            status          TEXT    NOT NULL DEFAULT 'active',
            app_name        TEXT    NOT NULL DEFAULT '',
            package_name    TEXT    NOT NULL DEFAULT '',
            keystore_path   TEXT,
            cert_sha256     TEXT    NOT NULL DEFAULT '',
            template_id     TEXT    NOT NULL DEFAULT 'iboultra',
            config_json     TEXT    NOT NULL DEFAULT '{}',
            last_built_at   INTEGER NOT NULL DEFAULT 0,
            last_job_id     TEXT,
            created_at      INTEGER NOT NULL
        )",

        // ----- APK BUILDER JOB HISTORY -----
        // Build job history table. Retained for schema stability across
        // panel variants; not populated in this variant.
        "CREATE TABLE IF NOT EXISTS builds (
            job_id          TEXT PRIMARY KEY,
            slot_id         TEXT    NOT NULL,
            spec_json       TEXT    NOT NULL,
            status          TEXT    NOT NULL DEFAULT 'queued',
            log_path        TEXT,
            apk_path        TEXT,
            apk_url         TEXT,
            apk_size        INTEGER,
            manifest_json   TEXT,
            cert_sha256     TEXT,
            exit_code       INTEGER,
            submitted_at    INTEGER NOT NULL,
            finished_at     INTEGER
        )",
        "CREATE INDEX IF NOT EXISTS idx_builds_submitted ON builds(submitted_at)",

        // Credit request table — retained for schema stability; not populated
        // in this variant.
        "CREATE TABLE IF NOT EXISTS credit_requests (
            id             INTEGER PRIMARY KEY AUTOINCREMENT,
            ts             INTEGER NOT NULL,
            amount         INTEGER NOT NULL,
            status         TEXT    NOT NULL DEFAULT 'pending',
            granted_amount INTEGER,
            tg_chat_id     INTEGER,
            tg_message_id  INTEGER,
            decided_at     INTEGER,
            notified_at    INTEGER,
            requested_by   TEXT
        )",
        "CREATE INDEX IF NOT EXISTS idx_credit_requests_status ON credit_requests(status)",
    ];

    foreach ($statements as $sql) {
        $pdo->exec($sql);
    }
}

// ---------------------------------------------------------------------------
// Seed: insert the default admin user and the single config row.
// Idempotent — only inserts what's missing.
// ---------------------------------------------------------------------------
function db_ensure_seed(PDO $pdo) {
    // Default admin
    $admin_exists = (int)$pdo->query('SELECT COUNT(*) FROM admin_users')->fetchColumn();
    if ($admin_exists === 0) {
        $hash = password_hash(PANEL_DEFAULT_ADMIN_PASS, PASSWORD_BCRYPT);
        $st = $pdo->prepare('INSERT INTO admin_users (username, password_hash, role) VALUES (?, ?, ?)');
        $st->execute([PANEL_DEFAULT_ADMIN_USER, $hash, 'admin']);
    }

    // Single config row
    $config_exists = (int)$pdo->query('SELECT COUNT(*) FROM config')->fetchColumn();
    if ($config_exists === 0) {
        $pdo->exec("INSERT INTO config (
            id, display_name, pkg_name, cert_sha256_hex,
            libapksec_auth_key_hex, libapksec_build_id, target_server_key_hex,
            bootstrap_auth_key_hex, bootstrap_build_id, panel_base_url,
            theme, noti_title, noti_content, app_version
        ) VALUES (
            1, 'Master Panel', '', '',
            '', '', '''',
            '', '', '',
            '1', 'Welcome', 'Enjoy your streaming!', '4.3'
        )");
    }

    // Self-healing migration: add columns to an already-created config table
    // (so operators with an existing DB don't have to `rm data/panel.db`).
    $cols = $pdo->query('PRAGMA table_info(config)')->fetchAll(PDO::FETCH_COLUMN, 1);
    if (!in_array('target_server_key_hex', $cols, true)) {
        $pdo->exec("ALTER TABLE config ADD COLUMN target_server_key_hex TEXT NOT NULL DEFAULT ''''");
    }
    if (!in_array('panel_base_url', $cols, true)) {
        $pdo->exec("ALTER TABLE config ADD COLUMN panel_base_url TEXT NOT NULL DEFAULT ''");
    }
    // Optional identity columns. Empty for standalone self-hosted installs.
    if (!in_array('slug', $cols, true)) {
        $pdo->exec("ALTER TABLE config ADD COLUMN slug TEXT NOT NULL DEFAULT ''");
    }
    if (!in_array('customer_id', $cols, true)) {
        $pdo->exec("ALTER TABLE config ADD COLUMN customer_id TEXT NOT NULL DEFAULT ''");
    }
    // addaplaylist.com integration — the third-party QR-pair broker that lets
    // end users scan a QR with their phone, enter their Xtream credentials,
    // and have the playlist forwarded to this panel without admin involvement.
    // Empty = the panel hasn't been registered with addaplaylist yet → the
    // QR rendering path falls through to the legacy "<mac>|<device_key>"
    // content; api/addaplaylist.php returns 404 for anyone hitting it.
    if (!in_array('addaplaylist_panel_code', $cols, true)) {
        $pdo->exec("ALTER TABLE config ADD COLUMN addaplaylist_panel_code TEXT NOT NULL DEFAULT ''");
    }
    if (!in_array('addaplaylist_auth_token', $cols, true)) {
        $pdo->exec("ALTER TABLE config ADD COLUMN addaplaylist_auth_token TEXT NOT NULL DEFAULT ''");
    }
    // Panel timezone — applied via date_default_timezone_set() so every date()
    // call (sport_top header label, log timestamps, etc.) renders local time
    // instead of whatever PHP's default is set to. Empty = use UTC. Buyers
    // can set this to their preferred zone from Settings → Configuration.
    if (!in_array('timezone', $cols, true)) {
        $pdo->exec("ALTER TABLE config ADD COLUMN timezone TEXT NOT NULL DEFAULT ''");
    }
    // Best-effort auto-register with addaplaylist on first DB creation. Only
    // runs when both addaplaylist columns are empty AND a bundled secrets
    // file is present at includes/addaplaylist_constants.php. Lite buyers
    // who strip that file get the legacy QR behavior with zero config.
    $aapl_row = $pdo->query('SELECT addaplaylist_panel_code FROM config WHERE id = 1')->fetch();
    if ($aapl_row && trim((string)($aapl_row['addaplaylist_panel_code'] ?? '')) === '') {
        try { addaplaylist_self_register($pdo); } catch (Throwable $e) { /* never fatal on seed */ }
    }

    // sports_events.thumb_url — TheSportsDB strThumb (event preview image used
    // as the card background in sport_top.php).
    $ev_cols = $pdo->query('PRAGMA table_info(sports_events)')->fetchAll(PDO::FETCH_COLUMN, 1);
    if ($ev_cols && !in_array('thumb_url', $ev_cols, true)) {
        $pdo->exec("ALTER TABLE sports_events ADD COLUMN thumb_url TEXT NOT NULL DEFAULT ''");
    }

    // admin_users TOTP columns — RFC 6238 secret (base32) + enabled flag.
    $au_cols = $pdo->query('PRAGMA table_info(admin_users)')->fetchAll(PDO::FETCH_COLUMN, 1);
    if (!in_array('totp_secret', $au_cols, true)) {
        $pdo->exec("ALTER TABLE admin_users ADD COLUMN totp_secret TEXT NOT NULL DEFAULT ''");
    }
    if (!in_array('totp_enabled', $au_cols, true)) {
        $pdo->exec("ALTER TABLE admin_users ADD COLUMN totp_enabled INTEGER NOT NULL DEFAULT 0");
    }
    // password_enc — AES-encrypted plaintext written by the master at provision
    // / reset time so the master admin can reveal the credentials to a buyer
    // who lost their password. Bcrypt hash stays the source-of-truth for login.
    // Nullable on standalone (non-b3studios) installs; only the master ever
    // writes it. Server key in /_secrets/ guards against DB-only leaks.
    if (!in_array('password_enc', $au_cols, true)) {
        $pdo->exec("ALTER TABLE admin_users ADD COLUMN password_enc TEXT");
    }

    // Seed lang_strings_<code> for the 8 built-in languages from
    // includes/_languages_seed.json. Idempotent — only inserts codes that
    // don't yet have a row in webview_settings. Also ensures lang_codes
    // includes all 8 codes (adds any missing without touching existing
    // custom codes).
    lang_strings_seed_if_missing($pdo);
}

function lang_strings_seed_if_missing(PDO $pdo) {
    $seed_path = __DIR__ . '/_languages_seed.json';
    if (!is_file($seed_path)) return;
    $raw = @file_get_contents($seed_path);
    if ($raw === false) return;
    $seed = @json_decode($raw, true);
    if (!is_array($seed)) return;

    // Grab what's already in webview_settings so we don't re-INSERT existing rows.
    $existing_keys = [];
    foreach ($pdo->query("SELECT key FROM webview_settings WHERE key LIKE 'lang_strings_%'") as $r) {
        $existing_keys[$r['key']] = true;
    }

    $ins = $pdo->prepare(
        "INSERT INTO webview_settings (key, value, updated_at) VALUES (?, ?, ?)
         ON CONFLICT(key) DO NOTHING"
    );
    $now = time();
    foreach ($seed as $code => $strings) {
        if (!is_array($strings) || !$strings) continue;
        $wkey = 'lang_strings_' . $code;
        if (isset($existing_keys[$wkey])) continue;
        $json = json_encode($strings, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        $ins->execute([$wkey, $json, $now]);
    }

    // Merge seeded codes into lang_codes so the dropdown lists them.
    $codes_row = $pdo->query("SELECT value FROM webview_settings WHERE key='lang_codes'")->fetch();
    $existing_codes = [];
    if ($codes_row && $codes_row['value']) {
        $existing_codes = array_values(array_filter(array_map('trim', explode(',', (string)$codes_row['value']))));
    } else {
        $existing_codes = ['en'];
    }
    $seed_codes = array_keys($seed);
    $merged = array_values(array_unique(array_merge($existing_codes, $seed_codes)));
    if ($merged !== $existing_codes) {
        $pdo->prepare(
            "INSERT INTO webview_settings (key, value, updated_at) VALUES ('lang_codes', ?, ?)
             ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at"
        )->execute([implode(',', $merged), $now]);
    }
}

// ---------------------------------------------------------------------------
// Convenience wrappers
// ---------------------------------------------------------------------------
function db_one($sql, $params = []) {
    $st = panel_db()->prepare($sql);
    $st->execute($params);
    $row = $st->fetch();
    return $row === false ? null : $row;
}

function db_all($sql, $params = []) {
    $st = panel_db()->prepare($sql);
    $st->execute($params);
    return $st->fetchAll();
}

function db_exec($sql, $params = []) {
    $st = panel_db()->prepare($sql);
    $st->execute($params);
    return $st->rowCount();
}

function db_insert($sql, $params = []) {
    $pdo = panel_db();
    $st = $pdo->prepare($sql);
    $st->execute($params);
    return (int)$pdo->lastInsertId();
}

// ---------------------------------------------------------------------------
// Auto-register THIS panel with addaplaylist on first DB creation. Reads the
// shared secret from a bundled constants file (gracefully skipped if absent),
// derives the panel's public domain from $_SERVER (web) or config.panel_base_url
// (CLI), and POSTs the simple {domain, endpoint_path, register_secret} envelope
// to addaplaylist's /api/panel/register. On success, writes the returned
// {panel_code, auth_token} into the config row.
//
// Best-effort: any failure (no secret file, no network, no domain detection,
// HTTP non-200) is silent. The panel boots fine with empty addaplaylist
// columns → QR + endpoint behave exactly as legacy.
// ---------------------------------------------------------------------------
function addaplaylist_self_register(PDO $pdo): void {
    $constants_path = __DIR__ . '/addaplaylist_constants.php';
    if (!is_file($constants_path)) return;
    $constants = @include $constants_path;
    if (!is_array($constants)) return;

    $url    = (string)($constants['PANEL_REGISTER_URL']    ?? '');
    $secret = (string)($constants['PANEL_REGISTER_SECRET'] ?? '');
    if ($url === '' || $secret === '') return;

    // Derive public domain. Order of preference:
    //   1. $_SERVER (HTTP request triggered the seed) → host + script-derived base
    //   2. config.panel_base_url (set at provision time)
    $domain = '';
    if (!empty($_SERVER['HTTP_HOST'])) {
        $host = (string)$_SERVER['HTTP_HOST'];
        $base = '';
        // Mirror helpers.php's panel_base_path() logic just enough for CLI safety
        $panel_root = realpath(__DIR__ . '/..');
        $script_fn  = isset($_SERVER['SCRIPT_FILENAME']) ? realpath($_SERVER['SCRIPT_FILENAME']) : '';
        $script_url = isset($_SERVER['SCRIPT_NAME']) ? (string)$_SERVER['SCRIPT_NAME'] : '';
        if ($panel_root && $script_fn && $script_url !== ''
            && strpos(str_replace('\\','/',$script_fn), str_replace('\\','/',$panel_root)) === 0) {
            $rel = str_replace('\\','/', substr($script_fn, strlen($panel_root)));
            if ($rel !== '' && substr($script_url, -strlen($rel)) === $rel) {
                $base = substr($script_url, 0, -strlen($rel));
                $base = ($base === '' ? '' : rtrim($base, '/'));
            }
        }
        $domain = $host . $base;
    }
    if ($domain === '') {
        try {
            $row = $pdo->query('SELECT panel_base_url FROM config WHERE id = 1')->fetch();
            $bu = trim((string)($row['panel_base_url'] ?? ''));
            if ($bu !== '') {
                $p = parse_url($bu);
                if (is_array($p) && !empty($p['host'])) {
                    $domain = $p['host'] . (isset($p['path']) ? rtrim($p['path'], '/') : '');
                }
            }
        } catch (Throwable $e) { /* table not ready yet — fall through */ }
    }
    if ($domain === '') return;   // can't auto-register without a callback target

    $payload = json_encode([
        'domain'          => $domain,
        'endpoint_path'   => 'api/addaplaylist.php',
        'register_secret' => $secret,
    ]);

    $ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_POST           => true,
        CURLOPT_POSTFIELDS     => $payload,
        CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_CONNECTTIMEOUT => 3,
        CURLOPT_TIMEOUT        => 8,
    ]);
    $body = curl_exec($ch);
    $code = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    if ($code !== 200) return;

    $resp = json_decode((string)$body, true);
    if (!is_array($resp) || empty($resp['panel_code']) || empty($resp['auth_token'])) return;

    $pdo->prepare(
        'UPDATE config SET addaplaylist_panel_code = ?, addaplaylist_auth_token = ?, updated_at = ? WHERE id = 1'
    )->execute([
        (string)$resp['panel_code'],
        (string)$resp['auth_token'],
        time(),
    ]);
}

function db_config() {
    $row = db_one('SELECT * FROM config WHERE id = 1');
    if (!$row) { $row = []; }
    // Static fallbacks from apksec_constants.php cover keys the DB row leaves
    // blank. DB values, when present, take precedence.
    static $constants = null;
    if ($constants === null) {
        $constants = @include __DIR__ . '/apksec_constants.php';
        if (!is_array($constants)) { $constants = []; }
    }
    foreach ($constants as $k => $v) {
        $cur = $row[$k] ?? null;
        if ($cur === null || $cur === '') {
            $row[$k] = $v;
        }
    }
    return $row;
}

