<?php
require_once __DIR__ . '/config.php';

try {
    $pdo = db();
    echo "Connected to database successfully.\n";

    $commands = [
        'CREATE TABLE IF NOT EXISTS players (
            id INT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            team VARCHAR(255),
            sport VARCHAR(255),
            image_url VARCHAR(255),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;',
        'CREATE TABLE IF NOT EXISTS player_favorites (
            player_id INT PRIMARY KEY,
            favorite_count INT DEFAULT 1 NOT NULL,
            FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
        ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;',
        'CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) NOT NULL UNIQUE,
            email VARCHAR(255) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL,
            role ENUM("regular", "sports_analyst", "sports_bettor", "admin") NOT NULL DEFAULT "regular",
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;',
        'CREATE TABLE IF NOT EXISTS user_favorites (
            user_id INT NOT NULL,
            player_id INT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (user_id, player_id),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
        ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
    ];

    foreach ($commands as $command) {
        $pdo->exec($command);
    }

    echo "Tables created successfully.\n";

} catch (PDOException $e) {
    die("Database setup failed: " . $e->getMessage());
}