-- ============================================================================
-- timetable_periods.period_type
--
-- The period editor (modules/timetable/periods.php) exposes a "Period Type"
-- selector and modules/timetable/ajax/save_period.php writes it, but the column
-- was never added to the schema — so every save failed outright with
-- "Unknown column 'period_type' in 'field list'".
--
-- Read paths already tolerate its absence: timetableGetPeriodType() in
-- modules/timetable/helpers.php falls back to matching the period name. This
-- migration backfills existing rows with exactly the same rules, so nothing has
-- to be retyped by hand.
--
-- VARCHAR rather than ENUM: helpers.php can infer 'worship' and 'closing_time',
-- which are not offered by the editor's selector, and an ENUM would reject them.
--
-- Idempotent (ADD COLUMN IF NOT EXISTS — MariaDB-safe).
-- ============================================================================

ALTER TABLE `timetable_periods`
  ADD COLUMN IF NOT EXISTS `period_type` VARCHAR(30) NOT NULL DEFAULT 'teaching' AFTER `is_break`;

-- Backfill, mirroring timetableGetPeriodType() precedence exactly. Only rows
-- still holding the default are touched, so any type set by hand is preserved.
UPDATE `timetable_periods`
SET `period_type` = CASE
        WHEN LOWER(`period_name`) REGEXP '\\bnap\\b'                  THEN 'nap'
        WHEN LOWER(`period_name`) LIKE '%circle%'                     THEN 'circle_time'
        WHEN LOWER(`period_name`) LIKE '%assembly%'                   THEN 'assembly'
        WHEN LOWER(`period_name`) REGEXP '\\blunch\\b|launch'         THEN 'lunch'
        WHEN LOWER(`period_name`) LIKE '%recess%'                     THEN 'recess'
        WHEN LOWER(`period_name`) REGEXP 'arrival|settling|reporting' THEN 'arrival'
        WHEN LOWER(`period_name`) LIKE '%routine%'                    THEN 'routines'
        WHEN LOWER(`period_name`) LIKE '%silent%'                     THEN 'silent_hour'
        WHEN LOWER(`period_name`) LIKE '%worship%'                    THEN 'worship'
        WHEN LOWER(`period_name`) LIKE '%devotion%'                   THEN 'devotion'
        WHEN LOWER(`period_name`) REGEXP 'assignment|checking|marking' THEN 'assignment_checking'
        WHEN LOWER(`period_name`) REGEXP 'sanitation|clean|sweep'     THEN 'sanitation'
        WHEN LOWER(`period_name`) REGEXP 'sports|games|\\bp\\.?e\\.?\\b' THEN 'sports'
        WHEN LOWER(`period_name`) LIKE '%library%'                    THEN 'library'
        WHEN LOWER(`period_name`) REGEXP '\\bpreps?\\b|study'          THEN 'prep'
        WHEN LOWER(`period_name`) LIKE '%club%'                       THEN 'club'
        WHEN LOWER(`period_name`) LIKE '%inspection%'                 THEN 'inspection'
        WHEN LOWER(`period_name`) REGEXP 'roll ?call'                 THEN 'roll_call'
        WHEN LOWER(`period_name`) LIKE '%story%'                      THEN 'story_time'
        WHEN LOWER(`period_name`) REGEXP 'dining|feeding'             THEN 'dining'
        WHEN LOWER(`period_name`) REGEXP 'extra class|remedial'       THEN 'extra_classes'
        WHEN LOWER(`period_name`) LIKE '%general activit%'            THEN 'general_activity'
        WHEN LOWER(`period_name`) LIKE '%activit%'                    THEN 'class_activities'
        WHEN LOWER(`period_name`) LIKE '%closing%'                    THEN 'closing_time'
        WHEN `is_break` = 1                                           THEN 'break'
        ELSE 'teaching'
    END
WHERE `period_type` = 'teaching';
