-- 运营中心 M365 休眠账号：复用 platform_automation 同步中心
-- 1. 启用 citext 扩展（用于 user_principal_name 大小写不敏感比较）
-- 2. 加 M365_DORMANT_SYNC 到 AutomationTaskType 枚举
-- 3. 新建 platform_ops_center schema 与两张表（M365User + M365UserActivityChange）
-- 4. M365UserActivityChangeField 枚举
--
-- 注意：跨 schema FK 指向 platform_automation.AutomationExecution.id 不通过外键约束维护
-- （Prisma 多 schema 限制 + 同步元数据生命周期独立），仅靠 UUID 列保留引用

-- ===== Extension =====
CREATE EXTENSION IF NOT EXISTS citext;

-- ===== Schema =====
CREATE SCHEMA IF NOT EXISTS "platform_ops_center";

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

-- ===== CreateEnum =====
CREATE TYPE "platform_ops_center"."M365UserActivityChangeField" AS ENUM (
    'lastSignInDateTime',
    'lastNonInteractiveSignInDateTime',
    'lastEmailActivity',
    'lastOneDriveActivity',
    'lastTeamsActivity',
    'lastSharePointActivity',
    'accountEnabled',
    'hasLicense',
    'licenses'
);

-- ===== CreateTable: m365_users =====
CREATE TABLE "platform_ops_center"."m365_users" (
    "id" UUID NOT NULL,
    "organization_id" UUID NOT NULL,
    "created_by_id" UUID NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

    "graph_user_id" VARCHAR(64) NOT NULL,
    "user_principal_name" CITEXT NOT NULL,
    "display_name" TEXT,
    "mail" TEXT,
    "department" TEXT,
    "job_title" TEXT,

    "account_enabled" BOOLEAN NOT NULL,
    "has_license" BOOLEAN NOT NULL,
    "licenses" JSONB NOT NULL DEFAULT '[]',

    "last_sign_in_at" TIMESTAMPTZ(3),
    "last_non_interactive_sign_in_at" TIMESTAMPTZ(3),
    "last_email_activity_at" TIMESTAMPTZ(3),
    "last_one_drive_activity_at" TIMESTAMPTZ(3),
    "last_teams_activity_at" TIMESTAMPTZ(3),
    "last_share_point_activity_at" TIMESTAMPTZ(3),

    "last_any_activity_at" TIMESTAMPTZ(3),
    "days_inactive" INTEGER,

    "account_created_at" TIMESTAMPTZ(3),

    "first_seen_in_execution_id" UUID NOT NULL,
    "last_seen_in_execution_id" UUID NOT NULL,

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

CREATE UNIQUE INDEX "m365_users_organization_id_graph_user_id_key"
  ON "platform_ops_center"."m365_users"("organization_id", "graph_user_id");

CREATE INDEX "m365_users_organization_id_days_inactive_idx"
  ON "platform_ops_center"."m365_users"("organization_id", "days_inactive" DESC);

CREATE INDEX "m365_users_organization_id_user_principal_name_idx"
  ON "platform_ops_center"."m365_users"("organization_id", "user_principal_name");

CREATE INDEX "m365_users_organization_id_last_seen_in_execution_id_idx"
  ON "platform_ops_center"."m365_users"("organization_id", "last_seen_in_execution_id");

-- ===== CreateTable: m365_user_activity_changes =====
CREATE TABLE "platform_ops_center"."m365_user_activity_changes" (
    "id" UUID NOT NULL,
    "organization_id" UUID NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    "user_id" UUID NOT NULL,
    "execution_id" UUID NOT NULL,

    "field" "platform_ops_center"."M365UserActivityChangeField" NOT NULL,
    "previous_value" TEXT,
    "current_value" TEXT,

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

CREATE INDEX "m365_user_activity_changes_user_id_created_at_idx"
  ON "platform_ops_center"."m365_user_activity_changes"("user_id", "created_at" DESC);

CREATE INDEX "m365_user_activity_changes_organization_id_execution_id_idx"
  ON "platform_ops_center"."m365_user_activity_changes"("organization_id", "execution_id");

CREATE INDEX "m365_user_activity_changes_organization_id_field_created_at_idx"
  ON "platform_ops_center"."m365_user_activity_changes"("organization_id", "field", "created_at" DESC);

-- ===== AddForeignKey =====
ALTER TABLE "platform_ops_center"."m365_user_activity_changes"
  ADD CONSTRAINT "m365_user_activity_changes_user_id_fkey"
  FOREIGN KEY ("user_id") REFERENCES "platform_ops_center"."m365_users"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;
