-- SMI Core Periods compatibility and hardening upgrade
-- Safe to run once after taking a database backup.
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE `core_admins`
  ADD COLUMN IF NOT EXISTS `full_name` varchar(255) DEFAULT NULL AFTER `name`,
  ADD COLUMN IF NOT EXISTS `status` varchar(30) NOT NULL DEFAULT 'active' AFTER `password`,
  ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT NULL AFTER `created_at`;

ALTER TABLE `core_notifications`
  ADD COLUMN IF NOT EXISTS `notification_type` varchar(50) NOT NULL DEFAULT 'general' AFTER `message`;

ALTER TABLE `core_homework`
  ADD COLUMN IF NOT EXISTS `max_score` decimal(8,2) NOT NULL DEFAULT 10.00 AFTER `max_points`,
  ADD COLUMN IF NOT EXISTS `points` decimal(8,2) NOT NULL DEFAULT 10.00 AFTER `max_score`,
  ADD COLUMN IF NOT EXISTS `weight` decimal(6,2) NOT NULL DEFAULT 10.00 AFTER `points`,
  ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT NULL AFTER `created_at`;
UPDATE `core_homework` SET `max_score` = COALESCE(NULLIF(`max_score`,0),`max_points`,10), `points` = COALESCE(NULLIF(`points`,0),`max_points`,10);

ALTER TABLE `core_exam_attempts`
  ADD COLUMN IF NOT EXISTS `result_released` tinyint(1) NOT NULL DEFAULT 1 AFTER `result_status`;

ALTER TABLE `core_grade_items`
  MODIFY COLUMN `item_type` varchar(50) NOT NULL DEFAULT 'extra',
  ADD COLUMN IF NOT EXISTS `item_id` int(11) DEFAULT NULL AFTER `exam_id`,
  ADD COLUMN IF NOT EXISTS `type` varchar(50) DEFAULT NULL AFTER `item_type`,
  ADD COLUMN IF NOT EXISTS `score` decimal(10,2) DEFAULT NULL AFTER `max_points`,
  ADD COLUMN IF NOT EXISTS `max_score` decimal(10,2) DEFAULT NULL AFTER `score`,
  ADD COLUMN IF NOT EXISTS `percentage` decimal(6,2) DEFAULT NULL AFTER `max_score`,
  ADD COLUMN IF NOT EXISTS `weight` decimal(6,2) DEFAULT NULL AFTER `weight_percent`,
  ADD COLUMN IF NOT EXISTS `feedback` text DEFAULT NULL AFTER `requires_submission`,
  ADD COLUMN IF NOT EXISTS `notes` text DEFAULT NULL AFTER `feedback`,
  ADD COLUMN IF NOT EXISTS `teacher_id` int(11) DEFAULT NULL AFTER `notes`,
  ADD COLUMN IF NOT EXISTS `created_by_admin_id` int(11) DEFAULT NULL AFTER `teacher_id`,
  ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT NULL AFTER `created_at`;
UPDATE `core_grade_items`
SET `type` = COALESCE(NULLIF(`type`,''),`item_type`),
    `item_type` = COALESCE(NULLIF(`item_type`,''),NULLIF(`type`,''),'extra'),
    `max_score` = COALESCE(`max_score`,`max_points`,100),
    `weight` = COALESCE(`weight`,`weight_percent`,0),
    `percentage` = CASE WHEN `score` IS NOT NULL AND COALESCE(`max_score`,`max_points`,0) > 0 THEN LEAST(100,GREATEST(0,`score` / COALESCE(`max_score`,`max_points`) * 100)) ELSE `percentage` END;

CREATE TABLE IF NOT EXISTS `core_grades` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `grade_item_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `score` decimal(10,2) NOT NULL DEFAULT 0.00,
  `max_score` decimal(10,2) NOT NULL DEFAULT 100.00,
  `percentage` decimal(6,2) NOT NULL DEFAULT 0.00,
  `feedback` text DEFAULT NULL,
  `graded_by_teacher_id` int(11) DEFAULT NULL,
  `graded_by_admin_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_core_grade_student_item` (`grade_item_id`,`student_id`),
  KEY `idx_core_grades_course_student` (`course_id`,`student_id`),
  KEY `idx_core_grades_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `core_grades` (`grade_item_id`,`student_id`,`course_id`,`score`,`max_score`,`percentage`,`feedback`,`created_at`,`updated_at`)
SELECT gi.id, gi.student_id, gi.course_id,
       COALESCE(gi.score,0), COALESCE(NULLIF(gi.max_score,0),NULLIF(gi.max_points,0),100),
       COALESCE(gi.percentage, CASE WHEN COALESCE(NULLIF(gi.max_score,0),NULLIF(gi.max_points,0),0)>0 THEN gi.score/COALESCE(NULLIF(gi.max_score,0),NULLIF(gi.max_points,0))*100 ELSE 0 END),
       COALESCE(gi.feedback,gi.notes), gi.created_at, COALESCE(gi.updated_at,gi.created_at)
FROM `core_grade_items` gi
WHERE gi.student_id IS NOT NULL AND gi.course_id > 0 AND gi.score IS NOT NULL
ON DUPLICATE KEY UPDATE
 `score`=VALUES(`score`), `max_score`=VALUES(`max_score`), `percentage`=VALUES(`percentage`), `feedback`=VALUES(`feedback`), `updated_at`=VALUES(`updated_at`);

ALTER TABLE `core_final_grades`
  ADD COLUMN IF NOT EXISTS `grade_letter` varchar(5) DEFAULT NULL AFTER `final_percentage`,
  ADD COLUMN IF NOT EXISTS `gpa` decimal(3,2) DEFAULT NULL AFTER `grade_letter`,
  ADD COLUMN IF NOT EXISTS `pass_status` varchar(30) DEFAULT NULL AFTER `gpa`,
  ADD COLUMN IF NOT EXISTS `created_at` datetime DEFAULT current_timestamp() AFTER `exam_percentage`,
  ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT NULL AFTER `created_at`;

ALTER TABLE `core_join_requests`
  ADD COLUMN IF NOT EXISTS `review_note` text DEFAULT NULL AFTER `message`,
  ADD COLUMN IF NOT EXISTS `reviewed_by` int(11) DEFAULT NULL AFTER `reviewed_at`,
  ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT NULL AFTER `reviewed_by`;

ALTER TABLE `core_participation`
  ADD COLUMN IF NOT EXISTS `teacher_id` int(11) DEFAULT NULL AFTER `student_id`,
  ADD COLUMN IF NOT EXISTS `notes` text DEFAULT NULL AFTER `note`,
  ADD COLUMN IF NOT EXISTS `participation_date` date DEFAULT NULL AFTER `notes`;
UPDATE `core_participation` SET `notes`=COALESCE(`notes`,`note`), `participation_date`=COALESCE(`participation_date`,DATE(`created_at`));

ALTER TABLE `core_vocabulary`
  ADD COLUMN IF NOT EXISTS `teacher_id` int(11) DEFAULT NULL AFTER `course_id`,
  ADD COLUMN IF NOT EXISTS `cognitive_weight` decimal(6,2) NOT NULL DEFAULT 1.00 AFTER `difficulty`;

ALTER TABLE `core_attendance`
  ADD COLUMN IF NOT EXISTS `notes` text DEFAULT NULL AFTER `note`;
UPDATE `core_attendance` SET `notes`=COALESCE(`notes`,`note`), `note`=COALESCE(`note`,`notes`), `minutes_attended`=GREATEST(COALESCE(`minutes_attended`,0),COALESCE(`attendance_minutes`,0)), `attendance_minutes`=GREATEST(COALESCE(`minutes_attended`,0),COALESCE(`attendance_minutes`,0));

ALTER TABLE `core_shadowing_submissions`
  MODIFY COLUMN `task_id` int(11) NOT NULL DEFAULT 0,
  MODIFY COLUMN `recording_url` text DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `recording_link` text DEFAULT NULL AFTER `recording_url`,
  ADD COLUMN IF NOT EXISTS `student_note` text DEFAULT NULL AFTER `recording_link`;
UPDATE `core_shadowing_submissions` SET `task_id`=COALESCE(NULLIF(`task_id`,0),`lesson_id`,0), `recording_link`=COALESCE(`recording_link`,`recording_url`), `recording_url`=COALESCE(`recording_url`,`recording_link`);

ALTER TABLE `core_obstacle_submissions`
  ADD COLUMN IF NOT EXISTS `student_note` text DEFAULT NULL AFTER `submission_url`;

UPDATE `students`
SET `student_card_number` = COALESCE(NULLIF(`student_card_number`,''),NULLIF(`student_card`,'')),
    `student_card` = COALESCE(NULLIF(`student_card`,''),NULLIF(`student_card_number`,''));

DROP VIEW IF EXISTS `core_shadowing_lessons`;
CREATE VIEW `core_shadowing_lessons` AS
SELECT `id`,`course_id`,`teacher_id`,`title`,`description`,`youtube_link`,
       `shadowing_level` AS `lesson_order`, `points`, 'active' AS `status`, `created_at`
FROM `core_shadowing`;

SET FOREIGN_KEY_CHECKS=1;
