-- Create subject_types table
CREATE TABLE IF NOT EXISTS `subject_types` (
  `type_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_name` varchar(50) NOT NULL,
  `type_code` varchar(20) NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`type_id`),
  UNIQUE KEY `unique_type_name` (`type_name`),
  UNIQUE KEY `unique_type_code` (`type_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default subject types
INSERT INTO `subject_types` (`type_name`, `type_code`, `description`, `is_active`) VALUES
('Regular', 'REG', 'Regular theory subjects', 1),
('Practical', 'PRAC', 'Practical subjects with hands-on training', 1),
('Elective', 'ELE', 'Elective/optional subjects', 1),
('Lab', 'LAB', 'Laboratory subjects', 1),
('Project', 'PROJ', 'Project work subjects', 1),
('Seminar', 'SEM', 'Seminar subjects', 1),
('Workshop', 'WS', 'Workshop subjects', 1),
('Internship', 'INT', 'Internship subjects', 1);

-- Update existing subjects to use proper subject types
-- This will map existing subject types to the new table
UPDATE `subjects` SET `type` = 'Regular' WHERE `type` = 'Regular';
UPDATE `subjects` SET `type` = 'Practical' WHERE `type` = 'Practical';
UPDATE `subjects` SET `type` = 'Elective' WHERE `type` = 'Elective';
UPDATE `subjects` SET `type` = 'Lab' WHERE `type` = 'Lab';

-- Add index for better performance
CREATE INDEX `idx_subject_types_active` ON `subject_types` (`is_active`);
CREATE INDEX `idx_subject_types_name` ON `subject_types` (`type_name`);