-- 1. Insert compulsory subjects for all JSS 1 students
INSERT INTO results (
    student_id, subject, class, section,
    test_score, test_score2, class_performance, weekly_assessment, exam_score,
    session_id, term_id, teacher_id, total_score, remarks,
    status, status_test,
    test_absent, test2_absent, class_performance_absent, weekly_assessment_absent, exam_absent
)
SELECT 
    s.id AS student_id,
    subj.Subject_name AS subject,
    s.class,
    s.section,
    FLOOR(RAND() * 6) + 5 AS test_score,          -- 5–10
    FLOOR(RAND() * 6) + 5 AS test_score2,         -- 5–10
    FLOOR(RAND() * 3) + 3 AS class_performance,   -- 3–5
    FLOOR(RAND() * 3) + 3 AS weekly_assessment,   -- 3–5
    FLOOR(RAND() * 31) + 40 AS exam_score,        -- 40–70
    2 AS session_id,
    1 AS term_id,
    30 AS teacher_id,
    0,   -- placeholder total_score
    'Excellent performance. Keep it up!' AS remarks,
    'Approved' AS status,
    'Pending' AS status_test,
    0,0,0,0,0
FROM students s
JOIN subjectcombination subj 
    ON subj.Class = 'JSS 1' AND subj.section = 'T' AND subj.subject_type = 'Compulsory'
WHERE s.class = 'JSS 1' AND s.section = 'T';

-- 2. Insert elective (C.R.S or I.R.S)
-- Rule: Even student IDs get C.R.S, Odd student IDs get I.R.S
INSERT INTO results (
    student_id, subject, class, section,
    test_score, test_score2, class_performance, weekly_assessment, exam_score,
    session_id, term_id, teacher_id, total_score, remarks,
    status, status_test,
    test_absent, test2_absent, class_performance_absent, weekly_assessment_absent, exam_absent
)
SELECT 
    s.id AS student_id,
    CASE WHEN s.id % 2 = 0 THEN 'C.R.S' ELSE 'I.R.S' END AS subject,
    s.class,
    s.section,
    FLOOR(RAND() * 6) + 5 AS test_score,
    FLOOR(RAND() * 6) + 5 AS test_score2,
    FLOOR(RAND() * 3) + 3 AS class_performance,
    FLOOR(RAND() * 3) + 3 AS weekly_assessment,
    FLOOR(RAND() * 31) + 40 AS exam_score,
    2 AS session_id,
    1 AS term_id,
    30 AS teacher_id,
    0,
    'Excellent performance. Keep it up!',
    'Approved',
    'Pending',
    0,0,0,0,0
FROM students s
WHERE s.class = 'JSS 1' AND s.section = 'T';

-- 3. Update total_score for all inserted rows
UPDATE results
SET total_score = test_score + test_score2 + class_performance + weekly_assessment + exam_score
WHERE class = 'JSS 1' AND section = 'T';




CREATE TABLE school_calendar (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    term_id INT NOT NULL,
    resumption_date DATE NOT NULL,
    closing_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY (session_id, term_id)
);

CREATE TABLE public_holidays (
    id INT AUTO_INCREMENT PRIMARY KEY,
    holiday_date DATE NOT NULL,
    description VARCHAR(255) NULL,
    session_id INT NOT NULL,
    term_id INT NOT NULL
);


-- 1. Insert compulsory subjects for all JSS 1 students
INSERT INTO result2 (
    student_id, subject, class, section,
    test_score, test_score2, class_performance, weekly_assessment, exam_score,
    session_id, term_id, teacher_id, total_score, remarks_test,
    status, status_test,
    test_absent, test2_absent, class_performance_absent, weekly_assessment_absent, exam_absent
)
SELECT 
    s.id AS student_id,
    subj.Subject_name AS subject,
    s.class,
    s.section,
    FLOOR(RAND() * 6) + 5 AS test_score,          -- 5–10
    FLOOR(RAND() * 6) + 5 AS test_score2,         -- 5–10
    FLOOR(RAND() * 3) + 3 AS class_performance,   -- 3–5
    FLOOR(RAND() * 3) + 3 AS weekly_assessment,   -- 3–5
    FLOOR(RAND() * 31) + 40 AS exam_score,        -- 40–70
    2 AS session_id,
    1 AS term_id,
    30 AS teacher_id,
    0,   -- placeholder total_score
    'Excellent performance. Keep it up!' AS remarks,
    'Pending' AS status,
    'Approved' AS status_test,
    0,0,0,0,0
FROM students s
JOIN subjectcombination subj 
    ON subj.Class = 'JSS 1' AND subj.section = 'T' AND subj.subject_type = 'Compulsory'
WHERE s.class = 'JSS 1' AND s.section = 'T';

-- 2. Insert elective (C.R.S or I.R.S)
-- Rule: Even student IDs get C.R.S, Odd student IDs get I.R.S
INSERT INTO result2 (
    student_id, subject, class, section,
    test_score, test_score2, class_performance, weekly_assessment, exam_score,
    session_id, term_id, teacher_id, total_score, remarks_test,
    status, status_test,
    test_absent, test2_absent, class_performance_absent, weekly_assessment_absent, exam_absent
)
SELECT 
    s.id AS student_id,
    CASE WHEN s.id % 2 = 0 THEN 'C.R.S' ELSE 'I.R.S' END AS subject,
    s.class,
    s.section,
    FLOOR(RAND() * 6) + 5 AS test_score,
    FLOOR(RAND() * 6) + 5 AS test_score2,
    FLOOR(RAND() * 3) + 3 AS class_performance,
    FLOOR(RAND() * 3) + 3 AS weekly_assessment,
    FLOOR(RAND() * 31) + 40 AS exam_score,
    2 AS session_id,
    1 AS term_id,
    30 AS teacher_id,
    0,
    'Excellent performance. Keep it up!',
    'Pending',
    'Approved',
    0,0,0,0,0
FROM students s
WHERE s.class = 'JSS 1' AND s.section = 'T';

-- 3. Update total_score for all inserted rows
UPDATE result2
SET total_score = test_score + test_score2 + class_performance + weekly_assessment + exam_score
WHERE class = 'JSS 1' AND section = 'T';




-- 1. Insert compulsory subjects for all SSS 1 students
INSERT INTO results (
    student_id, subject, class, section,
    test_score, class_performance, weekly_assessment, exam_score,
    session_id, term_id, teacher_id, total_score, remarks,
    status, status_test,
    test_absent, class_performance_absent, weekly_assessment_absent, exam_absent
)
SELECT 
    s.id AS student_id,
    subj.Subject_name AS subject,
    s.class,
    s.section,
    FLOOR(RAND() * 6) + 5 AS test_score,           -- 5–10
    FLOOR(RAND() * 11) + 5 AS class_performance,   -- 5–15
    FLOOR(RAND() * 3) + 3 AS weekly_assessment,    -- 3–5
    FLOOR(RAND() * 31) + 40 AS exam_score,         -- 40–70
    2 AS session_id,
    1 AS term_id,
    30 AS teacher_id,
    0,   -- placeholder total_score
    'Excellent performance. Keep it up!' AS remarks,
    'Approved' AS status,
    'Pending' AS status_test,
    0,0,0,0
FROM students s
JOIN subjectcombination subj 
    ON subj.Class = 'SS 2' AND subj.section = 'Q' AND subj.subject_type = 'Compulsory'
WHERE s.class = 'SS 1' AND s.section = 'Q';


-- 2. Insert elective subjects (Food & Nutrition, Animal Husbandry, Agricultural Science)
-- Rule: Rotate electives based on student_id
INSERT INTO results (
    student_id, subject, class, section,
    test_score, class_performance, weekly_assessment, exam_score,
    session_id, term_id, teacher_id, total_score, remarks,
    status, status_test,
    test_absent, class_performance_absent, weekly_assessment_absent, exam_absent
)
SELECT 
    s.id AS student_id,
    CASE 
        WHEN s.id % 3 = 0 THEN 'Food & Nutrition'
        WHEN s.id % 3 = 1 THEN 'Animal Husbandry'
        ELSE 'Agricultural Science'
    END AS subject,
    s.class,
    s.section,
    FLOOR(RAND() * 6) + 5 AS test_score,           -- 5–10
    FLOOR(RAND() * 11) + 5 AS class_performance,   -- 5–15
    FLOOR(RAND() * 3) + 3 AS weekly_assessment,    -- 3–5
    FLOOR(RAND() * 31) + 40 AS exam_score,         -- 40–70
    2 AS session_id,
    1 AS term_id,
    30 AS teacher_id,
    0,
    'Excellent performance. Keep it up!',
    'Approved',
    'Pending',
    0,0,0,0
FROM students s
WHERE s.class = 'SS 1' AND s.section = 'Q';


-- 3. Update total_score for all inserted rows
UPDATE results
SET total_score = test_score + class_performance + weekly_assessment + exam_score
WHERE class = 'SS 1' AND section = 'Q';


/* ===============================
   STEP 1: BACKUP SECTION R
   =============================== */
DROP TABLE IF EXISTS subjectcombination_backup_R;
CREATE TABLE subjectcombination_backup_R AS
SELECT * 
FROM subjectcombination
WHERE Class = 'SS 1' AND section = 'R';

/* ===============================
   STEP 2: MERGE Q INTO R
   =============================== */

-- Insert missing subjects from Q into R (as Elective)
INSERT INTO subjectcombination (Class, Subject_name, section, status, subject_type)
SELECT 'SS 1', q.Subject_name, 'R', q.status, 'Elective'
FROM subjectcombination q
LEFT JOIN subjectcombination r 
       ON q.Subject_name = r.Subject_name 
      AND r.Class = 'SS 1' AND r.section = 'R'
WHERE q.Class = 'SS 1' AND q.section = 'Q'
  AND r.Subject_name IS NULL;

-- Mark subjects that appear in both Q and R as Compulsory
UPDATE subjectcombination sc
JOIN (
    SELECT q.Subject_name
    FROM subjectcombination q
    INNER JOIN subjectcombination r 
        ON q.Subject_name = r.Subject_name
       AND q.Class = 'SS 1' AND q.section = 'Q'
       AND r.Class = 'SS 1' AND r.section = 'R'
) common ON sc.Subject_name = common.Subject_name
SET sc.subject_type = 'Compulsory'
WHERE sc.Class = 'SS 1' AND sc.section = 'R';

-- Mark subjects unique to either Q or R as Elective
UPDATE subjectcombination sc
SET sc.subject_type = 'Elective'
WHERE sc.Class = 'SS 1' AND sc.section = 'R'
  AND sc.Subject_name NOT IN (
      SELECT q.Subject_name
      FROM subjectcombination q
      INNER JOIN subjectcombination r 
        ON q.Subject_name = r.Subject_name
       AND q.Class = 'SS 1' AND q.section = 'Q'
       AND r.Class = 'SS 1' AND r.section = 'R'
  );

-- Remove duplicates inside Section R (keep only one copy, preferring lowest ID)
DELETE sc1 FROM subjectcombination sc1
JOIN subjectcombination sc2 
  ON sc1.Class = sc2.Class 
 AND sc1.Subject_name = sc2.Subject_name 
 AND sc1.section = sc2.section 
 AND sc1.id > sc2.id
WHERE sc1.Class = 'SS 1' AND sc1.section = 'R';

/* ===============================
   STEP 3: ROLLBACK OPTION
   =============================== */
/* 
   If something goes wrong, run ONLY the block below:
   ----------------------------------------------
   DELETE FROM subjectcombination
   WHERE Class = 'SS 1' AND section = 'R';

   INSERT INTO subjectcombination
   SELECT * FROM subjectcombination_backup_R;
   ----------------------------------------------
*/


-- Step 1: Delete old subjects in Q and R
DELETE FROM subjectcombination 
WHERE Class = 'SS 2' AND section IN ('Q', 'R');

-- Step 2: Insert merged subjects for R
INSERT INTO subjectcombination (Class, Subject_name, section, status, subject_type)
VALUES
('SS 2', 'English Language', 'R', 1, 'Compulsory'),
('SS 2', 'Mathematics', 'R', 1, 'Compulsory'),
('SS 2', 'Economics', 'R', 1, 'Compulsory'),
('SS 2', 'Yoruba', 'R', 1, 'Compulsory'),
('SS 2', 'Government', 'R', 1, 'Compulsory'),
('SS 2', 'Computer Studies', 'R', 1, 'Compulsory'),
('SS 2', 'Civic Education', 'R', 1, 'Compulsory'),
('SS 2', 'Biology', 'R', 1, 'Compulsory'),
('SS 2', 'Animal Husbandry', 'R', 1, 'Elective'),
('SS 2', 'Agricultural Science', 'R', 1, 'Elective'),
('SS 2', 'Literature', 'R', 1, 'Elective'),
('SS 2', 'History', 'R', 1, 'Elective'),
('SS 2', 'French', 'R', 1, 'Elective'),
('SS 2', 'C.R.S', 'R', 1, 'Elective'),
('SS 2', 'I.R.S', 'R', 1, 'Elective'),
('SS 2', 'Geography', 'R', 1, 'Elective'),
('SS 2', 'Commerce', 'R', 1, 'Elective'),
('SS 2', 'Financial Accounting', 'R', 1, 'Elective'),
('SS 2', 'Food & Nutrition', 'R', 1, 'Elective'),
('SS 2', 'Further Mathematics', 'R', 1, 'Elective');

-- Step 3: Insert merged subjects for Q
INSERT INTO subjectcombination (Class, Subject_name, section, status, subject_type)
VALUES
('SS 2', 'English Language', 'Q', 1, 'Compulsory'),
('SS 2', 'Mathematics', 'Q', 1, 'Compulsory'),
('SS 2', 'Economics', 'Q', 1, 'Compulsory'),
('SS 2', 'Yoruba', 'Q', 1, 'Compulsory'),
('SS 2', 'Government', 'Q', 1, 'Compulsory'),
('SS 2', 'Computer Studies', 'Q', 1, 'Compulsory'),
('SS 2', 'Civic Education', 'Q', 1, 'Compulsory'),
('SS 2', 'Biology', 'Q', 1, 'Compulsory'),
('SS 2', 'Animal Husbandry', 'Q', 1, 'Elective'),
('SS 2', 'Agricultural Science', 'Q', 1, 'Elective'),
('SS 2', 'Literature', 'Q', 1, 'Elective'),
('SS 2', 'History', 'Q', 1, 'Elective'),
('SS 2', 'French', 'Q', 1, 'Elective'),
('SS 2', 'C.R.S', 'Q', 1, 'Elective'),
('SS 2', 'I.R.S', 'Q', 1, 'Elective'),
('SS 2', 'Geography', 'Q', 1, 'Elective'),
('SS 2', 'Commerce', 'Q', 1, 'Elective'),
('SS 2', 'Financial Accounting', 'Q', 1, 'Elective'),
('SS 2', 'Food & Nutrition', 'Q', 1, 'Elective'),
('SS 2', 'Further Mathematics', 'Q', 1, 'Elective');

