-- CreateSchema
CREATE SCHEMA IF NOT EXISTS "platform_internal_apps";

-- CreateEnum
CREATE TYPE "platform_internal_apps"."InternalAppRuntime" AS ENUM ('node', 'static');

-- CreateEnum
CREATE TYPE "platform_internal_apps"."InternalAppStatus" AS ENUM ('PENDING', 'BUILDING', 'HEALTHY', 'FAILED', 'DISABLED', 'DESTROYED', 'DISABLED_ARCHIVED', 'PURGED');

-- CreateEnum
CREATE TYPE "platform_internal_apps"."InternalAppDeploymentStatus" AS ENUM ('PENDING', 'BUILDING', 'HEALTH_CHECKING', 'SUCCESS', 'FAILED', 'ROLLED_BACK');

-- CreateEnum
CREATE TYPE "platform_internal_apps"."InternalAppDeploymentTrigger" AS ENUM ('deploy', 'env_change', 'admin_restart');

-- CreateEnum
CREATE TYPE "platform_internal_apps"."InternalAppTokenStatus" AS ENUM ('ACTIVE', 'REVOKED', 'DISABLED', 'EXPIRED');

-- CreateEnum
CREATE TYPE "platform_internal_apps"."InternalAppTokenRevokeReason" AS ENUM ('self', 'admin', 'entra_disabled', 'rotated');

-- CreateTable
CREATE TABLE "platform_internal_apps"."employee_slug_bindings" (
    "id" UUID NOT NULL,
    "user_id" UUID NOT NULL,
    "employee_slug" VARCHAR(20) NOT NULL,
    "source_mail_nickname" VARCHAR(64) 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,

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

-- CreateTable
CREATE TABLE "platform_internal_apps"."internal_apps" (
    "id" UUID NOT NULL,
    "employee_slug" VARCHAR(20) NOT NULL,
    "app_slug" VARCHAR(22) NOT NULL,
    "display_name" VARCHAR(64),
    "runtime" "platform_internal_apps"."InternalAppRuntime" NOT NULL,
    "status" "platform_internal_apps"."InternalAppStatus" NOT NULL DEFAULT 'PENDING',
    "url" VARCHAR(255) NOT NULL,
    "gitea_repo_full_name" VARCHAR(100) NOT NULL,
    "last_deployed_at" TIMESTAMPTZ(3),
    "current_deployment_id" UUID,
    "destroyed_at" TIMESTAMPTZ(3),
    "retention_until" TIMESTAMPTZ(3),
    "force_disabled_at" TIMESTAMPTZ(3),
    "force_disabled_reason" VARCHAR(500),
    "force_disabled_by_id" UUID,
    "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,

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

-- CreateTable
CREATE TABLE "platform_internal_apps"."internal_app_deployments" (
    "id" UUID NOT NULL,
    "app_id" UUID NOT NULL,
    "commit_sha" VARCHAR(40),
    "status" "platform_internal_apps"."InternalAppDeploymentStatus" NOT NULL DEFAULT 'PENDING',
    "build_log_summary" TEXT,
    "health_check_log" JSONB,
    "started_at" TIMESTAMPTZ(3),
    "finished_at" TIMESTAMPTZ(3),
    "trigger" "platform_internal_apps"."InternalAppDeploymentTrigger" 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,

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

-- CreateTable
CREATE TABLE "platform_internal_apps"."internal_app_env_vars" (
    "id" UUID NOT NULL,
    "app_id" UUID NOT NULL,
    "key" VARCHAR(64) NOT NULL,
    "value_encrypted" BYTEA NOT NULL,
    "value_iv" BYTEA NOT NULL,
    "kms_key_version" INTEGER 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,

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

-- CreateTable
CREATE TABLE "platform_internal_apps"."internal_app_employee_tokens" (
    "id" UUID NOT NULL,
    "employee_slug" VARCHAR(20) NOT NULL,
    "token_hash" CHAR(64) NOT NULL,
    "prefix" VARCHAR(8) NOT NULL,
    "status" "platform_internal_apps"."InternalAppTokenStatus" NOT NULL DEFAULT 'ACTIVE',
    "issued_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "expires_at" TIMESTAMPTZ(3) NOT NULL,
    "revoked_at" TIMESTAMPTZ(3),
    "revoked_reason" "platform_internal_apps"."InternalAppTokenRevokeReason",
    "last_used_at" TIMESTAMPTZ(3),
    "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,

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

-- CreateIndex
CREATE UNIQUE INDEX "employee_slug_bindings_user_id_key" ON "platform_internal_apps"."employee_slug_bindings"("user_id");

-- CreateIndex
CREATE UNIQUE INDEX "employee_slug_bindings_employee_slug_key" ON "platform_internal_apps"."employee_slug_bindings"("employee_slug");

-- CreateIndex
CREATE INDEX "employee_slug_bindings_organization_id_idx" ON "platform_internal_apps"."employee_slug_bindings"("organization_id");

-- CreateIndex
CREATE UNIQUE INDEX "internal_apps_gitea_repo_full_name_key" ON "platform_internal_apps"."internal_apps"("gitea_repo_full_name");

-- CreateIndex
CREATE INDEX "internal_apps_status_idx" ON "platform_internal_apps"."internal_apps"("status");

-- CreateIndex
CREATE INDEX "internal_apps_created_by_id_idx" ON "platform_internal_apps"."internal_apps"("created_by_id");

-- CreateIndex
CREATE INDEX "internal_apps_organization_id_idx" ON "platform_internal_apps"."internal_apps"("organization_id");

-- CreateIndex
CREATE INDEX "internal_apps_retention_until_idx" ON "platform_internal_apps"."internal_apps"("retention_until");

-- CreateIndex
CREATE UNIQUE INDEX "internal_apps_employee_slug_app_slug_key" ON "platform_internal_apps"."internal_apps"("employee_slug", "app_slug");

-- CreateIndex
CREATE INDEX "internal_app_deployments_app_id_created_at_idx" ON "platform_internal_apps"."internal_app_deployments"("app_id", "created_at" DESC);

-- CreateIndex
CREATE INDEX "internal_app_deployments_status_idx" ON "platform_internal_apps"."internal_app_deployments"("status");

-- CreateIndex
CREATE INDEX "internal_app_deployments_organization_id_idx" ON "platform_internal_apps"."internal_app_deployments"("organization_id");

-- CreateIndex
CREATE INDEX "internal_app_env_vars_app_id_idx" ON "platform_internal_apps"."internal_app_env_vars"("app_id");

-- CreateIndex
CREATE INDEX "internal_app_env_vars_organization_id_idx" ON "platform_internal_apps"."internal_app_env_vars"("organization_id");

-- CreateIndex
CREATE UNIQUE INDEX "internal_app_env_vars_app_id_key_key" ON "platform_internal_apps"."internal_app_env_vars"("app_id", "key");

-- CreateIndex
CREATE UNIQUE INDEX "internal_app_employee_tokens_token_hash_key" ON "platform_internal_apps"."internal_app_employee_tokens"("token_hash");

-- CreateIndex
CREATE INDEX "internal_app_employee_tokens_employee_slug_idx" ON "platform_internal_apps"."internal_app_employee_tokens"("employee_slug");

-- CreateIndex
CREATE INDEX "internal_app_employee_tokens_status_idx" ON "platform_internal_apps"."internal_app_employee_tokens"("status");

-- CreateIndex
CREATE INDEX "internal_app_employee_tokens_expires_at_idx" ON "platform_internal_apps"."internal_app_employee_tokens"("expires_at");

-- CreateIndex
CREATE INDEX "internal_app_employee_tokens_organization_id_idx" ON "platform_internal_apps"."internal_app_employee_tokens"("organization_id");

-- AddForeignKey
ALTER TABLE "platform_internal_apps"."employee_slug_bindings" ADD CONSTRAINT "employee_slug_bindings_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_internal_apps"."internal_apps" ADD CONSTRAINT "internal_apps_employee_slug_fkey" FOREIGN KEY ("employee_slug") REFERENCES "platform_internal_apps"."employee_slug_bindings"("employee_slug") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_internal_apps"."internal_apps" ADD CONSTRAINT "internal_apps_created_by_id_fkey" FOREIGN KEY ("created_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_internal_apps"."internal_apps" ADD CONSTRAINT "internal_apps_force_disabled_by_id_fkey" FOREIGN KEY ("force_disabled_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_internal_apps"."internal_app_deployments" ADD CONSTRAINT "internal_app_deployments_app_id_fkey" FOREIGN KEY ("app_id") REFERENCES "platform_internal_apps"."internal_apps"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_internal_apps"."internal_app_deployments" ADD CONSTRAINT "internal_app_deployments_created_by_id_fkey" FOREIGN KEY ("created_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_internal_apps"."internal_app_env_vars" ADD CONSTRAINT "internal_app_env_vars_app_id_fkey" FOREIGN KEY ("app_id") REFERENCES "platform_internal_apps"."internal_apps"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_internal_apps"."internal_app_env_vars" ADD CONSTRAINT "internal_app_env_vars_created_by_id_fkey" FOREIGN KEY ("created_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_internal_apps"."internal_app_employee_tokens" ADD CONSTRAINT "internal_app_employee_tokens_employee_slug_fkey" FOREIGN KEY ("employee_slug") REFERENCES "platform_internal_apps"."employee_slug_bindings"("employee_slug") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_internal_apps"."internal_app_employee_tokens" ADD CONSTRAINT "internal_app_employee_tokens_created_by_id_fkey" FOREIGN KEY ("created_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;


-- ============================================================================
-- Additional constraints not expressible in Prisma schema
-- 详见 docs/modules/internal-app-platform/06-data-model.md
-- ============================================================================

-- CHECK: employee_slug 字符集 + 长度（PRD §核心约束）
ALTER TABLE "platform_internal_apps"."employee_slug_bindings"
  ADD CONSTRAINT "check_employee_slug_format"
  CHECK (employee_slug ~ '^[a-z0-9]([a-z0-9-]{1,18}[a-z0-9])?$');

-- CHECK: app_slug 字符集 + 长度
ALTER TABLE "platform_internal_apps"."internal_apps"
  ADD CONSTRAINT "check_app_slug_format"
  CHECK (app_slug ~ '^[a-z0-9]([a-z0-9-]{1,20}[a-z0-9])?$');

-- CHECK: env key 命名规范（[A-Z_][A-Z0-9_]*）
ALTER TABLE "platform_internal_apps"."internal_app_env_vars"
  ADD CONSTRAINT "check_env_key_format"
  CHECK (key ~ '^[A-Z_][A-Z0-9_]{0,63}$');

-- Partial index: 同一员工同时只能有一个 ACTIVE token（强不变量，详见 06 §2.5）
CREATE UNIQUE INDEX "uq_tokens_active_per_employee"
  ON "platform_internal_apps"."internal_app_employee_tokens" (employee_slug)
  WHERE status = 'ACTIVE';

-- Partial index: 加速 TTL Sweeper 扫 retention_until
CREATE INDEX "idx_internal_apps_retention_due"
  ON "platform_internal_apps"."internal_apps" (retention_until)
  WHERE retention_until IS NOT NULL;

-- Partial index: 加速到期 token 扫描
CREATE INDEX "idx_tokens_active_expires"
  ON "platform_internal_apps"."internal_app_employee_tokens" (expires_at)
  WHERE status = 'ACTIVE';
