-- =========================================================================
-- St. Michael Industrial School, Kalaiyar Karisal Kulam
-- Department of Employment and Training, Government of Tamil Nadu
-- Clean Production Database Schema
-- =========================================================================

-- 1. Trainees & Results Table
CREATE TABLE IF NOT EXISTS `results` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `reg_no` VARCHAR(64) NOT NULL UNIQUE,
  `candidate_name` VARCHAR(191) NOT NULL,
  `father_name` VARCHAR(191) NOT NULL,
  `dob` DATE NOT NULL,
  `trade_code` VARCHAR(32) NOT NULL,
  `trade_name` VARCHAR(191) NOT NULL,
  `institute_name` VARCHAR(255) NOT NULL DEFAULT 'St.Micheal Industrial School, Kalaiyar Karisal Kulam',
  `exam_title` VARCHAR(255) NOT NULL DEFAULT 'Industrial School Common Examination July 2026',
  `exam_year` INT NOT NULL DEFAULT 2026,
  `subjects` JSON NOT NULL,
  `total_max` INT NOT NULL DEFAULT 0,
  `total_obtained` INT NOT NULL DEFAULT 0,
  `percentage` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `result_status` ENUM('PASS', 'FAIL') NOT NULL DEFAULT 'PASS',
  `grade` VARCHAR(8) NOT NULL DEFAULT 'A',
  `rank` INT NULL,
  `issue_date` DATE NOT NULL DEFAULT '2026-07-15',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_reg_dob` (`reg_no`, `dob`),
  INDEX `idx_trade` (`trade_code`),
  INDEX `idx_status` (`result_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. System Settings Table (Logo, Site Metadata, Admin Password)
CREATE TABLE IF NOT EXISTS `system_settings` (
  `setting_key` VARCHAR(100) PRIMARY KEY,
  `setting_value` LONGTEXT NOT NULL,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Admin Users Table
CREATE TABLE IF NOT EXISTS `admin_users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` VARCHAR(50) NOT NULL DEFAULT 'admin',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Trades Table
CREATE TABLE IF NOT EXISTS `trades` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(32) NOT NULL UNIQUE,
  `name` VARCHAR(191) NOT NULL,
  `tamil_name` VARCHAR(191),
  `duration` VARCHAR(64) DEFAULT '1 Year',
  `total_subjects` INT NOT NULL DEFAULT 2,
  `total_max` INT NOT NULL DEFAULT 200,
  `subjects` JSON NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert Default Trades Configuration
INSERT INTO `trades` (`code`, `name`, `tamil_name`, `duration`, `total_subjects`, `total_max`, `subjects`) VALUES
('CT', 'Cutting and Tailoring', 'வெட்டுதல் மற்றும் தையல்', '1 Year', 2, 200, '[{"name":"Trade Theory","tamil":"தொழில்நுட்பக் கோட்பாடு","max":100,"minPass":40},{"name":"Trade Practical","tamil":"தொழில்நுட்பப் செய்முறை","max":100,"minPass":60}]'),
('ENW', 'Embroidery and Needle Work', 'எம்பிராய்டரி மற்றும் ஊசி வேலைப்பாடு', '1 Year', 2, 200, '[{"name":"Trade Theory","tamil":"தொழில்நுட்பக் கோட்பாடு","max":100,"minPass":40},{"name":"Trade Practical","tamil":"தொழில்நுட்பப் செய்முறை","max":100,"minPass":60}]'),
('TTC', 'Technical Teacher\'s Certificate', 'தொழில்நுட்ப ஆசிரியர் சான்றிதழ்', '1 Year', 11, 1100, '[{"name":"Psychology","tamil":"உளவியல்","max":100,"minPass":40},{"name":"Lesson Plan","tamil":"பாடத் திட்டம்","max":100,"minPass":40},{"name":"School Admin","tamil":"பள்ளி நிர்வாகம்","max":100,"minPass":40},{"name":"Home Science","tamil":"மனையியல்","max":100,"minPass":40},{"name":"General & Special Method","tamil":"பொது & சிறப்பு கற்பித்தல் முறை","max":100,"minPass":40},{"name":"Drafting","tamil":"வரைபடம் (டிராஃப்டிங்)","max":100,"minPass":40},{"name":"Textile Study","tamil":"ஜவுளி அறிவியல்","max":100,"minPass":40},{"name":"Drawing","tamil":"ஓவியம்","max":100,"minPass":40},{"name":"Method of Teaching","tamil":"கற்பித்தல் முறையியல்","max":100,"minPass":40},{"name":"Handwork Knitting and Crochet","tamil":"கைவேலை பின்னல் மற்றும் குரோசே","max":100,"minPass":60},{"name":"Practical","tamil":"செயல்முறைத் தேர்வு","max":100,"minPass":60}]')
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `tamil_name` = VALUES(`tamil_name`), `subjects` = VALUES(`subjects`);