-- Royal Pass Seasons Table
-- Run this SQL to create the seasons table

CREATE TABLE IF NOT EXISTS `rp_seasons` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `season_name` VARCHAR(64) NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE DEFAULT NULL,
    `is_active` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_active` (`is_active`),
    INDEX `idx_dates` (`start_date`, `end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert first season (current active season)
INSERT INTO rp_seasons (season_name, start_date, is_active) VALUES
('SEZON 1', '2025-01-01', 1);

-- Add season_id column to rp_teams if it doesn't exist
ALTER TABLE rp_teams ADD COLUMN IF NOT EXISTS `season_id` INT DEFAULT 1 AFTER `id`;
ALTER TABLE rp_teams ADD INDEX IF NOT EXISTS `idx_season` (`season_id`);

-- Archive table for past season teams
CREATE TABLE IF NOT EXISTS `rp_teams_archive` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `season_id` INT NOT NULL,
    `team_uid` INT NOT NULL,
    `team_name` VARCHAR(32) NOT NULL,
    `final_level` INT DEFAULT 1,
    `final_members` INT DEFAULT 1,
    `leader_uid` INT DEFAULT 0,
    `leader_name` VARCHAR(32) DEFAULT '',
    `final_points` INT DEFAULT 0,
    `final_rank` INT DEFAULT 0,
    `archived_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_season` (`season_id`),
    INDEX `idx_rank` (`season_id`, `final_rank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Archive table for past season players
CREATE TABLE IF NOT EXISTS `rp_players_archive` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `season_id` INT NOT NULL,
    `player_id` INT NOT NULL,
    `authid` VARCHAR(35) NOT NULL,
    `name` VARCHAR(32) NOT NULL,
    `final_points` INT DEFAULT 0,
    `final_rank` INT DEFAULT 0,
    `archived_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_season` (`season_id`),
    INDEX `idx_rank` (`season_id`, `final_rank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;