<?php
/**
 * table.php — Database schema definition
 *
 * Tables:
 *   user     - Admin credentials (password stored as bcrypt hash)
 *   dns      - IPTV portal/DNS entries served to the app
 *   note     - Announcements/notes
 *   welcome  - Welcome messages shown in the app
 *   devices  - Connected device tracking (from API calls)
 */

$tables = [
    'user' => [
        'id' => 'INTEGER PRIMARY KEY',
        'username' => 'TEXT NOT NULL',
        'password' => 'TEXT NOT NULL',
        'password_changed_at' => 'TEXT',
        'failed_attempts' => 'INTEGER DEFAULT 0',
        'locked_until' => 'TEXT',
    ],
    'dns' => [
        'id' => 'INTEGER PRIMARY KEY',
        'title' => 'TEXT',
        'url' => 'TEXT',
        'type' => "TEXT DEFAULT 'xtream'",
        'username' => 'TEXT',
        'password' => 'TEXT',
        'mac_address' => 'TEXT',
        'output' => "TEXT DEFAULT 'm3u_plus'",
    ],
    'note' => [
        'id' => 'INTEGER PRIMARY KEY',
        'note_title' => 'TEXT',
        'note_content' => 'TEXT',
        'start_time' => 'TEXT',
        'end_time' => 'TEXT',
        'createdate' => 'TEXT',
    ],
    'note_seen' => [
        'id' => 'INTEGER PRIMARY KEY',
        'note_id' => 'INTEGER NOT NULL',
        'device_id' => 'TEXT NOT NULL',
        'seen_at' => 'TEXT',
    ],
    'welcome' => [
        'id' => 'INTEGER PRIMARY KEY',
        'message_one' => 'TEXT',
        'message_two' => 'TEXT',
        'message_three' => 'TEXT',
    ],
    'devices' => [
        'id' => 'INTEGER PRIMARY KEY',
        'device_id' => 'TEXT',
        'device_id2' => 'TEXT',
        'mac_address' => 'TEXT',
        'version_code' => 'TEXT',
        'last_seen' => 'TEXT',
        'first_seen' => 'TEXT',
        'action' => 'TEXT',
        'ip_address' => 'TEXT',
    ],
    'vpn_profiles' => [
        'id' => 'INTEGER PRIMARY KEY',
        'name' => 'TEXT',
        'country' => 'TEXT',
        'city' => 'TEXT',
        'protocol' => "TEXT DEFAULT 'udp'",
        'port' => "TEXT DEFAULT '1194'",
        'ovpn_config' => 'TEXT',
        'config_url' => 'TEXT',
        'username' => 'TEXT',
        'password' => 'TEXT',
        'enabled' => 'INTEGER DEFAULT 1',
        'created_at' => 'TEXT',
    ],
    'sports_config' => [
        'id' => 'INTEGER PRIMARY KEY',
        'enabled' => 'INTEGER DEFAULT 0',
        'title' => "TEXT DEFAULT 'Sports'",
        'url' => 'TEXT',
        'javascript' => 'INTEGER DEFAULT 1',
        'allow_external' => 'INTEGER DEFAULT 0',
        'user_agent' => 'TEXT',
        'maintenance_message' => 'TEXT',
        'cache_mode' => "TEXT DEFAULT 'default'",
    ],
    'webview_config' => [
        'id' => 'INTEGER PRIMARY KEY',
        'enabled' => 'INTEGER DEFAULT 0',
        'title' => "TEXT DEFAULT 'Web'",
        'url' => 'TEXT',
        'javascript' => 'INTEGER DEFAULT 1',
        'allow_external' => 'INTEGER DEFAULT 0',
        'user_agent' => 'TEXT',
        'maintenance_message' => 'TEXT',
        'cache_mode' => "TEXT DEFAULT 'default'",
    ],
    // Sports fixtures feature — panel-side proxy/cache for TheSportsDB so the
    // API key never ships inside the app. See api/sports-fixtures.php.
    'sports_fixtures_config' => [
        'id' => 'INTEGER PRIMARY KEY',
        'enabled' => 'INTEGER DEFAULT 0',
        'thesportsdb_api_key' => "TEXT DEFAULT '3'", // TheSportsDB's public test key
        'cache_ttl_seconds' => 'INTEGER DEFAULT 300',
        // sport-tv-guide.live widget settings (the app's Sports menu)
        'widget_token' => "TEXT DEFAULT '104cc8ac97da'",
        'widget_sports' => "TEXT DEFAULT '1,7,8,14,15'",
        'widget_bg' => "TEXT DEFAULT '#2e3440'",
        'widget_bgs' => "TEXT DEFAULT '#3b4252'",
        'widget_timezone' => "TEXT DEFAULT 'Europe/London'",
        'widget_time12' => "TEXT DEFAULT '1'",
    ],
    'sports_fixtures_cache' => [
        'id' => 'INTEGER PRIMARY KEY',
        'cache_key' => 'TEXT NOT NULL',
        'json' => 'TEXT',
        'fetched_at' => 'INTEGER',
    ],
    // Sports events — the IBO-style 3-pane sports guide. Populated by
    // webview.php?fetch=sport (auto-import from ESPN mobile endpoint).
    'leagues' => [
        'id' => 'INTEGER PRIMARY KEY AUTOINCREMENT',
        'name' => 'TEXT NOT NULL',
        'league_id' => 'TEXT',
        'sport_type' => "TEXT NOT NULL DEFAULT ''",
        'created_at' => 'TEXT NOT NULL',
    ],
    'sport_events' => [
        'id' => 'INTEGER PRIMARY KEY',
        'league' => "TEXT NOT NULL DEFAULT ''",
        'league_id' => 'INTEGER',
        'sport_type' => "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 DEFAULT 0',
        'created_at' => 'TEXT',
        'source' => "TEXT NOT NULL DEFAULT 'manual'",
        'ext_id' => "TEXT NOT NULL DEFAULT ''",
        'category' => "TEXT NOT NULL DEFAULT ''",
        'home_badge' => "TEXT NOT NULL DEFAULT ''",
        'away_badge' => "TEXT NOT NULL DEFAULT ''",
        'league_badge' => "TEXT NOT NULL DEFAULT ''",
        'status' => "TEXT NOT NULL DEFAULT ''",
        'score' => "TEXT NOT NULL DEFAULT ''",
    ],
    // Analytics — lightweight request logging for the app-facing API
    // endpoints (api.php, guard/check.php, api/*.php). See
    // includes/functions.php::logRequest() and analytics.php.
    'request_log' => [
        'id' => 'INTEGER PRIMARY KEY',
        'endpoint' => 'TEXT NOT NULL',
        'ip_address' => 'TEXT',
        'country_code' => 'TEXT',
        'user_agent' => 'TEXT',
        'created_at' => 'INTEGER NOT NULL', // unix timestamp, for time-window filtering
    ],
    // Per-IP geolocation cache so analytics don't trigger an upstream
    // lookup (ipwho.is) on every single request — see logRequest().
    'ip_geo_cache' => [
        'ip_address' => 'TEXT PRIMARY KEY',
        'country_code' => 'TEXT',
        'latitude' => 'REAL',
        'longitude' => 'REAL',
        'resolved_at' => 'INTEGER',
    ],
    // Passkey (WebAuthn/FIDO2) credentials for optional two-factor login.
    // See includes/webauthn_bootstrap.php and api/webauthn-*.php.
    'webauthn_credentials' => [
        'id' => 'INTEGER PRIMARY KEY',
        'user_id' => 'INTEGER NOT NULL',
        'credential_id' => 'TEXT NOT NULL', // base64url-encoded credential ID
        'public_key' => 'TEXT NOT NULL',    // PEM-encoded public key, as returned by the WebAuthn library
        'sign_count' => 'INTEGER DEFAULT 0',
        'label' => 'TEXT',                  // user-supplied nickname, e.g. "MacBook Touch ID"
        'created_at' => 'TEXT',
        'last_used_at' => 'TEXT',
    ],
];
