<?php
require_once __DIR__.'/db.php';
$pdo=db();
$queries=[
"CREATE TABLE IF NOT EXISTS booking_game_settings (game_id INT PRIMARY KEY, receipt_routing ENUM('admins','owners') NOT NULL DEFAULT 'admins', card_number VARCHAR(64) NOT NULL DEFAULT '', card_holder VARCHAR(255) NOT NULL DEFAULT '', amount DECIMAL(12,2) NOT NULL DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
"CREATE TABLE IF NOT EXISTS booking_slot_templates (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, game_id INT NOT NULL, weekday TINYINT UNSIGNED NOT NULL DEFAULT 0, slot_time TIME NOT NULL, per_person_amount DECIMAL(12,2) NOT NULL DEFAULT 0, amount DECIMAL(12,2) NOT NULL DEFAULT 0, manual_closed TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_game_weekday_time(game_id,weekday,slot_time), KEY idx_template_game_day(game_id,weekday)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
"CREATE TABLE IF NOT EXISTS booking_slots (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, game_id INT NOT NULL, slot_date DATE NOT NULL, slot_time TIME NOT NULL, per_person_amount DECIMAL(12,2) NOT NULL DEFAULT 0, amount DECIMAL(12,2) NOT NULL DEFAULT 0, manual_closed TINYINT(1) NOT NULL DEFAULT 0, reserved TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_slot(game_id,slot_date,slot_time), KEY idx_game_date(game_id,slot_date)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
"CREATE TABLE IF NOT EXISTS reservations (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, slot_id INT UNSIGNED NOT NULL, game_id INT NOT NULL, user_id BIGINT NOT NULL, players INT UNSIGNED NOT NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, per_person_amount DECIMAL(12,2) NOT NULL DEFAULT 0, booking_code VARCHAR(20) NULL, full_name VARCHAR(255) NOT NULL DEFAULT '', receipt_file_id TEXT, receipt_type VARCHAR(20) NULL, status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending', reject_reason TEXT, handled_by BIGINT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, approved_at DATETIME NULL, rejected_at DATETIME NULL, KEY idx_slot(slot_id), KEY idx_game_status(game_id,status), KEY idx_user(user_id), CONSTRAINT fk_res_slot FOREIGN KEY(slot_id) REFERENCES booking_slots(id) ON DELETE RESTRICT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
];
foreach($queries as $q){try{$pdo->exec($q);}catch(Throwable $e){}}
$migrations=[
"ALTER TABLE booking_slot_templates ADD COLUMN weekday TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER game_id",
"ALTER TABLE booking_slot_templates ADD COLUMN per_person_amount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER slot_time",
"ALTER TABLE booking_slot_templates ADD COLUMN amount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER per_person_amount",
"ALTER TABLE booking_slot_templates ADD COLUMN manual_closed TINYINT(1) NOT NULL DEFAULT 0 AFTER amount",
"ALTER TABLE booking_slots ADD COLUMN slot_date DATE NULL AFTER game_id",
"ALTER TABLE booking_slots ADD COLUMN per_person_amount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER slot_time",
"ALTER TABLE booking_slots ADD COLUMN amount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER per_person_amount",
"ALTER TABLE booking_slots ADD COLUMN manual_closed TINYINT(1) NOT NULL DEFAULT 0 AFTER amount","ALTER TABLE booking_slots ADD COLUMN reserved TINYINT(1) NOT NULL DEFAULT 0 AFTER manual_closed",
"ALTER TABLE reservations ADD COLUMN per_person_amount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER amount",
"ALTER TABLE reservations ADD COLUMN booking_code VARCHAR(20) NULL AFTER per_person_amount",
"ALTER TABLE reservations ADD COLUMN full_name VARCHAR(255) NOT NULL DEFAULT '' AFTER booking_code","ALTER TABLE reservations ADD COLUMN receipt_type VARCHAR(20) NULL AFTER receipt_file_id"
];
foreach($migrations as $q){try{$pdo->exec($q);}catch(Throwable $e){}}
try{$pdo->exec("ALTER TABLE booking_slot_templates DROP INDEX uq_game_time");}catch(Throwable $e){}
try{$pdo->exec("ALTER TABLE booking_slots DROP INDEX uq_game_time");}catch(Throwable $e){}
try{
    $idx=$pdo->query("SELECT DISTINCT INDEX_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='booking_slot_templates' AND NON_UNIQUE=0 AND INDEX_NAME<>'PRIMARY'")->fetchAll(PDO::FETCH_COLUMN);
    foreach($idx as $name){
        $cols=$pdo->prepare("SELECT COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='booking_slot_templates' AND INDEX_NAME=? ORDER BY SEQ_IN_INDEX");
        $cols->execute([$name]); $c=$cols->fetchAll(PDO::FETCH_COLUMN);
        if($name==='uq_game_time' || ($c===['game_id','slot_time'])){
            $safe=preg_replace('/[^A-Za-z0-9_]/','',$name);
            if($safe)$pdo->exec("ALTER TABLE booking_slot_templates DROP INDEX `{$safe}`");
        }
    }
}catch(Throwable $e){}
try{$pdo->exec("DELETE t1 FROM booking_slot_templates t1 INNER JOIN booking_slot_templates t2 ON t1.game_id=t2.game_id AND t1.weekday=t2.weekday AND t1.slot_time=t2.slot_time AND t1.id>t2.id");}catch(Throwable $e){}
try{$pdo->exec("ALTER TABLE booking_slot_templates DROP INDEX uq_game_weekday_time");}catch(Throwable $e){}
try{$pdo->exec("ALTER TABLE booking_slot_templates ADD UNIQUE KEY uq_game_weekday_time(game_id,weekday,slot_time)");}catch(Throwable $e){}
try{$pdo->exec("ALTER TABLE booking_slots DROP INDEX uq_slot");}catch(Throwable $e){} try{$pdo->exec("ALTER TABLE booking_slots ADD UNIQUE KEY uq_slot(game_id,slot_date,slot_time)");}catch(Throwable $e){}
try{$pdo->exec("ALTER TABLE booking_slots ADD KEY idx_game_date(game_id,slot_date)");}catch(Throwable $e){}
echo "OK\n";
