SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS 
    league_participants, private_leagues,
    notifications, forum_comments, forum_posts, direct_messages,
    chat_messages, fantasy_points, fantasy_lineup_players, 
    fantasy_lineups, fantasy_transfers, fantasy_roster, 
    fantasy_teams, match_events, fixtures, players, clubs, users;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    nickname VARCHAR(100) UNIQUE NOT NULL,
    role ENUM('user', 'admin') DEFAULT 'user',
    tokens DECIMAL(10,2) DEFAULT 1000.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    INDEX idx_email (email),
    INDEX idx_role (role)
);

CREATE TABLE clubs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    short_name VARCHAR(50),
    founded_year INT,
    stadium VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE players (
    id INT PRIMARY KEY AUTO_INCREMENT,
    club_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    position ENUM('GK', 'DF', 'MF', 'FW') NOT NULL,
    price DECIMAL(8,2) NOT NULL DEFAULT 50.00,
    jersey_number INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (club_id) REFERENCES clubs(id) ON DELETE CASCADE,
    INDEX idx_club (club_id),
    INDEX idx_position (position),
    INDEX idx_active (is_active)
);

CREATE TABLE fixtures (
    id INT PRIMARY KEY AUTO_INCREMENT,
    round INT NOT NULL,
    home_club_id INT NOT NULL,
    away_club_id INT NOT NULL,
    home_goals INT DEFAULT NULL,
    away_goals INT DEFAULT NULL,
    status ENUM('scheduled', 'playing', 'finished', 'postponed') DEFAULT 'scheduled',
    played_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (home_club_id) REFERENCES clubs(id) ON DELETE CASCADE,
    FOREIGN KEY (away_club_id) REFERENCES clubs(id) ON DELETE CASCADE,
    INDEX idx_round (round),
    INDEX idx_status (status),
    UNIQUE KEY unique_fixture (round, home_club_id, away_club_id)
);

CREATE TABLE match_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fixture_id INT NOT NULL,
    player_id INT NOT NULL,
    type ENUM('goal', 'assist', 'red_card', 'yellow_card') NOT NULL,
    minute INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fixture_id) REFERENCES fixtures(id) ON DELETE CASCADE,
    FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE,
    INDEX idx_fixture (fixture_id),
    INDEX idx_player (player_id)
);

CREATE TABLE fantasy_teams (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL UNIQUE,
    team_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id)
);

CREATE TABLE fantasy_roster (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fantasy_team_id INT NOT NULL,
    player_id INT NOT NULL,
    bought_price DECIMAL(8,2) NOT NULL,
    bought_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (fantasy_team_id) REFERENCES fantasy_teams(id) ON DELETE CASCADE,
    FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE,
    UNIQUE KEY unique_roster (fantasy_team_id, player_id),
    INDEX idx_team (fantasy_team_id),
    INDEX idx_player (player_id)
);

CREATE TABLE fantasy_transfers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fantasy_team_id INT NOT NULL,
    player_id INT NOT NULL,
    type ENUM('buy', 'sell') NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fantasy_team_id) REFERENCES fantasy_teams(id) ON DELETE CASCADE,
    FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE,
    INDEX idx_team (fantasy_team_id),
    INDEX idx_date (created_at)
);

CREATE TABLE fantasy_lineups (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fantasy_team_id INT NOT NULL,
    round INT NOT NULL,
    formation VARCHAR(10) DEFAULT '1-4-4-2',
    total_points DECIMAL(5,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fantasy_team_id) REFERENCES fantasy_teams(id) ON DELETE CASCADE,
    UNIQUE KEY unique_lineup (fantasy_team_id, round),
    INDEX idx_team_round (fantasy_team_id, round)
);

CREATE TABLE fantasy_lineup_players (
    id INT PRIMARY KEY AUTO_INCREMENT,
    lineup_id INT NOT NULL,
    player_id INT NOT NULL,
    position ENUM('GK', 'DF', 'MF', 'FW') NOT NULL,
    is_captain BOOLEAN DEFAULT FALSE,
    points DECIMAL(5,2) DEFAULT 0,
    FOREIGN KEY (lineup_id) REFERENCES fantasy_lineups(id) ON DELETE CASCADE,
    FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE,
    UNIQUE KEY unique_lineup_player (lineup_id, player_id),
    INDEX idx_lineup (lineup_id)
);

CREATE TABLE fantasy_points (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fantasy_team_id INT NOT NULL,
    round INT NOT NULL,
    total_points DECIMAL(5,2) NOT NULL DEFAULT 0,
    details JSON,
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fantasy_team_id) REFERENCES fantasy_teams(id) ON DELETE CASCADE,
    UNIQUE KEY unique_team_round (fantasy_team_id, round),
    INDEX idx_team (fantasy_team_id),
    INDEX idx_round (round)
);

CREATE TABLE chat_messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_created (created_at)
);

CREATE TABLE direct_messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sender_id INT NOT NULL,
    receiver_id INT NOT NULL,
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_sender (sender_id),
    INDEX idx_receiver (receiver_id),
    INDEX idx_created (created_at)
);

CREATE TABLE forum_posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_created (created_at)
);

CREATE TABLE forum_comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES forum_posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_post (post_id),
    INDEX idx_user (user_id)
);

CREATE TABLE notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type ENUM('message', 'transfer', 'points', 'system') NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_read (is_read)
);

CREATE TABLE private_leagues (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    code VARCHAR(10) UNIQUE NOT NULL,
    created_by INT NOT NULL,
    max_participants INT DEFAULT 20,
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_code (code),
    INDEX idx_created_by (created_by)
);

CREATE TABLE league_participants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    league_id INT NOT NULL,
    user_id INT NOT NULL,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (league_id) REFERENCES private_leagues(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_participant (league_id, user_id),
    INDEX idx_league (league_id),
    INDEX idx_user (user_id)
);