-- ============================================================================
-- Phase 5: Exam paper template library
--
-- 8 curated system templates a school can select in the Paper Builder to
-- pre-structure a paper (duration, total marks, instructions) and the
-- auto-generate question mix. Schools can add their own rows later (is_system=0)
-- via the Phase 6 builder. Idempotent: safe to run multiple times.
-- ============================================================================

CREATE TABLE IF NOT EXISTS `exam_paper_templates` (
  `id`          INT(11)      NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(120) NOT NULL,
  `description` VARCHAR(255) NOT NULL DEFAULT '',
  `level`       VARCHAR(30)  NOT NULL DEFAULT '',
  `structure`   LONGTEXT     NOT NULL,            -- JSON: form defaults + auto-generate mix
  `is_system`   TINYINT(1)   NOT NULL DEFAULT 0,
  `is_active`   TINYINT(1)   NOT NULL DEFAULT 1,
  `created_by`  INT(11)      DEFAULT NULL,
  `created_at`  TIMESTAMP    NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_tmpl_active` (`is_active`, `is_system`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Seed the 8 system templates (idempotent by name).
INSERT INTO `exam_paper_templates` (`name`, `description`, `level`, `structure`, `is_system`)
SELECT * FROM (SELECT
  'BECE Mock (JHS)' AS name,
  'Junior High mock in BECE style: 40 objectives plus structured theory.' AS description,
  'jhs' AS level,
  '{"duration_minutes":90,"total_marks":100,"instructions":"SECTION A: Answer ALL 40 objective questions. SECTION B: Answer any FOUR questions. Show all working clearly.","auto":{"mcq_count":40,"theory_count":6,"fill_count":0,"easy_pct":30,"medium_pct":50,"hard_pct":20}}' AS structure,
  1 AS is_system
) AS t WHERE NOT EXISTS (SELECT 1 FROM `exam_paper_templates` WHERE `name` = 'BECE Mock (JHS)');

INSERT INTO `exam_paper_templates` (`name`, `description`, `level`, `structure`, `is_system`)
SELECT * FROM (SELECT
  'WASSCE-Style (SHS)',
  'Senior High paper: a large objective section plus essay/structured questions.',
  'shs',
  '{"duration_minutes":120,"total_marks":100,"instructions":"SECTION A: Answer ALL objective questions. SECTION B: Answer any SIX questions, choosing at least TWO from each part.","auto":{"mcq_count":50,"theory_count":6,"fill_count":0,"easy_pct":25,"medium_pct":50,"hard_pct":25}}',
  1
) AS t WHERE NOT EXISTS (SELECT 1 FROM `exam_paper_templates` WHERE `name` = 'WASSCE-Style (SHS)');

INSERT INTO `exam_paper_templates` (`name`, `description`, `level`, `structure`, `is_system`)
SELECT * FROM (SELECT
  'End-of-Term Examination',
  'Balanced end-of-term paper mixing objectives, fill-in, and theory.',
  '',
  '{"duration_minutes":90,"total_marks":100,"instructions":"Answer ALL questions in Section A and Section B. Write neatly and show your working where required.","auto":{"mcq_count":30,"theory_count":5,"fill_count":5,"easy_pct":35,"medium_pct":45,"hard_pct":20}}',
  1
) AS t WHERE NOT EXISTS (SELECT 1 FROM `exam_paper_templates` WHERE `name` = 'End-of-Term Examination');

INSERT INTO `exam_paper_templates` (`name`, `description`, `level`, `structure`, `is_system`)
SELECT * FROM (SELECT
  'Mid-Term Quiz',
  'Short objective-led quiz for a mid-term check.',
  '',
  '{"duration_minutes":40,"total_marks":40,"instructions":"Answer ALL questions. Choose the best option for each objective question.","auto":{"mcq_count":20,"theory_count":0,"fill_count":5,"easy_pct":50,"medium_pct":40,"hard_pct":10}}',
  1
) AS t WHERE NOT EXISTS (SELECT 1 FROM `exam_paper_templates` WHERE `name` = 'Mid-Term Quiz');

INSERT INTO `exam_paper_templates` (`name`, `description`, `level`, `structure`, `is_system`)
SELECT * FROM (SELECT
  'Mock Examination (Full)',
  'Full-length mock with a heavy objective section and extended theory.',
  '',
  '{"duration_minutes":120,"total_marks":100,"instructions":"This is a full mock examination. Answer ALL of Section A and the required number of questions in Section B within the time allowed.","auto":{"mcq_count":50,"theory_count":8,"fill_count":0,"easy_pct":25,"medium_pct":45,"hard_pct":30}}',
  1
) AS t WHERE NOT EXISTS (SELECT 1 FROM `exam_paper_templates` WHERE `name` = 'Mock Examination (Full)');

INSERT INTO `exam_paper_templates` (`name`, `description`, `level`, `structure`, `is_system`)
SELECT * FROM (SELECT
  'Class Test (Short)',
  'Quick low-stakes class test.',
  '',
  '{"duration_minutes":30,"total_marks":30,"instructions":"Answer ALL questions within the time given.","auto":{"mcq_count":15,"theory_count":2,"fill_count":0,"easy_pct":50,"medium_pct":40,"hard_pct":10}}',
  1
) AS t WHERE NOT EXISTS (SELECT 1 FROM `exam_paper_templates` WHERE `name` = 'Class Test (Short)');

INSERT INTO `exam_paper_templates` (`name`, `description`, `level`, `structure`, `is_system`)
SELECT * FROM (SELECT
  'Primary Lower (Basic 1-3)',
  'Gentle paper for lower primary: mostly objectives and fill-in.',
  'lower_primary',
  '{"duration_minutes":45,"total_marks":50,"instructions":"Answer ALL questions. Read each question carefully before you answer.","auto":{"mcq_count":20,"theory_count":0,"fill_count":5,"easy_pct":60,"medium_pct":35,"hard_pct":5}}',
  1
) AS t WHERE NOT EXISTS (SELECT 1 FROM `exam_paper_templates` WHERE `name` = 'Primary Lower (Basic 1-3)');

INSERT INTO `exam_paper_templates` (`name`, `description`, `level`, `structure`, `is_system`)
SELECT * FROM (SELECT
  'Continuous Assessment Test',
  'Mixed continuous-assessment test feeding the SBA class score.',
  '',
  '{"duration_minutes":45,"total_marks":40,"instructions":"Answer ALL questions. This test counts towards your continuous assessment.","auto":{"mcq_count":20,"theory_count":3,"fill_count":2,"easy_pct":40,"medium_pct":45,"hard_pct":15}}',
  1
) AS t WHERE NOT EXISTS (SELECT 1 FROM `exam_paper_templates` WHERE `name` = 'Continuous Assessment Test');
