-- IAM 完整终态迁移
-- 一次性引入 09-iam-security.md 规则要求的 schema 变更：
-- 1. DataScopeType / FieldAccess 枚举
-- 2. DataScope / RoleDataScope 表（Layer 4 行级数据权限）
-- 3. PermissionDelegation 表（§5.3.14 委托）
-- 4. FieldPermission 表（§5.4 字段级权限）
-- 5. IamAuditLog 表（§5.3.3.2 配置变更审计）
-- 6. UserRole 增加 Access Review 字段（§5.3.15）

-- ============================================================
-- 1. CreateEnum: DataScopeType
-- ============================================================
CREATE TYPE "platform_iam"."DataScopeType" AS ENUM (
  'SELF',
  'DEPARTMENT',
  'DEPARTMENT_TREE',
  'ORGANIZATION',
  'REGION',
  'ALL',
  'CUSTOM'
);

-- ============================================================
-- 2. CreateEnum: FieldAccess
-- ============================================================
CREATE TYPE "platform_iam"."FieldAccess" AS ENUM (
  'VISIBLE',
  'READONLY',
  'HIDDEN',
  'DESENSITIZE'
);

-- ============================================================
-- 3. CreateTable: data_scopes
-- ============================================================
CREATE TABLE "platform_iam"."data_scopes" (
  "id"            UUID NOT NULL,
  "code"          TEXT NOT NULL,
  "name"          TEXT NOT NULL,
  "scope_type"    "platform_iam"."DataScopeType" NOT NULL,
  "rules"         JSONB,
  "is_built_in"   BOOLEAN NOT NULL DEFAULT true,
  "created_at"    TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at"    TIMESTAMPTZ(3) NOT NULL,

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

CREATE UNIQUE INDEX "data_scopes_code_key" ON "platform_iam"."data_scopes"("code");

-- ============================================================
-- 4. CreateTable: role_data_scope_rel
-- ============================================================
CREATE TABLE "platform_iam"."role_data_scope_rel" (
  "id"             UUID NOT NULL,
  "role_id"        UUID NOT NULL,
  "data_scope_id"  UUID NOT NULL,
  "resource"       TEXT NOT NULL DEFAULT '*',
  "created_at"     TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

CREATE UNIQUE INDEX "role_data_scope_rel_role_id_data_scope_id_resource_key"
  ON "platform_iam"."role_data_scope_rel"("role_id", "data_scope_id", "resource");
CREATE INDEX "role_data_scope_rel_role_id_resource_idx"
  ON "platform_iam"."role_data_scope_rel"("role_id", "resource");
CREATE INDEX "role_data_scope_rel_data_scope_id_idx"
  ON "platform_iam"."role_data_scope_rel"("data_scope_id");

ALTER TABLE "platform_iam"."role_data_scope_rel"
  ADD CONSTRAINT "role_data_scope_rel_role_id_fkey"
  FOREIGN KEY ("role_id") REFERENCES "platform_iam"."roles"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "platform_iam"."role_data_scope_rel"
  ADD CONSTRAINT "role_data_scope_rel_data_scope_id_fkey"
  FOREIGN KEY ("data_scope_id") REFERENCES "platform_iam"."data_scopes"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

-- ============================================================
-- 5. AlterTable: user_role_rel 加 Access Review 字段（§5.3.15）
-- ============================================================
ALTER TABLE "platform_iam"."user_role_rel"
  ADD COLUMN "last_reviewed_at" TIMESTAMPTZ(3),
  ADD COLUMN "last_reviewed_by" UUID,
  ADD COLUMN "review_comment"   TEXT;

CREATE INDEX "user_role_rel_last_reviewed_at_idx"
  ON "platform_iam"."user_role_rel"("last_reviewed_at");

-- ============================================================
-- 6. CreateTable: permission_delegations（§5.3.14）
-- ============================================================
CREATE TABLE "platform_iam"."permission_delegations" (
  "id"               UUID NOT NULL,
  "from_user_id"     UUID NOT NULL,
  "to_user_id"       UUID NOT NULL,
  "resource"         TEXT NOT NULL DEFAULT '*',
  "valid_from"       TIMESTAMPTZ(3) NOT NULL,
  "valid_to"         TIMESTAMPTZ(3) NOT NULL,
  "reason"           TEXT NOT NULL,
  "organization_id"  UUID,
  "created_by_id"    UUID NOT NULL,
  "created_at"       TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at"       TIMESTAMPTZ(3) NOT NULL,
  "revoked_at"       TIMESTAMPTZ(3),

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

CREATE INDEX "permission_delegations_to_user_id_valid_from_valid_to_idx"
  ON "platform_iam"."permission_delegations"("to_user_id", "valid_from", "valid_to");
CREATE INDEX "permission_delegations_from_user_id_idx"
  ON "platform_iam"."permission_delegations"("from_user_id");
CREATE INDEX "permission_delegations_organization_id_idx"
  ON "platform_iam"."permission_delegations"("organization_id");

-- ============================================================
-- 7. CreateTable: field_permissions（§5.4）
-- ============================================================
CREATE TABLE "platform_iam"."field_permissions" (
  "id"          UUID NOT NULL,
  "role_id"     UUID NOT NULL,
  "resource"    TEXT NOT NULL,
  "field"       TEXT NOT NULL,
  "access"      "platform_iam"."FieldAccess" NOT NULL,
  "created_at"  TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at"  TIMESTAMPTZ(3) NOT NULL,

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

CREATE UNIQUE INDEX "field_permissions_role_id_resource_field_key"
  ON "platform_iam"."field_permissions"("role_id", "resource", "field");
CREATE INDEX "field_permissions_resource_field_idx"
  ON "platform_iam"."field_permissions"("resource", "field");

-- ============================================================
-- 8. CreateTable: iam_audit_log（§5.3.3.2）
-- ============================================================
CREATE TABLE "platform_iam"."iam_audit_log" (
  "id"          UUID NOT NULL,
  "actor"       UUID NOT NULL,
  "action"      VARCHAR(20) NOT NULL,
  "resource"    VARCHAR(64) NOT NULL,
  "target_id"   UUID,
  "before"      JSONB,
  "after"       JSONB,
  "ip"          TEXT,
  "user_agent"  TEXT,
  "timestamp"   TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

CREATE INDEX "iam_audit_log_actor_timestamp_idx"
  ON "platform_iam"."iam_audit_log"("actor", "timestamp");
CREATE INDEX "iam_audit_log_resource_timestamp_idx"
  ON "platform_iam"."iam_audit_log"("resource", "timestamp");
