<?php
/** PDO singleton with utf8mb4. */

function db(): PDO {
    static $pdo = null;
    if ($pdo) return $pdo;

    $dsn = sprintf(
        'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
        DB_HOST, DB_PORT, DB_NAME
    );
    try {
        $pdo = new PDO($dsn, DB_USER, DB_PASS, [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
            PDO::ATTR_PERSISTENT         => false,
        ]);
    } catch (Throwable $e) {
        json_error(500, 'Database connection failed: ' . $e->getMessage());
    }
    return $pdo;
}

/**
 * Create tables on first request if they don't exist yet.
 * Idempotent - safe to call on every request (it only runs DDL once
 * per PHP process since static $done short-circuits subsequent calls).
 */
function ensure_schema(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    $sql = <<<SQL
CREATE TABLE IF NOT EXISTS `users` (
  `id`              VARCHAR(64)  NOT NULL PRIMARY KEY,
  `username`        VARCHAR(64)  NOT NULL UNIQUE,
  `password_hash`   VARCHAR(255) NOT NULL,
  `role`            VARCHAR(16)  NOT NULL DEFAULT 'user',
  `created_at`      VARCHAR(40)  NOT NULL,
  `expiry_at`       VARCHAR(40)           DEFAULT NULL,
  `xtream_mode`     VARCHAR(16)  NOT NULL DEFAULT 'shared',
  `xtream_server`   VARCHAR(500) NOT NULL DEFAULT '',
  `xtream_username` VARCHAR(255) NOT NULL DEFAULT '',
  `xtream_password` VARCHAR(255) NOT NULL DEFAULT '',
  `live_server`     VARCHAR(500) NOT NULL DEFAULT '',
  `live_username`   VARCHAR(255) NOT NULL DEFAULT '',
  `live_password`   VARCHAR(255) NOT NULL DEFAULT '',
  `vod_server`      VARCHAR(500) NOT NULL DEFAULT '',
  `vod_username`    VARCHAR(255) NOT NULL DEFAULT '',
  `vod_password`    VARCHAR(255) NOT NULL DEFAULT '',
  `note`            TEXT         NOT NULL,
  `ads_enabled`     TINYINT(1)            DEFAULT NULL,
  INDEX `ix_users_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `devices` (
  `device_id`    VARCHAR(32)  NOT NULL PRIMARY KEY,
  `mac_address`  VARCHAR(32)  NOT NULL,
  `pin`          VARCHAR(8)   NOT NULL UNIQUE,
  `paired`       TINYINT(1)   NOT NULL DEFAULT 0,
  `server_url`   VARCHAR(500)          DEFAULT NULL,
  `username`     VARCHAR(255)          DEFAULT NULL,
  `password`     VARCHAR(255)          DEFAULT NULL,
  `profile_name` VARCHAR(255)          DEFAULT NULL,
  `created_at`   VARCHAR(40)  NOT NULL,
  `paired_at`    VARCHAR(40)           DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `settings` (
  `id`   VARCHAR(32) NOT NULL PRIMARY KEY,
  `data` JSON        NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SQL;

    foreach (explode(';', $sql) as $stmt) {
        $stmt = trim($stmt);
        if ($stmt !== '') db()->exec($stmt);
    }

    // Additive migrations — run idempotently on every schema check so
    // existing production databases pick up new columns without a drop.
    try {
        db()->exec("ALTER TABLE `users` ADD COLUMN `ads_enabled` TINYINT(1) DEFAULT NULL");
    } catch (Throwable $e) {
        // Column already exists — ignore.
    }
    // Hybrid source columns (Source A = Live TV, Source B = VOD)
    $hybrid_cols = [
        'live_server'   => "VARCHAR(500) NOT NULL DEFAULT ''",
        'live_username' => "VARCHAR(255) NOT NULL DEFAULT ''",
        'live_password' => "VARCHAR(255) NOT NULL DEFAULT ''",
        'vod_server'    => "VARCHAR(500) NOT NULL DEFAULT ''",
        'vod_username'  => "VARCHAR(255) NOT NULL DEFAULT ''",
        'vod_password'  => "VARCHAR(255) NOT NULL DEFAULT ''",
    ];
    foreach ($hybrid_cols as $col => $def) {
        try {
            db()->exec("ALTER TABLE `users` ADD COLUMN `$col` $def");
        } catch (Throwable $e) {
            // Column already exists — ignore.
        }
    }

    // BUG-5 FEATURE (2026-05) — `ad_free_until` column for the
    // "watch N rewarded ads → unlock M hours ad-free" flow. ISO 8601
    // datetime string, nullable. Idempotent — DROP/EXISTS guard makes
    // this safe to call on every request.
    try {
        db()->exec("ALTER TABLE `users` ADD COLUMN `ad_free_until` VARCHAR(40) DEFAULT NULL");
    } catch (Throwable $e) {
        // Column already exists — ignore.
    }
}

/** Seed the admin row if it doesn't exist (re-syncs password from config). */
function ensure_admin_seed(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    ensure_schema();

    $row = db()->prepare('SELECT id, password_hash FROM users WHERE username = ?');
    $row->execute([strtolower(ADMIN_USERNAME)]);
    $existing = $row->fetch();

    $hash = password_hash(ADMIN_PASSWORD, PASSWORD_BCRYPT);

    if (!$existing) {
        $stmt = db()->prepare(
            'INSERT INTO users (id, username, password_hash, role, created_at, expiry_at,
                                xtream_mode, xtream_server, xtream_username, xtream_password, note)
             VALUES (?, ?, ?, "admin", ?, NULL, "shared", "", "", "", "")'
        );
        $stmt->execute([
            uuid_v4(),
            strtolower(ADMIN_USERNAME),
            $hash,
            (new DateTime('now', new DateTimeZone('UTC')))->format('Y-m-d\TH:i:s.uP'),
        ]);
    } else if (!password_verify(ADMIN_PASSWORD, $existing['password_hash'])) {
        $stmt = db()->prepare('UPDATE users SET password_hash = ?, role = "admin" WHERE id = ?');
        $stmt->execute([$hash, $existing['id']]);
    }
}

function uuid_v4(): string {
    $d = random_bytes(16);
    $d[6] = chr((ord($d[6]) & 0x0f) | 0x40);
    $d[8] = chr((ord($d[8]) & 0x3f) | 0x80);
    return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($d), 4));
}

function now_iso(): string {
    return (new DateTime('now', new DateTimeZone('UTC')))->format('Y-m-d\TH:i:s.uP');
}
