<?php
/**
 * Script de instalación - ejecutar UNA vez para crear las tablas.
 * Luego eliminar o proteger este archivo.
 */
require_once __DIR__ . '/db.php';

$errors = [];
$success = [];

try {
    $pdo = db();

    // Tabla de usuarios web
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS users (
            id           INT AUTO_INCREMENT PRIMARY KEY,
            username     VARCHAR(50)  NOT NULL UNIQUE,
            password     VARCHAR(255) NOT NULL,
            display_name VARCHAR(100) NOT NULL,
            email        VARCHAR(150) DEFAULT NULL,
            role         ENUM('admin','client') NOT NULL DEFAULT 'client',
            active       TINYINT(1) NOT NULL DEFAULT 1,
            created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_login   TIMESTAMP NULL,
            INDEX idx_username (username)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
    $success[] = 'Tabla users creada.';

    // Mapeo: usuarios web → client_ids de AdGuard (un usuario puede tener varios clientes)
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS client_mapping (
            id                INT AUTO_INCREMENT PRIMARY KEY,
            user_id           INT NOT NULL,
            adguard_client_id VARCHAR(100) NOT NULL,
            label             VARCHAR(100) DEFAULT NULL,
            created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uk_user_client (user_id, adguard_client_id),
            INDEX idx_client_id (adguard_client_id),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
    $success[] = 'Tabla client_mapping creada.';

    // Registros de consultas DNS sincronizados desde AdGuard
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS query_logs (
            id                 BIGINT AUTO_INCREMENT PRIMARY KEY,
            record_hash        CHAR(32)     NOT NULL,
            adguard_client_id  VARCHAR(100) NOT NULL,
            client_name        VARCHAR(100) DEFAULT NULL,
            domain             VARCHAR(255) NOT NULL,
            query_type         VARCHAR(10)  DEFAULT 'A',
            reason             VARCHAR(50)  NOT NULL,
            filter_id          INT          DEFAULT NULL,
            filter_name        VARCHAR(255) DEFAULT NULL,
            category           VARCHAR(50)  DEFAULT NULL,
            category_group     VARCHAR(50)  DEFAULT NULL,
            category_color     VARCHAR(7)   DEFAULT NULL,
            rule_text          TEXT         DEFAULT NULL,
            status             VARCHAR(20)  DEFAULT NULL,
            elapsed_ms         FLOAT        DEFAULT NULL,
            log_time           DATETIME(3)  NOT NULL,
            fetched_at         TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uk_hash (record_hash),
            INDEX idx_client  (adguard_client_id),
            INDEX idx_time    (log_time),
            INDEX idx_reason  (reason),
            INDEX idx_category(category),
            INDEX idx_domain  (domain(100))
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
    $success[] = 'Tabla query_logs creada.';

    // Log de sincronizaciones
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS sync_log (
            id              INT AUTO_INCREMENT PRIMARY KEY,
            started_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            finished_at     TIMESTAMP NULL,
            records_fetched INT       NOT NULL DEFAULT 0,
            records_new     INT       NOT NULL DEFAULT 0,
            oldest_record   DATETIME  NULL,
            newest_record   DATETIME  NULL,
            status          ENUM('running','ok','error') NOT NULL DEFAULT 'running',
            error_message   TEXT      NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
    $success[] = 'Tabla sync_log creada.';

    // Estado de cursor de sincronización
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS sync_state (
            id            INT AUTO_INCREMENT PRIMARY KEY,
            state_key     VARCHAR(50) NOT NULL UNIQUE,
            state_value   TEXT        NOT NULL,
            updated_at    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
    $success[] = 'Tabla sync_state creada.';

    // Crear usuario administrador por defecto
    $adminExists = $pdo->query("SELECT COUNT(*) FROM users WHERE role='admin'")->fetchColumn();
    if (!$adminExists) {
        $hash = password_hash('admin123', PASSWORD_DEFAULT);
        $stmt = $pdo->prepare("INSERT INTO users (username, password, display_name, role) VALUES (?, ?, ?, 'admin')");
        $stmt->execute(['admin', $hash, 'Administrador PCASSI']);
        $success[] = 'Usuario admin creado (usuario: admin / clave: admin123). ¡Cambiar la clave inmediatamente!';
    } else {
        $success[] = 'Usuario admin ya existe.';
    }

} catch (Exception $e) {
    $errors[] = 'Error: ' . $e->getMessage();
}
?>
<!DOCTYPE html>
<html lang="es">
<head>
    <meta charset="UTF-8">
    <title>Instalación - ZeroTrust Shield</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css">
</head>
<body class="bg-light">
<div class="container py-5" style="max-width:600px">
    <div class="card shadow">
        <div class="card-body p-4">
            <h3 class="mb-4">🛡️ Instalación de ZeroTrust Shield</h3>
            <?php foreach ($errors as $e): ?>
                <div class="alert alert-danger"><?= htmlspecialchars($e) ?></div>
            <?php endforeach; ?>
            <?php foreach ($success as $s): ?>
                <div class="alert alert-success"><?= htmlspecialchars($s) ?></div>
            <?php endforeach; ?>
            <?php if (empty($errors)): ?>
                <div class="alert alert-warning mt-3">
                    <strong>¡Importante!</strong> Elimine o proteja este archivo <code>install.php</code> luego de la instalación.
                </div>
                <a href="index.php" class="btn btn-primary">Ir al Login →</a>
            <?php endif; ?>
        </div>
    </div>
</div>
</body>
</html>
