-- Site Attendance v1.5: 共享签到 + QR 轮换 + SIGNED 访问模式
-- 参考：docs/modules/site-attendance/06-data-model.md (迁移策略)

-- ============================================================
-- 1. 新增枚举 SiteCheckpointAccessMode
-- ============================================================

CREATE TYPE "platform_site_attendance"."SiteCheckpointAccessMode" AS ENUM ('PUBLIC', 'SIGNED');

-- ============================================================
-- 2. SiteCheckpoint 扩展 6 个字段（存量行获得默认值）
-- ============================================================

ALTER TABLE "platform_site_attendance"."site_checkpoints"
  ADD COLUMN "access_mode" "platform_site_attendance"."SiteCheckpointAccessMode" NOT NULL DEFAULT 'PUBLIC',
  ADD COLUMN "qr_rotation_seconds" INTEGER,
  ADD COLUMN "qr_grace_seconds" INTEGER NOT NULL DEFAULT 120,
  ADD COLUMN "shared_checkin_enabled" BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN "shared_company_id" VARCHAR(64),
  ADD COLUMN "shared_company_label" VARCHAR(200);

CREATE INDEX "site_checkpoints_shared_checkin_enabled_is_active_idx"
  ON "platform_site_attendance"."site_checkpoints" ("shared_checkin_enabled", "is_active");

-- ============================================================
-- 3. SharedCheckinPartner（合作伙伴）
-- ============================================================

CREATE TABLE "platform_site_attendance"."shared_checkin_partners" (
  "id"            VARCHAR(32) NOT NULL,
  "checkpoint_id" VARCHAR(32) NOT NULL,
  "company_id"    VARCHAR(64) NOT NULL,
  "company_label" VARCHAR(200) NOT NULL,
  "display_label" VARCHAR(200),
  "target_url"    VARCHAR(1000) NOT NULL,
  "is_active"     BOOLEAN NOT NULL DEFAULT true,
  "sort_order"    INTEGER NOT NULL DEFAULT 0,
  "created_by"    UUID NOT NULL,
  "updated_by"    UUID NOT NULL,
  "created_at"    TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at"    TIMESTAMPTZ(3) NOT NULL,

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

CREATE INDEX "shared_checkin_partners_checkpoint_id_is_active_sort_order_idx"
  ON "platform_site_attendance"."shared_checkin_partners" ("checkpoint_id", "is_active", "sort_order");

ALTER TABLE "platform_site_attendance"."shared_checkin_partners"
  ADD CONSTRAINT "shared_checkin_partners_checkpoint_id_fkey"
    FOREIGN KEY ("checkpoint_id")
    REFERENCES "platform_site_attendance"."site_checkpoints" ("id")
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "platform_site_attendance"."shared_checkin_partners"
  ADD CONSTRAINT "shared_checkin_partners_created_by_fkey"
    FOREIGN KEY ("created_by")
    REFERENCES "platform_iam"."users" ("id")
    ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE "platform_site_attendance"."shared_checkin_partners"
  ADD CONSTRAINT "shared_checkin_partners_updated_by_fkey"
    FOREIGN KEY ("updated_by")
    REFERENCES "platform_iam"."users" ("id")
    ON DELETE RESTRICT ON UPDATE CASCADE;

-- ============================================================
-- 4. SharedCheckinTicketUsage（Ticket nonce 一次性去重）
-- ============================================================

CREATE TABLE "platform_site_attendance"."shared_checkin_ticket_usage" (
  "nonce"      VARCHAR(64) NOT NULL,
  "used_at"    TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "expires_at" TIMESTAMPTZ(3) NOT NULL,

  CONSTRAINT "shared_checkin_ticket_usage_pkey" PRIMARY KEY ("nonce")
);

CREATE INDEX "shared_checkin_ticket_usage_expires_at_idx"
  ON "platform_site_attendance"."shared_checkin_ticket_usage" ("expires_at");
