-- ═══════════════════════════════════════════════════
-- QAFYS — 006 Academic Years & Quarters Schema
-- Stores historical & active school years with 4 quarters
-- ═══════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `academic_years` (
  `id`           VARCHAR(20) NOT NULL,            -- e.g. '2024-2025', '2025-2026', '2026-2027'
  `label`        VARCHAR(100) NOT NULL,           -- e.g. '2026–2027 Academic Year'
  `start_date`   DATE NOT NULL,
  `end_date`     DATE NOT NULL,
  `is_active`    TINYINT(1) DEFAULT 0,            -- 1 = current active school year
  `quarters`     LONGTEXT NOT NULL,               -- JSON String: [{q: 'Q1', start: '...', end: '...'}, ...]
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert Seed Academic Years
INSERT INTO `academic_years` (`id`, `label`, `start_date`, `end_date`, `is_active`, `quarters`) VALUES
  (
    '2024-2025',
    '2024–2025 Academic Year',
    '2024-09-03',
    '2025-06-20',
    0,
    '[{"q":"Q1","start":"2024-09-03","end":"2024-11-08"},{"q":"Q2","start":"2024-11-12","end":"2025-01-24"},{"q":"Q3","start":"2025-01-27","end":"2025-04-04"},{"q":"Q4","start":"2025-04-07","end":"2025-06-20"}]'
  ),
  (
    '2025-2026',
    '2025–2026 Academic Year',
    '2025-09-02',
    '2026-06-25',
    0,
    '[{"q":"Q1","start":"2025-09-02","end":"2025-11-07"},{"q":"Q2","start":"2025-11-11","end":"2026-01-23"},{"q":"Q3","start":"2026-01-26","end":"2026-04-03"},{"q":"Q4","start":"2026-04-06","end":"2026-06-25"}]'
  ),
  (
    '2026-2027',
    '2026–2027 Academic Year',
    '2026-09-01',
    '2027-06-30',
    1,
    '[{"q":"Q1","start":"2026-09-01","end":"2026-11-06"},{"q":"Q2","start":"2026-11-10","end":"2027-01-22"},{"q":"Q3","start":"2027-01-25","end":"2027-04-02"},{"q":"Q4","start":"2027-04-05","end":"2027-06-30"}]'
  )
ON DUPLICATE KEY UPDATE
  `label` = VALUES(`label`),
  `start_date` = VALUES(`start_date`),
  `end_date` = VALUES(`end_date`),
  `is_active` = VALUES(`is_active`),
  `quarters` = VALUES(`quarters`);