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

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','viewer') NOT NULL DEFAULT 'admin',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE teams (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  ward VARCHAR(150) NOT NULL,
  subcounty VARCHAR(150) NOT NULL,
  category ENUM('male','female') NOT NULL DEFAULT 'male',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE fixtures (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team1_id INT NOT NULL,
  team2_id INT NOT NULL,
  fixture_date DATE,
  fixture_time TIME,
  ward VARCHAR(150),
  subcounty VARCHAR(150),
  stage ENUM('prelim','locational','ward','interward','subcounty','county') DEFAULT 'prelim',
  category ENUM('male','female') NOT NULL DEFAULT 'male',
  is_final TINYINT(1) DEFAULT 0,
  status ENUM('scheduled','played') DEFAULT 'scheduled',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (team1_id) REFERENCES teams(id) ON DELETE CASCADE,
  FOREIGN KEY (team2_id) REFERENCES teams(id) ON DELETE CASCADE
);

CREATE TABLE results (
  id INT AUTO_INCREMENT PRIMARY KEY,
  fixture_id INT NOT NULL,
  score1 SMALLINT,
  score2 SMALLINT,
  winner_id INT,
  played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fixture_id) REFERENCES fixtures(id) ON DELETE CASCADE,
  FOREIGN KEY (winner_id) REFERENCES teams(id) ON DELETE SET NULL
);

CREATE TABLE champions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  level ENUM('ward','subcounty','county') NOT NULL,
  level_name VARCHAR(150) NOT NULL,
  team_id INT NOT NULL,
  season VARCHAR(20) DEFAULT '2025',
  crowned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
);

CREATE TABLE site_content (
  id INT AUTO_INCREMENT PRIMARY KEY,
  content_key VARCHAR(100) UNIQUE,
  content_value TEXT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE team_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_id INT NOT NULL,
  season VARCHAR(10) NOT NULL,
  level ENUM('subcounty','county') NOT NULL,
  note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
);

CREATE TABLE polls (
  id INT AUTO_INCREMENT PRIMARY KEY,
  question VARCHAR(255),
  options JSON,
  votes JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

/* Sample data: teams and fixtures */
INSERT INTO teams (name, ward, subcounty, category) VALUES
('Iten Stars','Emsoo','Keiyo North','male'),
('Marakwet United','Kapchemutwa','Marakwet West','male'),
('Kapsowar Rovers','Kapsowar Ward','Keiyo South','male'),
('Tambach Warriors','Tambach','Keiyo South','male'),
('Iten Queens','Emsoo','Keiyo North','female'),
('Marakwet Ladies','Kapchemutwa','Marakwet West','female'),
('Kapsowar Starlets','Kapsowar Ward','Keiyo South','female'),
('Tambach Angels','Tambach','Keiyo South','female');

INSERT INTO fixtures (team1_id,team2_id,fixture_date,fixture_time,ward,subcounty,stage,category,is_final,status) VALUES
(1,2,'2025-09-25','15:00','Emsoo','Keiyo North','ward','male',0,'scheduled'),
(3,4,'2025-09-26','16:00','Kapsowar Ward','Keiyo South','ward','male',0,'scheduled'),
(5,6,'2025-09-25','13:00','Emsoo','Keiyo North','ward','female',0,'scheduled'),
(7,8,'2025-09-26','14:00','Kapsowar Ward','Keiyo South','ward','female',0,'scheduled');

