<?php
declare(strict_types=1);

require_once __DIR__ . '/config.php';

function gpc_db(): PDO
{
    static $pdo = null;
    if ($pdo instanceof PDO) {
        return $pdo;
    }

    if (!is_dir(GPC_DATA)) {
        mkdir(GPC_DATA, 0755, true);
    }

    $pdo = new PDO('sqlite:' . GPC_DB);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $pdo->exec('PRAGMA journal_mode=WAL');
    $pdo->exec('PRAGMA foreign_keys=ON');

    gpc_bootstrap($pdo);

    return $pdo;
}

function gpc_bootstrap(PDO $db): void
{
    $db->exec('CREATE TABLE IF NOT EXISTS admins (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        password TEXT NOT NULL,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS settings (
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS devices (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        mac_address TEXT NOT NULL,
        username TEXT DEFAULT "",
        password TEXT DEFAULT "",
        dns TEXT DEFAULT "",
        playlist_url TEXT DEFAULT "",
        title TEXT DEFAULT "GPC",
        expire_date TEXT,
        device_key TEXT,
        notes TEXT DEFAULT "",
        access_type TEXT DEFAULT "app_license",
        license_type TEXT DEFAULT "official",
        key_created_at TEXT DEFAULT "",
        key_expire_at TEXT DEFAULT "",
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS dns_servers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        url TEXT NOT NULL,
        code TEXT DEFAULT "",
        status INTEGER DEFAULT 1,
        validity TEXT DEFAULT "",
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )');



    $db->exec('CREATE TABLE IF NOT EXISTS resellers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL DEFAULT "",
        username TEXT NOT NULL UNIQUE,
        password TEXT NOT NULL,
        credits INTEGER DEFAULT 0,
        active INTEGER DEFAULT 1,
        notes TEXT DEFAULT "",
        dns_limit INTEGER DEFAULT 0,
        validity TEXT DEFAULT "",
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )');


    // Atualizações seguras para revenda DNS/código.
    try { $db->exec('ALTER TABLE resellers ADD COLUMN dns_limit INTEGER DEFAULT 0'); } catch (Throwable $e) {}
    try { $db->exec('ALTER TABLE resellers ADD COLUMN validity TEXT DEFAULT ""'); } catch (Throwable $e) {}
    try { $db->exec('ALTER TABLE resellers ADD COLUMN can_bulk_dns INTEGER DEFAULT 0'); } catch (Throwable $e) {}
    try { $db->exec('ALTER TABLE dns_servers ADD COLUMN reseller_id INTEGER DEFAULT NULL'); } catch (Throwable $e) {}
    try { $db->exec('CREATE INDEX IF NOT EXISTS idx_dns_servers_reseller_id ON dns_servers(reseller_id)'); } catch (Throwable $e) {}
    try { $db->exec('CREATE INDEX IF NOT EXISTS idx_dns_servers_code ON dns_servers(code)'); } catch (Throwable $e) {}

    // Atualizações seguras para painel revendedor SYNTRA.
    try { $db->exec('ALTER TABLE devices ADD COLUMN reseller_id INTEGER DEFAULT NULL'); } catch (Throwable $e) {}
    try { $db->exec('ALTER TABLE devices ADD COLUMN backup_license INTEGER DEFAULT 0'); } catch (Throwable $e) {}


    // SYNTRA Revenda V2.0: níveis e permissões profissionais.
    try { $db->exec('ALTER TABLE resellers ADD COLUMN reseller_plan TEXT DEFAULT "basic"'); } catch (Throwable $e) {}
    try { $db->exec('ALTER TABLE resellers ADD COLUMN can_mac INTEGER DEFAULT 1'); } catch (Throwable $e) {}
    try { $db->exec('ALTER TABLE resellers ADD COLUMN can_dns INTEGER DEFAULT 1'); } catch (Throwable $e) {}
    try { $db->exec('CREATE INDEX IF NOT EXISTS idx_devices_reseller_id ON devices(reseller_id)'); } catch (Throwable $e) {}
    try { $db->exec('CREATE INDEX IF NOT EXISTS idx_devices_access_type ON devices(access_type)'); } catch (Throwable $e) {}

    // Atualização segura para bancos antigos: separa licença do APP de acesso por parceiro/código.
    try { $db->exec('ALTER TABLE devices ADD COLUMN access_type TEXT DEFAULT "app_license"'); } catch (Throwable $e) {}
    try { $db->exec('ALTER TABLE devices ADD COLUMN license_type TEXT DEFAULT "official"'); } catch (Throwable $e) {}
    try { $db->exec('ALTER TABLE devices ADD COLUMN key_created_at TEXT DEFAULT ""'); } catch (Throwable $e) {}
    try { $db->exec('ALTER TABLE devices ADD COLUMN key_expire_at TEXT DEFAULT ""'); } catch (Throwable $e) {}
    try { $db->exec('CREATE INDEX IF NOT EXISTS idx_devices_license_type ON devices(license_type)'); } catch (Throwable $e) {}


    $db->exec('CREATE TABLE IF NOT EXISTS registration_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        mac_address TEXT,
        username TEXT,
        password TEXT,
        cod_revenda TEXT,
        dns_id INTEGER,
        dns_url TEXT,
        registered_at TEXT DEFAULT CURRENT_TIMESTAMP
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS client_login_attempts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        mac_address TEXT DEFAULT "",
        ip TEXT DEFAULT "",
        success INTEGER DEFAULT 0,
        reason TEXT DEFAULT "",
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS client_access_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        mac_address TEXT DEFAULT "",
        action TEXT DEFAULT "",
        ip TEXT DEFAULT "",
        user_agent TEXT DEFAULT "",
        details TEXT DEFAULT "",
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )');

    try { $db->exec('CREATE INDEX IF NOT EXISTS idx_client_login_attempts_ip_time ON client_login_attempts(ip, created_at)'); } catch (Throwable $e) {}
    try { $db->exec('CREATE INDEX IF NOT EXISTS idx_client_login_attempts_mac_time ON client_login_attempts(mac_address, created_at)'); } catch (Throwable $e) {}
    try { $db->exec('CREATE INDEX IF NOT EXISTS idx_client_access_logs_mac_time ON client_access_logs(mac_address, created_at)'); } catch (Throwable $e) {}

    $db->exec('CREATE TABLE IF NOT EXISTS banners (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT DEFAULT "",
        image_path TEXT NOT NULL,
        sort_order INTEGER DEFAULT 0,
        active INTEGER DEFAULT 1
    )');

    $defaults = [
        'panel_name'       => 'GPC Panel',
        'app_key_dev'      => '69865c6619886',
        'cod_revenda'      => '',
        'app_id'           => '11',
        'trial_days'       => '1',
        'auto_register_days' => '7',
        'auto_mac_enabled'   => '1',
        'client_login_max_attempts' => '5',
        'client_login_block_minutes' => '15',
        'client_session_minutes' => '30',
        'client_mask_sensitive_data' => '1',
        'notification'     => '',
        'logo_path'        => '',
        'bg_path'          => '',
    ];

    $ins = $db->prepare('INSERT OR IGNORE INTO settings (key, value) VALUES (:k, :v)');
    foreach ($defaults as $k => $v) {
        $ins->execute([':k' => $k, ':v' => $v]);
    }

    $count = (int) $db->query('SELECT COUNT(*) FROM admins')->fetchColumn();
    if ($count === 0) {
        $stmt = $db->prepare('INSERT INTO admins (username, password) VALUES (:u, :p)');
        $stmt->execute([
            ':u' => 'admin',
            ':p' => password_hash('admin123', PASSWORD_DEFAULT),
        ]);
    }
}

function gpc_setting(string $key, string $default = ''): string
{
    $stmt = gpc_db()->prepare('SELECT value FROM settings WHERE key = :k LIMIT 1');
    $stmt->execute([':k' => $key]);
    $row = $stmt->fetch();
    return $row ? (string) $row['value'] : $default;
}

function gpc_setting_save(string $key, string $value): void
{
    $stmt = gpc_db()->prepare('INSERT INTO settings (key, value) VALUES (:k, :v)
        ON CONFLICT(key) DO UPDATE SET value = excluded.value');
    $stmt->execute([':k' => $key, ':v' => $value]);
}
