-- Championship Features Database Schema
-- Run this on your Digital Ocean server to add championship tracking

-- Table to track tournament status for each location
CREATE TABLE IF NOT EXISTS `tournament_status` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `tourn_ID` INT(11) NOT NULL,
  `loc_ID` INT(11) NOT NULL,
  `location_name` VARCHAR(255) DEFAULT NULL,
  `region_name` VARCHAR(255) DEFAULT NULL,
  `game_date` DATE NOT NULL,
  `current_round` VARCHAR(10) DEFAULT '1',
  `current_question` INT(11) DEFAULT 1,
  `current_view` VARCHAR(50) DEFAULT 'question',
  
  -- Round completion status
  `round_1_complete` TINYINT(1) DEFAULT 0,
  `round_2_complete` TINYINT(1) DEFAULT 0,
  `round_3_complete` TINYINT(1) DEFAULT 0,
  `halftime_complete` TINYINT(1) DEFAULT 0,
  `halftime_finalized` TINYINT(1) DEFAULT 0,
  `round_4_complete` TINYINT(1) DEFAULT 0,
  `round_5_complete` TINYINT(1) DEFAULT 0,
  `round_6_complete` TINYINT(1) DEFAULT 0,
  `final_complete` TINYINT(1) DEFAULT 0,
  `final_finalized` TINYINT(1) DEFAULT 0,
  
  -- Team counts (for validation)
  `total_teams` INT(11) DEFAULT 0,
  `teams_scored_halftime` INT(11) DEFAULT 0,
  `teams_scored_final` INT(11) DEFAULT 0,
  
  -- Readiness flags
  `ready_for_halftime` TINYINT(1) DEFAULT 0,
  `ready_for_second_half` TINYINT(1) DEFAULT 0,
  `ready_for_final` TINYINT(1) DEFAULT 0,
  `ready_for_results` TINYINT(1) DEFAULT 0,
  
  -- Connection status
  `last_ping` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_online` TINYINT(1) DEFAULT 1,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `tourn_loc_date` (`tourn_ID`, `loc_ID`, `game_date`),
  KEY `tourn_date` (`tourn_ID`, `game_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table for championship notifications/messages
CREATE TABLE IF NOT EXISTS `championship_notifications` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `tourn_ID` INT(11) NOT NULL,
  `from_loc_ID` INT(11) DEFAULT NULL,
  `to_loc_ID` INT(11) DEFAULT NULL, -- NULL means broadcast to all
  `message` TEXT,
  `notification_type` VARCHAR(50) DEFAULT 'info', -- info, warning, success, alert
  `is_read` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `created_by` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tourn_unread` (`tourn_ID`, `is_read`),
  KEY `created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table for championship chat messages (improved chat system)
CREATE TABLE IF NOT EXISTS `championship_chat` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `tourn_ID` INT(11) NOT NULL,
  `room` VARCHAR(50) DEFAULT 'general', -- 'general', 'hq', 'loc_123', etc.
  `user_id` INT(11) NOT NULL,
  `username` VARCHAR(100) NOT NULL,
  `loc_ID` INT(11) DEFAULT NULL,
  `location_name` VARCHAR(255) DEFAULT NULL,
  `message` TEXT NOT NULL,
  `message_type` VARCHAR(20) DEFAULT 'text', -- text, system, alert
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tourn_room_time` (`tourn_ID`, `room`, `created_at`),
  KEY `created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table for tracking who's online in the championship
CREATE TABLE IF NOT EXISTS `championship_presence` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `tourn_ID` INT(11) NOT NULL,
  `user_id` INT(11) NOT NULL,
  `username` VARCHAR(100) NOT NULL,
  `loc_ID` INT(11) NOT NULL,
  `location_name` VARCHAR(255) DEFAULT NULL,
  `last_seen` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_active` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tourn_user` (`tourn_ID`, `user_id`),
  KEY `last_seen` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add championship columns to existing team_scoring table if they don't exist
-- Note: Run each ALTER TABLE separately and ignore errors if column exists

ALTER TABLE `team_scoring` 
ADD COLUMN IF NOT EXISTS `tourn_ID` INT(11) DEFAULT 0 AFTER `team_active`;

ALTER TABLE `team_scoring` 
ADD COLUMN IF NOT EXISTS `H_finalized` CHAR(1) DEFAULT 'N' AFTER `H_in`;

ALTER TABLE `team_scoring` 
ADD COLUMN IF NOT EXISTS `F_finalized` CHAR(1) DEFAULT 'N' AFTER `F_yn`;

-- Index for tournament queries
ALTER TABLE `team_scoring` 
ADD KEY IF NOT EXISTS `tourn_date_loc` (`tourn_ID`, `game_date`, `loc_ID`);
