-- ============================================================================
-- Phase 2: SBA (School-Based Assessment) cumulative assessment
--
-- Adds component-level continuous assessment that rolls up into the existing
-- `scores.raw_class_score` and flows through to terminal reports unchanged.
--
-- Level-configurable weighting (Lower Primary / Upper Primary / JHS / SHS)
-- is stored in the existing `settings` table; the engine falls back to 30/70.
-- Idempotent: safe to run multiple times.
-- ============================================================================

-- Individual assessment items per class-subject (Class Test, Project, etc.).
CREATE TABLE IF NOT EXISTS `sba_component_types` (
  `id`             INT(11)       NOT NULL AUTO_INCREMENT,
  `class_name`     VARCHAR(20)   NOT NULL,
  `subject_id`     INT(11)       NOT NULL,
  `teacher_id`     INT(11)       DEFAULT NULL,
  `component_name` VARCHAR(80)   NOT NULL,
  `max_score`      DECIMAL(6,2)  NOT NULL DEFAULT 100.00,
  `weight`         DECIMAL(6,2)  NOT NULL DEFAULT 1.00,
  `display_order`  INT(11)       NOT NULL DEFAULT 0,
  `term`           VARCHAR(20)   NOT NULL,
  `academic_year`  VARCHAR(20)   NOT NULL,
  `is_active`      TINYINT(1)    NOT NULL DEFAULT 1,
  `created_at`     TIMESTAMP     NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_sba_ct_scope` (`class_name`, `subject_id`, `term`, `academic_year`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- One score per student per component.
CREATE TABLE IF NOT EXISTS `sba_scores` (
  `id`                INT(11)      NOT NULL AUTO_INCREMENT,
  `student_id`        INT(11)      NOT NULL,
  `component_type_id` INT(11)      NOT NULL,
  `score`             DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  `term`              VARCHAR(20)  NOT NULL,
  `academic_year`     VARCHAR(20)  NOT NULL,
  `created_at`        TIMESTAMP    NOT NULL DEFAULT current_timestamp(),
  `updated_at`        TIMESTAMP    NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_sba_score` (`student_id`, `component_type_id`),
  KEY `idx_sba_score_ct` (`component_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Level-based weighting defaults (CA percent / exam percent). Stored as "ca/exam".
-- Seeded to the Ghana basic-education default of 30/70; editable per school per level.
INSERT INTO `settings` (`setting_key`, `setting_value`)
SELECT * FROM (SELECT 'sba_weight_lower_primary' AS k, '30/70' AS v) AS t
WHERE NOT EXISTS (SELECT 1 FROM `settings` WHERE `setting_key` = 'sba_weight_lower_primary');

INSERT INTO `settings` (`setting_key`, `setting_value`)
SELECT * FROM (SELECT 'sba_weight_upper_primary' AS k, '30/70' AS v) AS t
WHERE NOT EXISTS (SELECT 1 FROM `settings` WHERE `setting_key` = 'sba_weight_upper_primary');

INSERT INTO `settings` (`setting_key`, `setting_value`)
SELECT * FROM (SELECT 'sba_weight_jhs' AS k, '30/70' AS v) AS t
WHERE NOT EXISTS (SELECT 1 FROM `settings` WHERE `setting_key` = 'sba_weight_jhs');

INSERT INTO `settings` (`setting_key`, `setting_value`)
SELECT * FROM (SELECT 'sba_weight_shs' AS k, '30/70' AS v) AS t
WHERE NOT EXISTS (SELECT 1 FROM `settings` WHERE `setting_key` = 'sba_weight_shs');
