-- ============================================
-- DATABASE: padel_match_system
-- ============================================

CREATE DATABASE IF NOT EXISTS padel_match_system DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE padel_match_system;

-- ============================================
-- TABLE: users (Admin Authentication)
-- ============================================
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'viewer') DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: players (Cadastro de Jogadores)
-- ============================================
CREATE TABLE players (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    level ENUM('iniciante', 'intermediario', 'avancado') NOT NULL DEFAULT 'iniciante',
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    profile_image_url VARCHAR(255),
    status ENUM('ativo', 'inativo') DEFAULT 'ativo',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_level (level),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: matches (Criação e Histórico de Jogos)
-- ============================================
CREATE TABLE matches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_date DATE NOT NULL,
    match_time TIME NOT NULL,
    court_name VARCHAR(100),
    status ENUM('agendado', 'concluido', 'cancelado') DEFAULT 'agendado',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_match_date (match_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: match_teams (Duplas por Jogo)
-- ============================================
CREATE TABLE match_teams (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    team_name VARCHAR(50), -- Opcional, pode ser 'Dupla 1', 'Dupla 2'
    player1_id INT NOT NULL,
    player2_id INT NOT NULL,
    team_number INT NOT NULL, -- 1 or 2
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
    FOREIGN KEY (player1_id) REFERENCES players(id) ON DELETE CASCADE,
    FOREIGN KEY (player2_id) REFERENCES players(id) ON DELETE CASCADE,
    UNIQUE KEY unique_team (match_id, team_number),
    INDEX idx_match_id (match_id),
    INDEX idx_player1_id (player1_id),
    INDEX idx_player2_id (player2_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: match_scores (Placar por Set)
-- ============================================
CREATE TABLE match_scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    set_number INT NOT NULL,
    team1_games INT NOT NULL DEFAULT 0,
    team2_games INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
    UNIQUE KEY unique_set (match_id, set_number),
    INDEX idx_match_id (match_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: match_results (Resultado Final do Jogo)
-- ============================================
CREATE TABLE match_results (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    winning_team_id INT NOT NULL, -- FK para match_teams(id)
    total_sets INT NOT NULL,
    sets_won_team1 INT NOT NULL,
    sets_won_team2 INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
    FOREIGN KEY (winning_team_id) REFERENCES match_teams(id) ON DELETE CASCADE,
    UNIQUE KEY unique_result (match_id),
    INDEX idx_match_id (match_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: player_statistics (Estatísticas por Jogador)
-- Esta tabela é atualizada após a conclusão de cada jogo.
-- ============================================
CREATE TABLE player_statistics (
    id INT PRIMARY KEY AUTO_INCREMENT,
    player_id INT NOT NULL,
    total_matches INT DEFAULT 0,
    total_wins INT DEFAULT 0,
    total_losses INT DEFAULT 0,
    win_rate DECIMAL(5, 2) DEFAULT 0.00,
    last_match_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE,
    UNIQUE KEY unique_player_stats (player_id),
    INDEX idx_player_id (player_id),
    INDEX idx_win_rate (win_rate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- INDEXES DE PERFORMANCE
-- ============================================
CREATE INDEX idx_matches_date_time ON matches(match_date, match_time);
CREATE INDEX idx_scores_match ON match_scores(match_id, set_number);
CREATE INDEX idx_results_winning_team ON match_results(winning_team_id);