-- ═══════════════════════════════════════════════════════════════════════
-- Multi-Level Academic Calendar  (add-on: multi_calendar)
-- Adds a programme-level dimension to school_terms so SHS / JHS / Primary can
-- run the same term on different dates. Backwards compatible: existing rows
-- become the "All" (school-wide) calendar and remain the fallback.
-- Idempotent on MariaDB (XAMPP). The app also self-heals via includes/calendar_lib.php.
-- ═══════════════════════════════════════════════════════════════════════

-- The base table (created by ajax/school_calendar_ajax.php on first use).
CREATE TABLE IF NOT EXISTS `school_terms` (
    `id`            INT AUTO_INCREMENT PRIMARY KEY,
    `academic_year` VARCHAR(20) NOT NULL,
    `term`          VARCHAR(20) NOT NULL,
    `start_date`    DATE NOT NULL,
    `end_date`      DATE NOT NULL,
    `created_at`    DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Add the programme-level dimension (default 'All' = school-wide).
ALTER TABLE `school_terms`
    ADD COLUMN IF NOT EXISTS `programme_level` VARCHAR(30) NOT NULL DEFAULT 'All' AFTER `term`;

-- Swap the old (year,term) unique key for one that includes the level.
ALTER TABLE `school_terms` DROP INDEX IF EXISTS `unique_term`;
ALTER TABLE `school_terms`
    ADD UNIQUE KEY IF NOT EXISTS `unique_term_level` (`academic_year`, `term`, `programme_level`);
