-- ============================================================
-- MIGRATION: Shift Schedule v2
-- Date: 2026-05-11
-- Extends: tb_assignment_shift for weekly day-based schedule
-- ============================================================

ALTER TABLE `tb_assignment_shift`
  ADD COLUMN IF NOT EXISTS `hari_ke` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=semua hari, 1=Senin ... 7=Minggu' AFTER `id_referensi`,
  ADD COLUMN IF NOT EXISTS `is_hari_kerja` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '0=off/libur, 1=hari kerja' AFTER `hari_ke`;

ALTER TABLE `tb_assignment_shift`
  MODIFY COLUMN `id_shift` INT(11) NULL;

UPDATE `tb_assignment_shift`
SET `hari_ke` = COALESCE(`hari_ke`, 0),
    `is_hari_kerja` = COALESCE(`is_hari_kerja`, 1);

ALTER TABLE `tb_assignment_shift`
  ADD KEY `idx_asgn_ref_hari` (`tipe`, `id_referensi`, `hari_ke`, `berlaku_mulai`, `berlaku_selesai`);