-- =====================================================================
-- Migration: Unify subjects + class_group_subjects (PMSS Admin)
-- Date: 2026-05-01
-- Safe to run multiple times (idempotent guards via IF NOT EXISTS).
-- =====================================================================

-- 1) Add new columns to `subjects` (skip silently if already added).
ALTER TABLE `subjects`
  ADD COLUMN IF NOT EXISTS `has_multiple_papers` TINYINT DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `paper_type` ENUM('single','paper1','paper2') DEFAULT 'single',
  ADD COLUMN IF NOT EXISTS `total_marks` INT DEFAULT 100,
  ADD COLUMN IF NOT EXISTS `written_marks` INT DEFAULT 70,
  ADD COLUMN IF NOT EXISTS `mcq_marks` INT DEFAULT 30,
  ADD COLUMN IF NOT EXISTS `practical_marks` INT DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `ca_marks` INT DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `pass_marks_written` INT DEFAULT 23,
  ADD COLUMN IF NOT EXISTS `pass_marks_mcq` INT DEFAULT 10,
  ADD COLUMN IF NOT EXISTS `pass_marks_practical` INT DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `pass_marks_ca` INT DEFAULT 0;

-- 2) Backfill granular marks from existing legacy columns where empty.
UPDATE `subjects`
   SET total_marks = COALESCE(NULLIF(total_marks,0), full_marks, 100),
       pass_marks_written = COALESCE(NULLIF(pass_marks_written,0), pass_marks, 33);

-- 3) Add helpful index for class+group lookups (no-op if exists).
ALTER TABLE `subjects`
  ADD INDEX IF NOT EXISTS idx_subjects_class_group (class, group_name);

-- 4) Migrate any extra mappings stored ONLY in class_group_subjects back
--    onto subjects.class / subjects.group_name. For each (class_id,
--    group_name, subject_id) tuple in the mapping table, ensure a row
--    exists in `subjects` with the same class+group.
--    Strategy: for mapping rows whose subject already belongs to a
--    different class, INSERT a duplicated subject row pinned to the
--    target class+group so no data is lost.
INSERT INTO `subjects`
  (subject_name, subject_code, class, group_name, is_optional, is_combined,
   has_mcq, has_practical, has_ca, full_marks, pass_marks, status)
SELECT s.subject_name, s.subject_code, m.class_id, NULLIF(m.group_name,''),
       s.is_optional, s.is_combined, s.has_mcq, s.has_practical, s.has_ca,
       s.full_marks, s.pass_marks, s.status
FROM `class_group_subjects` m
JOIN `subjects` s ON s.id = m.subject_id
WHERE NOT EXISTS (
  SELECT 1 FROM `subjects` s2
   WHERE s2.subject_name = s.subject_name
     AND (s2.class <=> m.class_id)
     AND (COALESCE(s2.group_name,'') = COALESCE(NULLIF(m.group_name,''),''))
);

-- 5) Drop the now-redundant mapping table.
DROP TABLE IF EXISTS `class_group_subjects`;

-- =====================================================================
-- Done. The `subjects` table is now the single source of truth.
-- =====================================================================