-- Work Activity Plan: step sebelum Work Activities (realisasi)
-- Aman: hanya CREATE tabel baru; optional ALTER workactivities.
-- Tabel: workactivityplans, workactivityplanitems (Squelize default + createdAt/updatedAt).

-- 1. Tabel header plan (satu per employee per bulan)
CREATE TABLE IF NOT EXISTS `workactivityplans` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `creation` DATETIME NOT NULL,
  `modified` DATETIME NOT NULL,
  `modified_by` INT(11) NOT NULL,
  `owner` INT(11) NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `employee_id` INT(11) NULL,
  `company_id` INT(11) NULL,
  `period_month` INT(11) NOT NULL COMMENT '1-12',
  `period_year` INT(11) NOT NULL,
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_workactivityplans_employee_period` (`employee_id`, `period_year`, `period_month`),
  KEY `idx_workactivityplans_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Plan WFH/WFO per employee per bulan';

-- 2. Tabel detail plan per tanggal (hanya hari kerja; libur dari holiday)
CREATE TABLE IF NOT EXISTS `workactivityplanitems` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `creation` DATETIME NOT NULL,
  `modified` DATETIME NOT NULL,
  `modified_by` INT(11) NOT NULL,
  `owner` INT(11) NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `work_activity_plan_id` INT(11) NOT NULL,
  `plan_date` DATE NOT NULL,
  `work_type` VARCHAR(10) NOT NULL COMMENT 'WFO or WFH',
  `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_workactivityplanitems_plan_date` (`work_activity_plan_id`, `plan_date`),
  KEY `idx_workactivityplanitems_plan_id` (`work_activity_plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Plan WFO/WFH per tanggal';

-- 3. Doctype untuk Workflow (Work Activity Plan), permission: work-activity-plan, ID = 246
INSERT IGNORE INTO `doctypes` (`id`, `name`, `display_name`, `creation`, `modified`, `modified_by`, `owner`, `enabled`)
VALUES (246, 'work-activity-plan', 'Work Activity Plan', NOW(), NOW(), 1, 1, 1);

-- 4. (Optional) Link realisasi ke plan — jalankan jika ingin FK di workactivities
-- ALTER TABLE `workactivities`
--   ADD COLUMN `work_activity_plan_id` INT(11) NULL DEFAULT NULL AFTER `company_id`,
--   ADD KEY `idx_workactivities_plan_id` (`work_activity_plan_id`);