-- 钉钉模块：员工生命周期与年假缓存增强
-- 1) 年假配额快照：JSON 缓存使用/释放记录
-- 2) 员工在职周期表（离职再入职支持）
-- 3) 员工累计司龄缓存列
-- 4) 员工停薪留职记录表

-- ── 1) 年假配额快照增加 JSON 缓存字段 ──
ALTER TABLE "platform_automation"."dingtalk_leave_quota_snapshots"
  ADD COLUMN "usage_records" JSONB,
  ADD COLUMN "release_records" JSONB;

-- ── 2) 员工在职周期表 ──
CREATE TABLE "platform_automation"."dingtalk_employee_employment_periods" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "user_id" TEXT NOT NULL,
    "period_index" INTEGER NOT NULL,
    "join_date" DATE NOT NULL,
    "leave_date" DATE,
    "count_in_tenure" BOOLEAN NOT NULL DEFAULT true,
    "note" TEXT NOT NULL DEFAULT '',
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

CREATE UNIQUE INDEX "dingtalk_employee_employment_periods_user_index_key"
  ON "platform_automation"."dingtalk_employee_employment_periods"("user_id", "period_index");

CREATE INDEX "dingtalk_employee_employment_periods_user_id_idx"
  ON "platform_automation"."dingtalk_employee_employment_periods"("user_id");

-- ── 3) 员工累计司龄缓存列 ──
ALTER TABLE "platform_automation"."dingtalk_employees"
  ADD COLUMN "tenure_days" INTEGER NOT NULL DEFAULT 0;

-- ── 4) 员工停薪留职记录表 ──
CREATE TABLE "platform_automation"."dingtalk_employee_suspension_periods" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "user_id" TEXT NOT NULL,
    "start_date" DATE NOT NULL,
    "end_date" DATE,
    "reason" TEXT NOT NULL DEFAULT '',
    "note" TEXT NOT NULL DEFAULT '',
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

CREATE INDEX "dingtalk_employee_suspension_periods_user_id_idx"
  ON "platform_automation"."dingtalk_employee_suspension_periods"("user_id");
