-- Employee archive fields aligned with personnel files
ALTER TABLE "Employee" ADD COLUMN "gender" TEXT;
ALTER TABLE "Employee" ADD COLUMN "email" TEXT;
ALTER TABLE "Employee" ADD COLUMN "mobile" TEXT;
ALTER TABLE "Employee" ADD COLUMN "idNo" TEXT;
ALTER TABLE "Employee" ADD COLUMN "education" TEXT;
ALTER TABLE "Employee" ADD COLUMN "bankName" TEXT;
ALTER TABLE "Employee" ADD COLUMN "bankAccount" TEXT;
ALTER TABLE "Employee" ADD COLUMN "remark" TEXT;

CREATE TABLE "HrSetting" (
    "id" TEXT NOT NULL DEFAULT 'default',
    "tenantId" TEXT NOT NULL DEFAULT '1',
    "idMask" BOOLEAN NOT NULL DEFAULT true,
    "autoAccount" BOOLEAN NOT NULL DEFAULT false,
    "probationMonths" INTEGER NOT NULL DEFAULT 3,
    "defaultRest" TEXT NOT NULL DEFAULT 'SINGLE',
    "workStart" TEXT NOT NULL DEFAULT '09:00',
    "workEnd" TEXT NOT NULL DEFAULT '18:00',
    "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

CREATE UNIQUE INDEX "HrSetting_tenantId_key" ON "HrSetting"("tenantId");

INSERT INTO "HrSetting" ("id", "tenantId", "idMask", "autoAccount", "probationMonths", "defaultRest", "workStart", "workEnd", "updatedAt")
VALUES ('default', '1', true, false, 3, 'SINGLE', '09:00', '18:00', CURRENT_TIMESTAMP);

CREATE TABLE "AttendanceRule" (
    "id" TEXT NOT NULL,
    "tenantId" TEXT NOT NULL DEFAULT '1',
    "name" TEXT NOT NULL,
    "restSchedule" TEXT NOT NULL,
    "workStart" TEXT NOT NULL,
    "workEnd" TEXT NOT NULL,
    "lateGraceMinutes" INTEGER NOT NULL DEFAULT 0,
    "enabled" BOOLEAN NOT NULL DEFAULT true,
    "sortNo" INTEGER NOT NULL DEFAULT 0,
    "remark" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

CREATE UNIQUE INDEX "AttendanceRule_tenantId_name_key" ON "AttendanceRule"("tenantId", "name");

INSERT INTO "AttendanceRule" ("id", "tenantId", "name", "restSchedule", "workStart", "workEnd", "lateGraceMinutes", "enabled", "sortNo", "remark", "createdAt", "updatedAt")
VALUES
  ('att-rule-single-000000000001', '1', '单休班次', 'SINGLE', '09:00', '18:00', 0, true, 1, '每周休一天，薪酬按 26.09 天核算', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
  ('att-rule-double-000000000001', '1', '双休班次', 'DOUBLE', '09:00', '18:00', 0, true, 2, '每周休两天，薪酬按 21.75 天核算', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

INSERT INTO "DictItem" ("id", "tenantId", "dictType", "label", "value", "sortNo")
SELECT d."id", '1', d."dictType", d."label", d."value", d."sortNo"
FROM (VALUES
  ('dict-gender-male-00000000001', 'gender', '男', 'MALE', 1),
  ('dict-gender-female-000000001', 'gender', '女', 'FEMALE', 2),
  ('dict-edu-high-0000000000001', 'education', '高中及以下', 'HIGH_SCHOOL', 1),
  ('dict-edu-college-00000000001', 'education', '专科', 'COLLEGE', 2),
  ('dict-edu-bachelor-0000000001', 'education', '本科', 'BACHELOR', 3),
  ('dict-edu-master-000000000001', 'education', '硕士', 'MASTER', 4),
  ('dict-edu-doctor-000000000001', 'education', '博士', 'DOCTOR', 5)
) AS d("id", "dictType", "label", "value", "sortNo")
WHERE NOT EXISTS (
  SELECT 1 FROM "DictItem" x WHERE x."tenantId" = '1' AND x."dictType" = d."dictType" AND x."value" = d."value"
);