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

-- Houses
CREATE TABLE IF NOT EXISTS houses (
  house_code CHAR(2) PRIMARY KEY,
  house_name VARCHAR(64) NOT NULL
);

INSERT INTO houses (house_code, house_name) VALUES
('FR','Freyberg') ON DUPLICATE KEY UPDATE house_name=VALUES(house_name),
('HB','Halberg')  ON DUPLICATE KEY UPDATE house_name=VALUES(house_name),
('HL','Hillary')  ON DUPLICATE KEY UPDATE house_name=VALUES(house_name),
('NR','Ngarimu')  ON DUPLICATE KEY UPDATE house_name=VALUES(house_name),
('NG','Ngata')    ON DUPLICATE KEY UPDATE house_name=VALUES(house_name),
('RF','Rutherford') ON DUPLICATE KEY UPDATE house_name=VALUES(house_name);

-- Students
CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_code VARCHAR(32) NULL,
  first_name VARCHAR(64) NOT NULL,
  last_name VARCHAR(64) NOT NULL,
  house_code CHAR(2) NOT NULL,
  year_level TINYINT NOT NULL,
  dob DATE NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (last_name), INDEX (house_code), INDEX (year_level),
  CONSTRAINT fk_students_house FOREIGN KEY (house_code) REFERENCES houses(house_code)
);

-- Events (definitions)
CREATE TABLE IF NOT EXISTS events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64) NOT NULL,
  category ENUM('Track','Field','Relay') NOT NULL,
  notes VARCHAR(255) NULL
);

-- Scheduled events (the actual instances for the day)
CREATE TABLE IF NOT EXISTS scheduled_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_id INT NOT NULL,
  division ENUM('Competitive','Participation') NOT NULL,
  year_min TINYINT NULL,
  year_max TINYINT NULL,
  year_list VARCHAR(32) NULL,          -- optional CSV (e.g. '9,10')
  is_team TINYINT(1) DEFAULT 0,        -- 4x100 is team event
  location VARCHAR(128) NULL,
  start_time DATETIME NULL,
  day_date DATE NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (division), INDEX (day_date),
  CONSTRAINT fk_sched_event FOREIGN KEY (event_id) REFERENCES events(id)
);

-- Participation entries
CREATE TABLE IF NOT EXISTS participation_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sched_id INT NOT NULL,
  student_id INT NOT NULL,
  finalist TINYINT(1) DEFAULT 0,
  created_by VARCHAR(64) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_sched_student (sched_id, student_id),
  CONSTRAINT fk_part_sched FOREIGN KEY (sched_id) REFERENCES scheduled_events(id),
  CONSTRAINT fk_part_student FOREIGN KEY (student_id) REFERENCES students(id)
);

-- Competitive results
CREATE TABLE IF NOT EXISTS competitive_results (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sched_id INT NOT NULL,
  student_id INT NULL,
  house_code CHAR(2) NULL,             -- team events like 4x100
  result_value DECIMAL(10,3) NOT NULL, -- time in seconds OR distance in meters
  result_unit ENUM('s','m') NOT NULL,
  place INT NULL,
  heat INT NULL,
  round ENUM('Heat','Final') DEFAULT 'Final',
  status ENUM('OK','DQ','DNS') DEFAULT 'OK',
  wind DECIMAL(4,1) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (sched_id), INDEX (student_id), INDEX (house_code),
  CONSTRAINT fk_comp_sched FOREIGN KEY (sched_id) REFERENCES scheduled_events(id),
  CONSTRAINT fk_comp_student FOREIGN KEY (student_id) REFERENCES students(id),
  CONSTRAINT fk_comp_house FOREIGN KEY (house_code) REFERENCES houses(house_code)
);

-- Seed events
INSERT INTO events (id,name,category) VALUES
(1,'100m','Track'),(2,'200m','Track'),(3,'300m/400m','Track'),(4,'800m','Track'),
(5,'1500m','Track'),(6,'3000m','Track'),(7,'Long Jump','Field'),(8,'Triple Jump','Field'),
(9,'High Jump','Field'),(10,'Shot Put','Field'),(11,'Discus','Field'),(12,'Javelin','Field'),
(13,'4 x 100m','Relay')
ON DUPLICATE KEY UPDATE name=VALUES(name), category=VALUES(category);

-- Example scheduled events (adjust year_list as needed)
INSERT INTO scheduled_events (event_id, division, year_list, is_team, day_date) VALUES
(1,'Competitive','9,10',0,CURDATE()),
(1,'Competitive','11,12,13',0,CURDATE()),
(13,'Competitive','9,10',1,CURDATE()),
(13,'Competitive','11,12,13',1,CURDATE()),
(4,'Participation','9,10,11,12,13',0,CURDATE())
ON DUPLICATE KEY UPDATE division=VALUES(division);
