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

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) DEFAULT NULL,
    role ENUM('super_admin','admin','manager','referee','scorer','viewer') NOT NULL DEFAULT 'scorer',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE tournaments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    status ENUM('draft','active','paused','finished','archived') NOT NULL DEFAULT 'draft',
    match_format ENUM('singles','doubles','mixed_doubles','custom') NOT NULL DEFAULT 'doubles',
    best_of TINYINT UNSIGNED NOT NULL DEFAULT 3,
    points_to_win TINYINT UNSIGNED NOT NULL DEFAULT 11,
    win_by_two TINYINT(1) NOT NULL DEFAULT 1,
    final_set_points TINYINT UNSIGNED DEFAULT 15,
    start_date DATE NULL,
    end_date DATE NULL,
    location VARCHAR(255) DEFAULT NULL,
    organizer_name VARCHAR(100) DEFAULT NULL,
    organizer_contact VARCHAR(100) DEFAULT NULL,
    overlay_theme VARCHAR(100) DEFAULT 'royalbluegold',
    primary_color VARCHAR(20) DEFAULT '#0f172a',
    accent_color VARCHAR(20) DEFAULT '#d4af37',
    logo_path VARCHAR(255) DEFAULT NULL,
    sponsors TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE courts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tournament_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    sort_order INT NOT NULL DEFAULT 1,
    is_live TINYINT(1) NOT NULL DEFAULT 1,
    stream_url VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_courts_tournament FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE tournament_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tournament_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    role ENUM('admin','manager','referee','scorer','viewer') NOT NULL DEFAULT 'scorer',
    assigned_court_id INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_tournament_user (tournament_id, user_id),
    CONSTRAINT fk_tu_tournament FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE,
    CONSTRAINT fk_tu_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE teams (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tournament_id INT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    short_name VARCHAR(50) DEFAULT NULL,
    logo_path VARCHAR(255) DEFAULT NULL,
    seed_no INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_teams_tournament FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE players (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tournament_id INT UNSIGNED NOT NULL,
    team_id INT UNSIGNED NULL,
    full_name VARCHAR(255) NOT NULL,
    gender ENUM('male','female','other') DEFAULT NULL,
    photo_path VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(30) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_players_tournament FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE,
    CONSTRAINT fk_players_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE team_players (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    team_id INT UNSIGNED NOT NULL,
    player_id INT UNSIGNED NOT NULL,
    player_order TINYINT UNSIGNED NOT NULL DEFAULT 1,
    UNIQUE KEY uniq_team_player (team_id, player_id),
    CONSTRAINT fk_tp_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
    CONSTRAINT fk_tp_player FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE matches (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tournament_id INT UNSIGNED NOT NULL,
    court_id INT UNSIGNED NULL,
    match_no VARCHAR(50) DEFAULT NULL,
    stage VARCHAR(100) DEFAULT NULL,
    round_name VARCHAR(100) DEFAULT NULL,
    team1_id INT UNSIGNED NOT NULL,
    team2_id INT UNSIGNED NOT NULL,
    team1_name_snapshot VARCHAR(255) DEFAULT NULL,
    team2_name_snapshot VARCHAR(255) DEFAULT NULL,
    status ENUM('scheduled','ready','live','paused','completed','cancelled','disputed') NOT NULL DEFAULT 'scheduled',
    format_type ENUM('singles','doubles') NOT NULL DEFAULT 'doubles',
    best_of TINYINT UNSIGNED NOT NULL DEFAULT 3,
    points_to_win TINYINT UNSIGNED NOT NULL DEFAULT 11,
    final_set_points TINYINT UNSIGNED DEFAULT 15,
    win_by_two TINYINT(1) NOT NULL DEFAULT 1,
    current_set_no TINYINT UNSIGNED NOT NULL DEFAULT 1,
    current_server_team_no TINYINT UNSIGNED NOT NULL DEFAULT 1,
    current_server_no TINYINT UNSIGNED NOT NULL DEFAULT 1,
    starting_server_team_no TINYINT UNSIGNED NOT NULL DEFAULT 1,
    starting_side ENUM('left','right') DEFAULT 'left',
    winner_team_id INT UNSIGNED NULL,
    scheduled_at DATETIME NULL,
    started_at DATETIME NULL,
    ended_at DATETIME NULL,
    youtube_link VARCHAR(255) DEFAULT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_matches_tournament FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE,
    CONSTRAINT fk_matches_court FOREIGN KEY (court_id) REFERENCES courts(id) ON DELETE SET NULL,
    CONSTRAINT fk_matches_team1 FOREIGN KEY (team1_id) REFERENCES teams(id) ON DELETE RESTRICT,
    CONSTRAINT fk_matches_team2 FOREIGN KEY (team2_id) REFERENCES teams(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE match_sets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    match_id INT UNSIGNED NOT NULL,
    set_no TINYINT UNSIGNED NOT NULL,
    team1_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    team2_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    winner_team_id INT UNSIGNED NULL,
    is_completed TINYINT(1) NOT NULL DEFAULT 0,
    started_at DATETIME NULL,
    ended_at DATETIME NULL,
    UNIQUE KEY uniq_match_set (match_id, set_no),
    CONSTRAINT fk_sets_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE match_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    match_id INT UNSIGNED NOT NULL,
    set_no TINYINT UNSIGNED NOT NULL,
    event_type ENUM('match_started','rally_won','point_awarded','side_out','server_changed','server_swapped','timeout','medical_timeout','technical_timeout','undo','correction','set_completed','match_completed','note') NOT NULL,
    scoring_team_no TINYINT UNSIGNED NULL,
    acting_user_id INT UNSIGNED NULL,
    server_team_no TINYINT UNSIGNED NULL,
    server_no TINYINT UNSIGNED NULL,
    team1_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    team2_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    payload_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_match_events_match (match_id),
    CONSTRAINT fk_events_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
    CONSTRAINT fk_events_user FOREIGN KEY (acting_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    tournament_id INT UNSIGNED NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT UNSIGNED NULL,
    action VARCHAR(50) NOT NULL,
    old_values_json JSON NULL,
    new_values_json JSON NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
