<?php

$host = 'localhost';
$db   = 'logicfas_escape_bot';
$user = 'logicfas_escape_user';
$pass =  '#Shuter0099';

try {
    $pdo = new PDO(
        "mysql:host=$host;dbname=$db;charset=utf8mb4",
        $user,
        $pass,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
    );

    $pdo->exec("SET NAMES utf8mb4");

    $sql = [

        // سانس‌ها
        "
        CREATE TABLE IF NOT EXISTS booking_slots (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            game_id VARCHAR(100) NOT NULL,
            slot_date DATE NOT NULL,
            slot_time TIME NOT NULL,

            manual_closed TINYINT(1) NOT NULL DEFAULT 0,

            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP,

            UNIQUE KEY unique_game_date_time (game_id, slot_date, slot_time),
            INDEX idx_game_date (game_id, slot_date)
        ) ENGINE=InnoDB
        DEFAULT CHARSET=utf8mb4
        COLLATE=utf8mb4_unicode_ci
        ",

        // رزروها
        "
        CREATE TABLE IF NOT EXISTS reservations (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

            slot_id INT UNSIGNED NOT NULL,
            game_id VARCHAR(100) NOT NULL,

            user_id BIGINT NOT NULL,
            phone VARCHAR(30) DEFAULT NULL,

            players INT UNSIGNED NOT NULL,

            amount DECIMAL(12,2) NOT NULL DEFAULT 0,

            receipt_file_id TEXT DEFAULT NULL,

            status ENUM(
                'pending',
                'approved',
                'rejected'
            ) NOT NULL DEFAULT 'pending',

            reject_reason TEXT DEFAULT NULL,

            handled_by BIGINT DEFAULT NULL,

            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP,

            approved_at DATETIME DEFAULT NULL,
            rejected_at DATETIME DEFAULT NULL,

            INDEX idx_slot (slot_id),
            INDEX idx_user (user_id),
            INDEX idx_status (status),
            INDEX idx_game (game_id),

            CONSTRAINT fk_reservation_slot
                FOREIGN KEY (slot_id)
                REFERENCES booking_slots(id)
                ON DELETE RESTRICT
                ON UPDATE CASCADE
        ) ENGINE=InnoDB
        DEFAULT CHARSET=utf8mb4
        COLLATE=utf8mb4_unicode_ci
        ",

        // لاگ تغییر وضعیت سانس
        "
        CREATE TABLE IF NOT EXISTS booking_slot_logs (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

            slot_id INT UNSIGNED NOT NULL,

            action ENUM(
                'created',
                'manual_open',
                'manual_close',
                'reserved',
                'reservation_rejected',
                'reservation_cancelled'
            ) NOT NULL,

            user_id BIGINT DEFAULT NULL,

            description TEXT DEFAULT NULL,

            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

            INDEX idx_slot_logs (slot_id),
            INDEX idx_created_at (created_at),

            CONSTRAINT fk_slot_log_slot
                FOREIGN KEY (slot_id)
                REFERENCES booking_slots(id)
                ON DELETE CASCADE
                ON UPDATE CASCADE
        ) ENGINE=InnoDB
        DEFAULT CHARSET=utf8mb4
        COLLATE=utf8mb4_unicode_ci
        "
    ];

    foreach ($sql as $query) {
        $pdo->exec($query);
    }

    echo '<h2>✅ Booking Database Installed Successfully</h2>';
    echo '<p>booking_slots: OK</p>';
    echo '<p>reservations: OK</p>';
    echo '<p>booking_slot_logs: OK</p>';

} catch (Throwable $e) {

    http_response_code(500);

    echo '<h2>❌ Database Error</h2>';
    echo '<pre>';
    echo htmlspecialchars($e->getMessage(), ENT_QUOTES, 'UTF-8');
    echo '</pre>';
}