schema.sql

-- Enable UUID extension
create extension if not exists "uuid-ossp";

-- Create tables
create table if not exists students (
  id uuid default uuid_generate_v4() primary key,
  full_name text not null,
  student_id text unique not null,
  belt_rank text not null,
  dojo text not null,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

create table if not exists events (
  id uuid default uuid_generate_v4() primary key,
  name text not null,
  date date not null,
  location text not null,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

create table if not exists participations (
  id uuid default uuid_generate_v4() primary key,
  student_id uuid references students(id) on delete cascade not null,
  event_id uuid references events(id) on delete cascade not null,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  unique(student_id, event_id)
);

create table if not exists results (
  id uuid default uuid_generate_v4() primary key,
  participation_id uuid references participations(id) on delete cascade not null,
  category text not null, -- e.g., 'Individual Kata', 'Team Kumite'
  medal text check (medal in ('GOLD', 'SILVER', 'BRONZE', 'PARTICIPATION')),
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Seed Data

-- Events
insert into events (name, date, location) values
('National Karate Championship 2024', '2024-03-15', 'New Delhi Indoor Stadium'),
('Regional Spring Tournament', '2024-05-20', 'Mumbai Sports Complex'),
('Summer Training Camp', '2024-07-10', 'Bangalore Martial Arts Center'),
('Junior Open Cup', '2024-09-05', 'Chennai Nehru Stadium'),
('Winter Dojo Championship', '2024-12-12', 'Hyderabad Gachibowli Stadium'),
('International Friendship Cup', '2025-01-20', 'Pune Balewadi Complex');

-- Students (260 Unique Names - No Permutations)
insert into students (full_name, student_id, belt_rank, dojo) values
('Aarav Patel', 'SK-0001', 'Black', 'Main Dojo'),
('Vihaan Singh', 'SK-0002', 'Brown', 'North Branch'),
('Aditya Sharma', 'SK-0003', 'Brown', 'South Branch'),
('Sai Kumar', 'SK-0004', 'Green', 'West Branch'),
('Ishaan Gupta', 'SK-0005', 'Black', 'Main Dojo'),
('Rohan Verma', 'SK-0006', 'Yellow', 'North Branch'),
('Atharv Reddy', 'SK-0007', 'White', 'South Branch'),
('Reyansh Mishra', 'SK-0008', 'Green', 'West Branch'),
('Arjun Nair', 'SK-0009', 'Brown', 'Main Dojo'),
('Krishna Iyer', 'SK-0010', 'Black', 'North Branch'),
('Dhruv Shetty', 'SK-0011', 'Yellow', 'South Branch'),
('Kabir Joshi', 'SK-0012', 'White', 'West Branch'),
('Vivaan Mehta', 'SK-0013', 'Green', 'Main Dojo'),
('Ayaan Khan', 'SK-0014', 'Brown', 'North Branch'),
('Shaurya Shah', 'SK-0015', 'Black', 'South Branch'),
('Ananya Patel', 'SK-0016', 'Green', 'West Branch'),
('Diya Singh', 'SK-0017', 'Brown', 'Main Dojo'),
('Saanvi Sharma', 'SK-0018', 'Black', 'North Branch'),
('Aadhya Kumar', 'SK-0019', 'Yellow', 'South Branch'),
('Kiara Gupta', 'SK-0020', 'White', 'West Branch'),
('Myra Verma', 'SK-0021', 'Green', 'Main Dojo'),
('Pari Reddy', 'SK-0022', 'Brown', 'North Branch'),
('Amara Mishra', 'SK-0023', 'Black', 'South Branch'),
('Riya Nair', 'SK-0024', 'Yellow', 'West Branch'),
('Anvi Iyer', 'SK-0025', 'White', 'Main Dojo'),
('Prisha Shetty', 'SK-0026', 'Green', 'North Branch'),
('Sarah Joshi', 'SK-0027', 'Brown', 'South Branch'),
('Amarya Mehta', 'SK-0028', 'Black', 'West Branch'),
('Zara Khan', 'SK-0029', 'Yellow', 'Main Dojo'),
('Fatima Shah', 'SK-0030', 'White', 'North Branch'),
('Rahul Malhotra', 'SK-0031', 'Green', 'South Branch'),
('Amit Saxena', 'SK-0032', 'Brown', 'West Branch'),
('Sumit Deshmukh', 'SK-0033', 'Black', 'Main Dojo'),
('Vikram Rao', 'SK-0034', 'Yellow', 'North Branch'),
('Suresh Kulkarni', 'SK-0035', 'White', 'South Branch'),
('Ramesh Choudhry', 'SK-0036', 'Green', 'West Branch'),
('Priya Chatterjee', 'SK-0037', 'Brown', 'Main Dojo'),
('Neha Bhattacharya', 'SK-0038', 'Black', 'North Branch'),
('Sneha Das', 'SK-0039', 'Yellow', 'South Branch'),
('Pooja Biswas', 'SK-0040', 'White', 'West Branch'),
('Karan Kapoor', 'SK-0041', 'Green', 'Main Dojo'),
('Arnav Khanna', 'SK-0042', 'Brown', 'North Branch'),
('Rishabh Jain', 'SK-0043', 'Black', 'South Branch'),
('Aryan Agarwal', 'SK-0044', 'Yellow', 'West Branch'),
('Dev Bansal', 'SK-0045', 'White', 'Main Dojo'),
('Yash Goel', 'SK-0046', 'Green', 'North Branch'),
('Harsh Singhania', 'SK-0047', 'Brown', 'South Branch'),
('Kunal Mittal', 'SK-0048', 'Black', 'West Branch'),
('Nikhil Jindal', 'SK-0049', 'Yellow', 'Main Dojo'),
('Tanvi Chopra', 'SK-0050', 'White', 'North Branch'),
('Ritika Soni', 'SK-0051', 'Green', 'South Branch'),
('Ishita Ahuja', 'SK-0052', 'Brown', 'West Branch'),
('Vani Bhatia', 'SK-0053', 'Black', 'Main Dojo'),
('Kavya Narang', 'SK-0054', 'Yellow', 'North Branch'),
('Avni Kaur', 'SK-0055', 'White', 'South Branch'),
('Simran Gill', 'SK-0056', 'Green', 'West Branch'),
('Gurasis Sandhu', 'SK-0057', 'Brown', 'Main Dojo'),
('Harpreet Dhillon', 'SK-0058', 'Black', 'North Branch'),
('Jaspreet Grewal', 'SK-0059', 'Yellow', 'South Branch'),
('Manpreet Sidhu', 'SK-0060', 'White', 'West Branch'),
('Rajeshwari Devi', 'SK-0061', 'Green', 'Main Dojo'),
('Meenakshi Sundaram', 'SK-0062', 'Brown', 'North Branch'),
('Lakshmi Narayan', 'SK-0063', 'Black', 'South Branch'),
('Saraswati Menon', 'SK-0064', 'Yellow', 'West Branch'),
('Parvati Pillai', 'SK-0065', 'White', 'Main Dojo'),
('Ganesh Acharya', 'SK-0066', 'Green', 'North Branch'),
('Karthik Nambiar', 'SK-0067', 'Brown', 'South Branch'),
('Manikandan Prabhu', 'SK-0068', 'Black', 'West Branch'),
('Balaji Srinivasan', 'SK-0069', 'Yellow', 'Main Dojo'),
('Murugan Swami', 'SK-0070', 'White', 'North Branch'),
('Abhinav Bindra', 'SK-0071', 'Green', 'South Branch'),
('Saina Nehwal', 'SK-0072', 'Brown', 'West Branch'),
('PV Sindhu', 'SK-0073', 'Black', 'Main Dojo'),
('Mary Kom', 'SK-0074', 'Yellow', 'North Branch'),
('Sania Mirza', 'SK-0075', 'White', 'South Branch'),
('Virat Kohli', 'SK-0076', 'Green', 'West Branch'),
('Rohit Sharma', 'SK-0077', 'Brown', 'Main Dojo'),
('MS Dhoni', 'SK-0078', 'Black', 'North Branch'),
('Sachin Tendulkar', 'SK-0079', 'Yellow', 'South Branch'),
('Sourav Ganguly', 'SK-0080', 'White', 'West Branch'),
('Raghavendra Rao', 'SK-0081', 'Green', 'Main Dojo'),
('Satish Kumar', 'SK-0082', 'Brown', 'North Branch'),
('Mukesh Ambani', 'SK-0083', 'Black', 'South Branch'),
('Ratan Tata', 'SK-0084', 'Yellow', 'West Branch'),
('Azim Premji', 'SK-0085', 'White', 'Main Dojo'),
('Shiv Nadar', 'SK-0086', 'Green', 'North Branch'),
('Gautam Adani', 'SK-0087', 'Brown', 'South Branch'),
('Cyrus Mistry', 'SK-0088', 'Black', 'West Branch'),
('Sunil Mittal', 'SK-0089', 'Yellow', 'Main Dojo'),
('Anil Agarwal', 'SK-0090', 'White', 'North Branch'),
('Mira Rajput', 'SK-0091', 'Green', 'South Branch'),
('Twinkle Khanna', 'SK-0092', 'Brown', 'West Branch'),
('Shilpa Shetty', 'SK-0093', 'Black', 'Main Dojo'),
('Karisma Kapoor', 'SK-0094', 'Yellow', 'North Branch'),
('Kareena Kapoor', 'SK-0095', 'White', 'South Branch'),
('Ranbir Kapoor', 'SK-0096', 'Green', 'West Branch'),
('Ranveer Singh', 'SK-0097', 'Brown', 'Main Dojo'),
('Deepika Padukone', 'SK-0098', 'Black', 'North Branch'),
('Alia Bhatt', 'SK-0099', 'Yellow', 'South Branch'),
('Varun Dhawan', 'SK-0100', 'White', 'West Branch'),
('Siddharth Malhotra', 'SK-0101', 'Green', 'Main Dojo'),
('Kartik Aaryan', 'SK-0102', 'Brown', 'North Branch'),
('Kriti Sanon', 'SK-0103', 'Black', 'South Branch'),
('Shraddha Kapoor', 'SK-0104', 'Yellow', 'West Branch'),
('Tiger Shroff', 'SK-0105', 'White', 'Main Dojo'),
('Hrithik Roshan', 'SK-0106', 'Green', 'North Branch'),
('Salman Khan', 'SK-0107', 'Brown', 'South Branch'),
('Shah Rukh Khan', 'SK-0108', 'Black', 'West Branch'),
('Aamir Khan', 'SK-0109', 'Yellow', 'Main Dojo'),
('Akshay Kumar', 'SK-0110', 'White', 'North Branch'),
('Ajay Devgn', 'SK-0111', 'Green', 'South Branch'),
('Saif Ali Khan', 'SK-0112', 'Brown', 'West Branch'),
('Vicky Kaushal', 'SK-0113', 'Black', 'Main Dojo'),
('Ayushmann Khurrana', 'SK-0114', 'Yellow', 'North Branch'),
('Rajkummar Rao', 'SK-0115', 'White', 'South Branch'),
('Pankaj Tripathi', 'SK-0116', 'Green', 'West Branch'),
('Nawazuddin Siddiqui', 'SK-0117', 'Brown', 'Main Dojo'),
('Manoj Bajpayee', 'SK-0118', 'Black', 'North Branch'),
('Irrfan Khan', 'SK-0119', 'Yellow', 'South Branch'),
('Naseeruddin Shah', 'SK-0120', 'White', 'West Branch'),
('Anupam Kher', 'SK-0121', 'Green', 'Main Dojo'),
('Paresh Rawal', 'SK-0122', 'Brown', 'North Branch'),
('Boman Irani', 'SK-0123', 'Black', 'South Branch'),
('Sanjay Dutt', 'SK-0124', 'Yellow', 'West Branch'),
('Suniel Shetty', 'SK-0125', 'White', 'Main Dojo'),
('John Abraham', 'SK-0126', 'Green', 'North Branch'),
('Emraan Hashmi', 'SK-0127', 'Brown', 'South Branch'),
('Randeep Hooda', 'SK-0128', 'Black', 'West Branch'),
('Sonu Sood', 'SK-0129', 'Yellow', 'Main Dojo'),
('Diljit Dosanjh', 'SK-0130', 'White', 'North Branch'),
('Kapil Sharma', 'SK-0131', 'Green', 'South Branch'),
('Badshah', 'SK-0132', 'Brown', 'West Branch'),
('Yo Yo Honey Singh', 'SK-0133', 'Black', 'Main Dojo'),
('Arijit Singh', 'SK-0134', 'Yellow', 'North Branch'),
('Sonu Nigam', 'SK-0135', 'White', 'South Branch'),
('Shreya Ghoshal', 'SK-0136', 'Green', 'West Branch'),
('Sunidhi Chauhan', 'SK-0137', 'Brown', 'Main Dojo'),
('Neha Kakkar', 'SK-0138', 'Black', 'North Branch'),
('Armaan Malik', 'SK-0139', 'Yellow', 'South Branch'),
('Darshan Raval', 'SK-0140', 'White', 'West Branch'),
('Guru Randhawa', 'SK-0141', 'Green', 'Main Dojo'),
('Hardik Pandya', 'SK-0142', 'Brown', 'North Branch'),
('KL Rahul', 'SK-0143', 'Black', 'South Branch'),
('Ravindra Jadeja', 'SK-0144', 'Yellow', 'West Branch'),
('Jasprit Bumrah', 'SK-0145', 'White', 'Main Dojo'),
('Shikhar Dhawan', 'SK-0146', 'Green', 'North Branch'),
('Rishabh Pant', 'SK-0147', 'Brown', 'South Branch'),
('Shreyas Iyer', 'SK-0148', 'Black', 'West Branch'),
('Subhman Gill', 'SK-0149', 'Yellow', 'Main Dojo'),
('Ishant Sharma', 'SK-0150', 'White', 'North Branch'),
('Mohammed Shami', 'SK-0151', 'Green', 'South Branch'),
('Bhuvneshwar Kumar', 'SK-0152', 'Brown', 'West Branch'),
('Yuzvendra Chahal', 'SK-0153', 'Black', 'Main Dojo'),
('Kuldeep Yadav', 'SK-0154', 'Yellow', 'North Branch'),
('Suryakumar Yadav', 'SK-0155', 'White', 'South Branch'),
('Sanju Samson', 'SK-0156', 'Green', 'West Branch'),
('Prithvi Shaw', 'SK-0157', 'Brown', 'Main Dojo'),
('Mayank Agarwal', 'SK-0158', 'Black', 'North Branch'),
('Cheteshwar Pujara', 'SK-0159', 'Yellow', 'South Branch'),
('Ajinkya Rahane', 'SK-0160', 'White', 'West Branch'),
('Ravichandran Ashwin', 'SK-0161', 'Green', 'Main Dojo'),
('Umesh Yadav', 'SK-0162', 'Brown', 'North Branch'),
('Axar Patel', 'SK-0163', 'Black', 'South Branch'),
('Shardul Thakur', 'SK-0164', 'Yellow', 'West Branch'),
('Washington Sundar', 'SK-0165', 'White', 'Main Dojo'),
('T Natarajan', 'SK-0166', 'Green', 'North Branch'),
('Navdeep Saini', 'SK-0167', 'Brown', 'South Branch'),
('Mohammed Siraj', 'SK-0168', 'Black', 'West Branch'),
('Deepak Chahar', 'SK-0169', 'Yellow', 'Main Dojo'),
('Rahul Chahar', 'SK-0170', 'White', 'North Branch'),
('Varun Chakravarthy', 'SK-0171', 'Green', 'South Branch'),
('Shivam Dube', 'SK-0172', 'Brown', 'West Branch'),
('Vijay Shankar', 'SK-0173', 'Black', 'Main Dojo'),
('Krunal Pandya', 'SK-0174', 'Yellow', 'North Branch'),
('Manish Pandey', 'SK-0175', 'White', 'South Branch'),
('Kedar Jadhav', 'SK-0176', 'Green', 'West Branch'),
('Dinesh Karthik', 'SK-0177', 'Brown', 'Main Dojo'),
('Ambati Rayudu', 'SK-0178', 'Black', 'North Branch'),
('Robin Uthappa', 'SK-0179', 'Yellow', 'South Branch'),
('Gautam Gambhir', 'SK-0180', 'White', 'West Branch'),
('Virender Sehwag', 'SK-0181', 'Green', 'Main Dojo'),
('Yuvraj Singh', 'SK-0182', 'Brown', 'North Branch'),
('Harbhajan Singh', 'SK-0183', 'Black', 'South Branch'),
('Zaheer Khan', 'SK-0184', 'Yellow', 'West Branch'),
('Ashish Nehra', 'SK-0185', 'White', 'Main Dojo'),
('Irfan Pathan', 'SK-0186', 'Green', 'North Branch'),
('Yusuf Pathan', 'SK-0187', 'Brown', 'South Branch'),
('Praveen Kumar', 'SK-0188', 'Black', 'West Branch'),
('Munaf Patel', 'SK-0189', 'Yellow', 'Main Dojo'),
('S Sreesanth', 'SK-0190', 'White', 'North Branch'),
('RP Singh', 'SK-0191', 'Green', 'South Branch'),
('Joginder Sharma', 'SK-0192', 'Brown', 'West Branch'),
('Piyush Chawla', 'SK-0193', 'Black', 'Main Dojo'),
('Murali Vijay', 'SK-0194', 'Yellow', 'North Branch'),
('Subramaniam Badrinath', 'SK-0195', 'White', 'South Branch'),
('L Balaji', 'SK-0196', 'Green', 'West Branch'),
('Parthiv Patel', 'SK-0197', 'Brown', 'Main Dojo'),
('Wriddhiman Saha', 'SK-0198', 'Black', 'North Branch'),
('Naman Ojha', 'SK-0199', 'Yellow', 'South Branch'),
('Stuart Binny', 'SK-0200', 'White', 'West Branch'),
('Rishi Dhawan', 'SK-0201', 'Green', 'Main Dojo'),
('Gurkeerat Mann', 'SK-0202', 'Brown', 'North Branch'),
('Barinder Sran', 'SK-0203', 'Black', 'South Branch'),
('Faiz Fazal', 'SK-0204', 'Yellow', 'West Branch'),
('Karun Nair', 'SK-0205', 'White', 'Main Dojo'),
('Jayant Yadav', 'SK-0206', 'Green', 'North Branch'),
('Shahbaz Nadeem', 'SK-0207', 'Brown', 'South Branch'),
('Siddarth Kaul', 'SK-0208', 'Black', 'West Branch'),
('Khaleel Ahmed', 'SK-0209', 'Yellow', 'Main Dojo'),
('Basil Thampi', 'SK-0210', 'White', 'North Branch'),
('Sandeep Warrier', 'SK-0211', 'Green', 'South Branch'),
('Simarjeet Singh', 'SK-0212', 'Brown', 'West Branch'),
('Kuldeep Sen', 'SK-0213', 'Black', 'Main Dojo'),
('Yash Dayal', 'SK-0214', 'Yellow', 'North Branch'),
('R Sai Kishore', 'SK-0215', 'White', 'South Branch'),
('Tilak Varma', 'SK-0216', 'Green', 'West Branch'),
('Dewald Brevis', 'SK-0217', 'Brown', 'Main Dojo'),
('Tristan Stubbs', 'SK-0218', 'Black', 'North Branch'),
('Tim David', 'SK-0219', 'Yellow', 'South Branch'),
('Cameron Green', 'SK-0220', 'White', 'West Branch'),
('Jofra Archer', 'SK-0221', 'Green', 'Main Dojo'),
('Jason Behrendorff', 'SK-0222', 'Brown', 'North Branch'),
('Jhye Richardson', 'SK-0223', 'Black', 'South Branch'),
('Riley Meredith', 'SK-0224', 'Yellow', 'West Branch'),
('Duanne Jansen', 'SK-0225', 'White', 'Main Dojo'),
('Arjun Tendulkar', 'SK-0226', 'Green', 'North Branch'),
('Vishnu Vinod', 'SK-0227', 'Brown', 'South Branch'),
('Shams Mulani', 'SK-0228', 'Black', 'West Branch'),
('Nehal Wadhera', 'SK-0229', 'Yellow', 'Main Dojo'),
('Akash Madhwal', 'SK-0230', 'White', 'North Branch'),
('Raghav Goyal', 'SK-0231', 'Green', 'South Branch'),
('Sandeep Sharma', 'SK-0232', 'Brown', 'West Branch'),
('Mayank Markande', 'SK-0233', 'Black', 'Main Dojo'),
('Abhishek Sharma', 'SK-0234', 'Yellow', 'North Branch'),
('Abdul Samad', 'SK-0235', 'White', 'South Branch'),
('Umran Malik', 'SK-0236', 'Green', 'West Branch'),
('Vivrant Sharma', 'SK-0237', 'Brown', 'Main Dojo'),
('Sanvir Singh', 'SK-0238', 'Black', 'North Branch'),
('Upendra Yadav', 'SK-0239', 'Yellow', 'South Branch'),
('Mayank Dagar', 'SK-0240', 'White', 'West Branch'),
('Nitish Kumar Reddy', 'SK-0241', 'Green', 'Main Dojo'),
('Anmolpreet Singh', 'SK-0242', 'Brown', 'North Branch'),
('Akeal Hosein', 'SK-0243', 'Black', 'South Branch'),
('Adil Rashid', 'SK-0244', 'Yellow', 'West Branch'),
('Harry Brook', 'SK-0245', 'White', 'Main Dojo'),
('Heinrich Klaasen', 'SK-0246', 'Green', 'North Branch'),
('Glenn Phillips', 'SK-0247', 'Brown', 'South Branch'),
('Marco Jansen', 'SK-0248', 'Black', 'West Branch'),
('Aiden Markram', 'SK-0249', 'Yellow', 'Main Dojo'),
('Fazalhaq Farooqi', 'SK-0250', 'White', 'North Branch'),
('Kartik Tyagi', 'SK-0251', 'Green', 'South Branch'),
('Tushar Deshpande', 'SK-0252', 'Brown', 'West Branch'),
('Matheesha Pathirana', 'SK-0253', 'Black', 'Main Dojo'),
('Maheesh Theekshana', 'SK-0254', 'Yellow', 'North Branch'),
('Devon Conway', 'SK-0255', 'White', 'South Branch'),
('Mitchell Santner', 'SK-0256', 'Green', 'West Branch'),
('Moeen Ali', 'SK-0257', 'Brown', 'Main Dojo'),
('Ben Stokes', 'SK-0258', 'Black', 'North Branch'),
('Ajay Mandal', 'SK-0259', 'Yellow', 'South Branch'),
('Bhagath Varma', 'SK-0260', 'White', 'West Branch');

-- Participations (Randomly assign students to events)
insert into participations (student_id, event_id)
select s.id, e.id
from students s
cross join events e
where random() < 0.3; -- 30% chance of participation per event

-- Results (Randomly assign medals to participations)
insert into results (participation_id, category, medal)
select 
  p.id,
  case (random() * 3)::int
    when 0 then 'Individual Kata'
    when 1 then 'Individual Kumite'
    when 2 then 'Team Kata'
    else 'Team Kumite'
  end as category,
  case (random() * 4)::int
    when 0 then 'GOLD'
    when 1 then 'SILVER'
    when 2 then 'BRONZE'
    else 'PARTICIPATION'
  end as medal
from participations p
where random() < 0.5; -- 50% chance of getting a result if participated