<?php
// Run this file once to set up the database
$host = 'localhost';
$username = 'wexctmco_zitvote';
$password = 'ZITVOTE@2025';

try {
    $pdo = new PDO("mysql:host=$host", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Create database
    $pdo->exec("CREATE DATABASE IF NOT EXISTS wexctmco_zitvote");
    $pdo->exec("USE wexctmco_zitvote");
    
    // Create tables
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) UNIQUE NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            password VARCHAR(255) NOT NULL,
            phone VARCHAR(20),
            location VARCHAR(100),
            profile_photo VARCHAR(255),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS positions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(50) NOT NULL,
            code VARCHAR(3) NOT NULL
        )
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS players (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            position_id INT,
            team VARCHAR(100),
            photo VARCHAR(255),
            game_week INT,
            is_active BOOLEAN DEFAULT TRUE,
            FOREIGN KEY (position_id) REFERENCES positions(id)
        )
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS coaches (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            team VARCHAR(100),
            photo VARCHAR(255),
            game_week INT,
            is_active BOOLEAN DEFAULT TRUE
        )
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS votes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT,
            player_id INT,
            coach_id INT,
            game_week INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id),
            FOREIGN KEY (player_id) REFERENCES players(id),
            FOREIGN KEY (coach_id) REFERENCES coaches(id),
            UNIQUE KEY unique_user_gameweek (user_id, game_week)
        )
    ");
    
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS voting_periods (
            id INT AUTO_INCREMENT PRIMARY KEY,
            game_week INT,
            start_date DATETIME,
            end_date DATETIME,
            is_active BOOLEAN DEFAULT FALSE
        )
    ");
    
    // Insert positions
    $pdo->exec("INSERT IGNORE INTO positions (name, code) VALUES 
        ('Goalkeeper', 'GK'),
        ('Defender', 'DEF'),
        ('Midfielder', 'MID'),
        ('Forward', 'FWD')");
    
    // Insert sample voting period
    $pdo->exec("INSERT IGNORE INTO voting_periods (game_week, start_date, end_date, is_active) VALUES 
        (1, NOW(), DATE_ADD(NOW(), INTERVAL 7 DAY), TRUE)");
    
    echo "Database setup completed successfully!";
    echo "<br><a href='index.php'>Go to Website</a>";
    
} catch(PDOException $e) {
    die("Database setup failed: " . $e->getMessage());
}
?>