-- Create database if it doesn't exist
CREATE DATABASE IF NOT EXISTS betting_prizes
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;

USE betting_prizes;

-- DROP and recreate tables
DROP TABLE IF EXISTS PayoutDeco;
DROP TABLE IF EXISTS TournamentDeco;

CREATE TABLE `TournamentDeco` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `CompetitionId` varchar(64) NOT NULL,
  `Sport` varchar(64) NOT NULL,
  `InitialPool` float NOT NULL,
  `TicketPrice` float NOT NULL,
  `TicketsSold` int NOT NULL,
  `PrizeCount` int NOT NULL,
  `CreatedAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `UpdatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `idx_competition_id` (`CompetitionId`),
  KEY `idx_sport` (`Sport`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `PayoutDeco` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `CompetitionId` varchar(64) NOT NULL,
  `Rank` int NOT NULL,
  `Amount` float NOT NULL,
  `Tier` varchar(32) DEFAULT NULL,
  `Sport` varchar(64) NOT NULL,
  `CreatedAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `UpdatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `idx_competition_id` (`CompetitionId`),
  KEY `idx_sport` (`Sport`),
  KEY `idx_rank` (`Rank`),
  KEY `idx_competition_rank` (`CompetitionId`, `Rank`)
) ENGINE=InnoDB AUTO_INCREMENT=941 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; 