-- Fix: Add missing columns to students table
-- Run in phpMyAdmin SQL tab

-- Add batch_id (FK to batches.id)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'students' AND COLUMN_NAME = 'batch_id');
SET @sql := IF(@col = 0, 'ALTER TABLE `students` ADD COLUMN `batch_id` INT DEFAULT NULL AFTER `course_id`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Add roll_no (sequential, starting from 154588)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'students' AND COLUMN_NAME = 'roll_no');
SET @sql := IF(@col = 0, 'ALTER TABLE `students` ADD COLUMN `roll_no` VARCHAR(20) DEFAULT NULL AFTER `enrollment_no`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Add teacher_id to batches table (for allocating teacher to batch)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batches' AND COLUMN_NAME = 'teacher_id');
SET @sql := IF(@col = 0, 'ALTER TABLE `batches` ADD COLUMN `teacher_id` BIGINT UNSIGNED DEFAULT NULL AFTER `branch_id`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Add teacher_name to batches table
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batches' AND COLUMN_NAME = 'teacher_name');
SET @sql := IF(@col = 0, 'ALTER TABLE `batches` ADD COLUMN `teacher_name` VARCHAR(255) DEFAULT NULL AFTER `teacher_id`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Add time_slot to batches table (e.g. "06:00-07:30 AM")
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batches' AND COLUMN_NAME = 'time_slot');
SET @sql := IF(@col = 0, 'ALTER TABLE `batches` ADD COLUMN `time_slot` VARCHAR(50) DEFAULT NULL AFTER `timing`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Add subjects column to courses table (JSON array of subjects with max_marks)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'subjects');
SET @sql := IF(@col = 0, 'ALTER TABLE `courses` ADD COLUMN `subjects` TEXT DEFAULT NULL AFTER `syllabus`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Verify
-- DESCRIBE students;
-- DESCRIBE batches;
-- DESCRIBE courses;
