<?php
require_once '../core/bootstrap.php';

// --- Security Check ---
if (!isset($_SESSION['user_id']) || !in_array($_SESSION['user_role'] ?? '', ['super_admin', 'admin'])) {
    die('Access Denied. You must be logged in as an Admin to run this script.');
}

$db = new Database();
echo "<h1>Installing Fantasy Picks & Stats Tables</h1>";

try {
    $sql = "CREATE TABLE IF NOT EXISTS `fantasy_picks` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `fantasy_team_id` int(11) NOT NULL,
      `player_id` int(11) NOT NULL,
      `gameweek` int(11) NOT NULL,
      `is_starter` tinyint(1) NOT NULL DEFAULT 0,
      `is_captain` tinyint(1) NOT NULL DEFAULT 0,
      `is_vice_captain` tinyint(1) NOT NULL DEFAULT 0,
      `bench_order` int(11) DEFAULT NULL,
      `is_auto_sub` tinyint(1) NOT NULL DEFAULT 0,
      `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
      PRIMARY KEY (`id`),
      KEY `fantasy_team_id` (`fantasy_team_id`),
      KEY `player_id` (`player_id`),
      KEY `gameweek` (`gameweek`),
      UNIQUE KEY `team_player_gw` (`fantasy_team_id`, `player_id`, `gameweek`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    
    $db->query($sql);
    $db->execute();
    echo "Success: Created 'fantasy_picks' table.<br>";
} catch (Exception $e) {
    echo "Error creating 'fantasy_picks': " . $e->getMessage() . "<br>";
}

echo "<h3>Checking 'fantasy_player_stats' table...</h3>";
try {
    $sql = "CREATE TABLE IF NOT EXISTS `fantasy_player_stats` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `player_id` int(11) NOT NULL,
      `gameweek` int(11) NOT NULL,
      `minutes_played` int(11) DEFAULT 0,
      `goals_scored` int(11) DEFAULT 0,
      `assists` int(11) DEFAULT 0,
      `clean_sheets` int(11) DEFAULT 0,
      `goals_conceded` int(11) DEFAULT 0,
      `own_goals` int(11) DEFAULT 0,
      `penalties_saved` int(11) DEFAULT 0,
      `penalties_missed` int(11) DEFAULT 0,
      `yellow_cards` int(11) DEFAULT 0,
      `red_cards` int(11) DEFAULT 0,
      `saves` int(11) DEFAULT 0,
      `bonus` int(11) DEFAULT 0,
      `total_points` int(11) DEFAULT 0,
      `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
      PRIMARY KEY (`id`),
      KEY `player_id` (`player_id`),
      KEY `gameweek` (`gameweek`),
      UNIQUE KEY `player_gw` (`player_id`, `gameweek`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

    $db->query($sql);
    $db->execute();
    echo "Success: Created 'fantasy_player_stats' table.<br>";
} catch (Exception $e) {
    echo "Error creating 'fantasy_player_stats': " . $e->getMessage() . "<br>";
}

echo "<br><strong>Installation Complete.</strong>";
?>