<?php
/**
 * Funções de banco e dispositivos (multi-tenant por ID)
 */
require_once __DIR__ . '/../config.php';

function raw_db(): PDO
{
    static $pdo = null;
    if ($pdo === null) {
        $dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=' . DB_CHARSET;
        $pdo = new PDO($dsn, DB_USER, DB_PASS, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
    }
    return $pdo;
}

function db(): PDO
{
    // Em produção, apenas abre a conexão. Alterações de estrutura ficam no install.php.
    // Isso evita erro HTTP 500 em hospedagens onde o usuário MySQL não possui
    // permissão CREATE/ALTER durante um login comum.
    return raw_db();
}

function ensure_device_schema(): void
{
    static $done = false;
    if ($done) {
        return;
    }
    $pdo = raw_db();

    $pdo->exec("CREATE TABLE IF NOT EXISTS devices (
        id CHAR(36) PRIMARY KEY,
        label VARCHAR(120) DEFAULT NULL,
        device_model VARCHAR(120) DEFAULT NULL,
        app_name VARCHAR(120) DEFAULT 'NEXUS TV',
        logo_file VARCHAR(255) DEFAULT NULL,
        wallpaper_file VARCHAR(255) DEFAULT NULL,
        launcher_blocked TINYINT(1) DEFAULT 0,
        block_message TEXT DEFAULT NULL,
        settings_lock_enabled TINYINT(1) DEFAULT 0,
        settings_lock_hash VARCHAR(64) DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        last_seen_at TIMESTAMP NULL DEFAULT NULL,
        last_ip VARCHAR(45) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS device_apps (
        id INT AUTO_INCREMENT PRIMARY KEY,
        device_id CHAR(36) NOT NULL,
        name VARCHAR(120) NOT NULL,
        package_name VARCHAR(255) DEFAULT NULL,
        icon_file VARCHAR(255) NOT NULL,
        apk_file VARCHAR(255) DEFAULT NULL,
        sort_order INT DEFAULT 0,
        is_enabled TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_device_apps_device (device_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $done = true;
}

function ensure_device_iptv_reset_column(): void
{
    static $done = false;
    if ($done) {
        return;
    }
    $pdo = raw_db();
    try {
        $stmt = $pdo->query("SHOW COLUMNS FROM devices LIKE 'iptv_reset_nonce'");
        if (!$stmt->fetch()) {
            $pdo->exec('ALTER TABLE devices ADD COLUMN iptv_reset_nonce INT UNSIGNED NOT NULL DEFAULT 0');
        }
    } catch (Throwable $e) {
        // Ignora se já existir ou ambiente sem permissão ALTER
    }
    $done = true;
}

function setting(string $key, ?string $default = null): ?string
{
    $stmt = db()->prepare('SELECT setting_value FROM settings WHERE setting_key = ?');
    $stmt->execute([$key]);
    $row = $stmt->fetch();
    return $row ? $row['setting_value'] : $default;
}

function set_setting(string $key, string $value): void
{
    $stmt = db()->prepare(
        'INSERT INTO settings (setting_key, setting_value) VALUES (?, ?)
         ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)'
    );
    $stmt->execute([$key, $value]);
}

function ensure_default_settings(): void
{
    $defaults = [
        'app_name' => 'NEXUS TV',
        'launcher_blocked' => '0',
        'block_message' => 'Este launcher foi desativado pelo administrador. Entre em contato com o suporte.',
        'settings_lock_enabled' => '0',
        'settings_lock_hash' => '',
        'renewal_contact_phone' => '',
        'renewal_contact_message' => 'Olá, preciso renovar meu acesso IPTV.',
    ];
    foreach ($defaults as $key => $value) {
        $stmt = db()->prepare('INSERT IGNORE INTO settings (setting_key, setting_value) VALUES (?, ?)');
        $stmt->execute([$key, $value]);
    }
    if (!setting('admin_username')) {
        set_setting('admin_username', ADMIN_USER);
    }
    if (!setting('admin_password_hash')) {
        set_setting('admin_password_hash', password_hash(ADMIN_PASS, PASSWORD_DEFAULT));
    }
}

function is_valid_device_id(string $id): bool
{
    return (bool) preg_match('/^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}$/i', $id);
}

function get_device(string $deviceId): ?array
{
    if (!is_valid_device_id($deviceId)) {
        return null;
    }
    $stmt = db()->prepare('SELECT * FROM devices WHERE id = ?');
    $stmt->execute([$deviceId]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function register_device(string $deviceId, ?string $label = null, ?string $model = null): void
{
    if (!is_valid_device_id($deviceId)) {
        throw new InvalidArgumentException('ID de dispositivo inválido.');
    }
    $ip = $_SERVER['REMOTE_ADDR'] ?? null;
    $stmt = db()->prepare(
        'INSERT INTO devices (id, label, device_model, last_seen_at, last_ip)
         VALUES (?, ?, ?, NOW(), ?)
         ON DUPLICATE KEY UPDATE
            label = COALESCE(VALUES(label), label),
            device_model = COALESCE(VALUES(device_model), device_model),
            last_seen_at = NOW(),
            last_ip = VALUES(last_ip)'
    );
    $stmt->execute([
        $deviceId,
        $label ?: ('TV ' . strtoupper(substr(str_replace('-', '', $deviceId), 0, 6))),
        $model,
        $ip,
    ]);
}

function touch_device(string $deviceId): void
{
    $stmt = db()->prepare('UPDATE devices SET last_seen_at = NOW(), last_ip = ? WHERE id = ?');
    $stmt->execute([$_SERVER['REMOTE_ADDR'] ?? null, $deviceId]);
}

function list_devices(): array
{
    return db()->query(
        'SELECT d.*,
            (SELECT COUNT(*) FROM device_apps da WHERE da.device_id = d.id AND da.is_enabled = 1) AS apps_count
         FROM devices d
         ORDER BY d.last_seen_at DESC, d.created_at DESC'
    )->fetchAll();
}

function update_device(string $deviceId, array $fields): void
{
    if (empty($fields)) {
        return;
    }
    $sets = [];
    $values = [];
    foreach ($fields as $col => $val) {
        $sets[] = "$col = ?";
        $values[] = $val;
    }
    $values[] = $deviceId;
    $sql = 'UPDATE devices SET ' . implode(', ', $sets) . ' WHERE id = ?';
    db()->prepare($sql)->execute($values);
}

/** Solicita logout da lista IPTV no app deste dispositivo (incrementa nonce remoto). */
function request_device_iptv_reset(string $deviceId): int
{
    if (!is_valid_device_id($deviceId)) {
        throw new InvalidArgumentException('ID de dispositivo inválido.');
    }
    db()->prepare('UPDATE devices SET iptv_reset_nonce = iptv_reset_nonce + 1 WHERE id = ?')
        ->execute([$deviceId]);
    $stmt = db()->prepare('SELECT iptv_reset_nonce FROM devices WHERE id = ?');
    $stmt->execute([$deviceId]);
    return (int) ($stmt->fetchColumn() ?: 0);
}

function device_upload_base(string $deviceId): string
{
    return PANEL_ROOT . '/uploads/devices/' . $deviceId;
}

function device_upload_url(string $deviceId, string $relativePath): string
{
    return upload_url('uploads/devices/' . $deviceId . '/' . ltrim($relativePath, '/'));
}

function ensure_device_upload_dirs(string $deviceId): array
{
    $base = device_upload_base($deviceId);
    $dirs = [
        'logo' => $base . '/logo',
        'wallpaper' => $base . '/wallpaper',
        'icons' => $base . '/apps/icons',
        'apk' => $base . '/apps/apk',
    ];
    foreach ($dirs as $dir) {
        if (!is_dir($dir)) {
            mkdir($dir, 0755, true);
        }
    }
    return $dirs;
}

function upload_url(string $relativePath): string
{
    return rtrim(PANEL_BASE_URL, '/') . '/' . ltrim(str_replace('\\', '/', $relativePath), '/');
}

function count_device_apps(string $deviceId): int
{
    $stmt = db()->prepare('SELECT COUNT(*) FROM device_apps WHERE device_id = ?');
    $stmt->execute([$deviceId]);
    return (int) $stmt->fetchColumn();
}

function get_device_apps(string $deviceId): array
{
    $stmt = db()->prepare(
        'SELECT * FROM device_apps WHERE device_id = ? ORDER BY sort_order ASC, id ASC'
    );
    $stmt->execute([$deviceId]);
    return $stmt->fetchAll();
}

function delete_device(string $deviceId): void
{
    $apps = get_device_apps($deviceId);
    $dirs = ensure_device_upload_dirs($deviceId);
    foreach ($apps as $app) {
        @unlink($dirs['icons'] . '/' . $app['icon_file']);
        if ($app['apk_file']) {
            @unlink($dirs['apk'] . '/' . $app['apk_file']);
        }
    }
    $device = get_device($deviceId);
    if ($device) {
        if ($device['logo_file']) {
            @unlink($dirs['logo'] . '/' . $device['logo_file']);
        }
        if ($device['wallpaper_file']) {
            @unlink($dirs['wallpaper'] . '/' . $device['wallpaper_file']);
        }
    }
    db()->prepare('DELETE FROM device_apps WHERE device_id = ?')->execute([$deviceId]);
    db()->prepare('DELETE FROM devices WHERE id = ?')->execute([$deviceId]);
    $base = device_upload_base($deviceId);
    if (is_dir($base)) {
        rrmdir($base);
    }
}

function rrmdir(string $dir): void
{
    if (!is_dir($dir)) {
        return;
    }
    foreach (scandir($dir) as $item) {
        if ($item === '.' || $item === '..') {
            continue;
        }
        $path = $dir . DIRECTORY_SEPARATOR . $item;
        is_dir($path) ? rrmdir($path) : @unlink($path);
    }
    @rmdir($dir);
}

function format_device_seen(?string $datetime): string
{
    if (!$datetime) {
        return 'Nunca';
    }
    return date('d/m/Y H:i', strtotime($datetime));
}

function device_presence_status(?string $lastSeenAt): array
{
    if (!$lastSeenAt) {
        return ['label' => 'Nunca conectou', 'class' => 'offline'];
    }
    $diff = time() - strtotime($lastSeenAt);
    if ($diff <= 900) {
        return ['label' => 'Online agora', 'class' => 'online'];
    }
    if ($diff <= 86400) {
        return ['label' => 'Ativo hoje', 'class' => 'recent'];
    }
    if ($diff <= 604800) {
        return ['label' => 'Esta semana', 'class' => 'idle'];
    }
    return ['label' => 'Inativo', 'class' => 'offline'];
}

function get_dashboard_stats(): array
{
    $pdo = db();
    $uploadsDir = PANEL_ROOT . '/uploads';
    if (!is_dir($uploadsDir)) {
        @mkdir($uploadsDir, 0755, true);
    }

    $totalDevices = (int) $pdo->query('SELECT COUNT(*) FROM devices')->fetchColumn();
    $onlineNow = (int) $pdo->query(
        "SELECT COUNT(*) FROM devices WHERE last_seen_at >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)"
    )->fetchColumn();
    $onlineToday = (int) $pdo->query(
        "SELECT COUNT(*) FROM devices WHERE last_seen_at >= CURDATE()"
    )->fetchColumn();
    $blockedCount = (int) $pdo->query(
        'SELECT COUNT(*) FROM devices WHERE launcher_blocked = 1'
    )->fetchColumn();
    $lockedCount = (int) $pdo->query(
        "SELECT COUNT(*) FROM devices WHERE settings_lock_enabled = 1 AND settings_lock_hash IS NOT NULL AND settings_lock_hash != ''"
    )->fetchColumn();
    $withLogo = (int) $pdo->query(
        "SELECT COUNT(*) FROM devices WHERE logo_file IS NOT NULL AND logo_file != ''"
    )->fetchColumn();
    $withWallpaper = (int) $pdo->query(
        "SELECT COUNT(*) FROM devices WHERE wallpaper_file IS NOT NULL AND wallpaper_file != ''"
    )->fetchColumn();
    $totalApps = (int) $pdo->query(
        'SELECT COUNT(*) FROM device_apps WHERE is_enabled = 1'
    )->fetchColumn();
    $dnsActive = (int) $pdo->query(
        'SELECT COUNT(*) FROM dns_servers WHERE is_active = 1'
    )->fetchColumn();
    $dnsTotal = (int) $pdo->query('SELECT COUNT(*) FROM dns_servers')->fetchColumn();

    $newWeek = (int) $pdo->query(
        "SELECT COUNT(*) FROM devices WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)"
    )->fetchColumn();

    $idleCount = (int) $pdo->query(
        "SELECT COUNT(*) FROM devices
         WHERE last_seen_at IS NOT NULL
           AND last_seen_at < DATE_SUB(NOW(), INTERVAL 1 DAY)
           AND last_seen_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)"
    )->fetchColumn();
    $offlineCount = (int) $pdo->query(
        "SELECT COUNT(*) FROM devices
         WHERE last_seen_at IS NULL OR last_seen_at < DATE_SUB(NOW(), INTERVAL 7 DAY)"
    )->fetchColumn();

    $recentActivity = $pdo->query(
        'SELECT id, label, device_model, last_seen_at, launcher_blocked
         FROM devices
         WHERE last_seen_at IS NOT NULL
         ORDER BY last_seen_at DESC
         LIMIT 6'
    )->fetchAll();

    $healthScore = 100;
    if (!$statsUploads = is_writable($uploadsDir)) {
        $healthScore -= 25;
    }
    if ($dnsActive === 0) {
        $healthScore -= 20;
    }
    if ($totalDevices === 0) {
        $healthScore -= 10;
    }
    if ($blockedCount > 0) {
        $healthScore -= min(15, $blockedCount * 5);
    }

    return [
        'total_devices' => $totalDevices,
        'online_now' => $onlineNow,
        'online_today' => $onlineToday,
        'idle_count' => $idleCount,
        'offline_count' => $offlineCount,
        'active_count' => max(0, $totalDevices - $blockedCount),
        'blocked_count' => $blockedCount,
        'locked_count' => $lockedCount,
        'with_logo' => $withLogo,
        'with_wallpaper' => $withWallpaper,
        'branded_count' => (int) $pdo->query(
            "SELECT COUNT(*) FROM devices WHERE logo_file IS NOT NULL AND logo_file != '' AND wallpaper_file IS NOT NULL AND wallpaper_file != ''"
        )->fetchColumn(),
        'total_apps' => $totalApps,
        'dns_active' => $dnsActive,
        'dns_total' => $dnsTotal,
        'new_week' => $newWeek,
        'db_ok' => true,
        'uploads_writable' => $statsUploads,
        'panel_url' => PANEL_BASE_URL,
        'server_time' => date('d/m/Y H:i:s'),
        'php_version' => PHP_VERSION,
        'health_score' => max(0, $healthScore),
        'recent_activity' => $recentActivity,
    ];
}
