-- ADP PTO 同步：复用 platform_automation 同步中心
-- 1. 加 ADP_SYNC 到 AutomationTaskType 枚举
-- 2. 新建 AdpPtoSchedule 表（隐私合规：只存何人/何时不在）
-- 3. User 表加 adp_aoid + adp_linked_at（外部 ID 模式，与 Entra 一致）

-- ===== AlterEnum =====
ALTER TYPE "platform_automation"."AutomationTaskType" ADD VALUE 'ADP_SYNC';

-- ===== AlterTable =====
ALTER TABLE "platform_iam"."users"
  ADD COLUMN "adp_aoid" VARCHAR(64),
  ADD COLUMN "adp_linked_at" TIMESTAMPTZ(3);

CREATE UNIQUE INDEX "users_adp_aoid_key" ON "platform_iam"."users"("adp_aoid");

-- ===== CreateTable =====
CREATE TABLE "platform_automation"."adp_pto_schedules" (
    "id" VARCHAR(32) NOT NULL,
    "user_id" UUID NOT NULL,
    "adp_aoid" VARCHAR(64) NOT NULL,
    "leave_date" DATE NOT NULL,
    "start_time" TIMESTAMPTZ(3) NOT NULL,
    "end_time" TIMESTAMPTZ(3) NOT NULL,
    "adp_entry_id" VARCHAR(128) NOT NULL,
    "synced_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "adp_pto_schedules_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "adp_pto_schedules_user_id_adp_entry_id_key"
  ON "platform_automation"."adp_pto_schedules"("user_id", "adp_entry_id");

CREATE INDEX "adp_pto_schedules_user_id_leave_date_idx"
  ON "platform_automation"."adp_pto_schedules"("user_id", "leave_date");

CREATE INDEX "adp_pto_schedules_start_time_end_time_idx"
  ON "platform_automation"."adp_pto_schedules"("start_time", "end_time");

ALTER TABLE "platform_automation"."adp_pto_schedules"
  ADD CONSTRAINT "adp_pto_schedules_user_id_fkey"
  FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;
