-- Academic data tables for dropdown options

-- Standards table
CREATE TABLE IF NOT EXISTS standards (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  display_order INT DEFAULT 0,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Divisions table
CREATE TABLE IF NOT EXISTS divisions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10) NOT NULL,
  display_order INT DEFAULT 0,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Roll numbers table
CREATE TABLE IF NOT EXISTS roll_numbers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  number INT NOT NULL,
  display_order INT DEFAULT 0,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample standards
INSERT INTO standards (name, display_order) VALUES 
('1st', 1),
('2nd', 2),
('3rd', 3),
('4th', 4),
('5th', 5),
('6th', 6),
('7th', 7),
('8th', 8),
('9th', 9),
('10th', 10),
('11th', 11),
('12th', 12);

-- Insert sample divisions
INSERT INTO divisions (name, display_order) VALUES 
('A', 1),
('B', 2),
('C', 3),
('D', 4),
('E', 5);

-- Insert roll numbers (1-50)
INSERT INTO roll_numbers (number, display_order) VALUES 
(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10),
(11, 11), (12, 12), (13, 13), (14, 14), (15, 15), (16, 16), (17, 17), (18, 18), (19, 19), (20, 20),
(21, 21), (22, 22), (23, 23), (24, 24), (25, 25), (26, 26), (27, 27), (28, 28), (29, 29), (30, 30),
(31, 31), (32, 32), (33, 33), (34, 34), (35, 35), (36, 36), (37, 37), (38, 38), (39, 39), (40, 40),
(41, 41), (42, 42), (43, 43), (44, 44), (45, 45), (46, 46), (47, 47), (48, 48), (49, 49), (50, 50);