-- v1.0 议程能力（Meeting Agenda Management）schema 落地
-- 事实源：docs/modules/meeting-attendance/06-data-model.md
-- 新增 2 个 enum + 5 个 entity + 多个 index + 跨 schema FK（platform_iam.users / corp_hr.organizations）
-- 由 `prisma migrate diff --from-url --to-schema-datamodel --script` 生成后手工裁剪只保留本次新增部分
--
-- 段级列模式（columnLabels）+ 议程项分列描述（columnDescriptions）：见 docs/modules/meeting-attendance/06-data-model.md
-- 替代了原 AgendaCategoryTag enum（v1.0 设计废弃，PR 内一并清理）

-- =============================================================================
-- 1. CreateEnum × 2
-- =============================================================================

-- 议程项上传任务状态
CREATE TYPE "platform_meeting_attendance"."UploadTaskStatus" AS ENUM ('PENDING', 'UPLOADED', 'CANCELLED');

-- 会议级附件分类
CREATE TYPE "platform_meeting_attendance"."MeetingAttachmentCategory" AS ENUM ('MINUTES', 'MATERIAL', 'PRESENTATION', 'OTHER');

-- =============================================================================
-- 2. CreateTable × 5
-- =============================================================================

-- 议程段（meeting → 多个 section）
CREATE TABLE "platform_meeting_attendance"."meeting_agenda_sections" (
    "id" UUID NOT NULL,
    "meeting_id" VARCHAR(32) NOT NULL,
    "order" INTEGER NOT NULL DEFAULT 0,
    "title" VARCHAR(200) NOT NULL,
    "column_labels" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
    "created_by_id" UUID NOT NULL,
    "organization_id" UUID,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,
    "deleted_at" TIMESTAMPTZ(3),

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

-- 议程项（section → 多个 item）
CREATE TABLE "platform_meeting_attendance"."meeting_agenda_items" (
    "id" UUID NOT NULL,
    "section_id" UUID NOT NULL,
    "order" INTEGER NOT NULL DEFAULT 0,
    "title" VARCHAR(200) NOT NULL,
    "description" TEXT,
    "column_descriptions" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
    "code" VARCHAR(64),
    "time_minutes" INTEGER,
    "presenter_user_id" UUID,
    "created_by_id" UUID NOT NULL,
    "organization_id" UUID,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,
    "deleted_at" TIMESTAMPTZ(3),

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

-- 议程项上传任务（item → 多个 assignee 上传任务）
CREATE TABLE "platform_meeting_attendance"."meeting_agenda_item_upload_tasks" (
    "id" UUID NOT NULL,
    "agenda_item_id" UUID NOT NULL,
    "assignee_user_id" UUID NOT NULL,
    "assigned_by_id" UUID NOT NULL,
    "status" "platform_meeting_attendance"."UploadTaskStatus" NOT NULL DEFAULT 'PENDING',
    "due_at" TIMESTAMPTZ(3),
    "assigned_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "completed_at" TIMESTAMPTZ(3),
    "created_by_id" UUID NOT NULL,
    "organization_id" UUID,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,
    "deleted_at" TIMESTAMPTZ(3),

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

-- 议程项级附件（item → 多个 file）
CREATE TABLE "platform_meeting_attendance"."meeting_agenda_item_attachments" (
    "id" UUID NOT NULL,
    "agenda_item_id" UUID NOT NULL,
    "uploaded_by_id" UUID NOT NULL,
    "filename" VARCHAR(255) NOT NULL,
    "mime_type" VARCHAR(128) NOT NULL,
    "size" BIGINT NOT NULL,
    "storage_path" VARCHAR(512) NOT NULL,
    "uploaded_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "created_by_id" UUID NOT NULL,
    "organization_id" UUID,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,
    "deleted_at" TIMESTAMPTZ(3),

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

-- 会议级附件（meeting → 纪要/材料/PPT 等）
CREATE TABLE "platform_meeting_attendance"."meeting_attachments" (
    "id" UUID NOT NULL,
    "meeting_id" VARCHAR(32) NOT NULL,
    "uploaded_by_id" UUID NOT NULL,
    "category" "platform_meeting_attendance"."MeetingAttachmentCategory",
    "filename" VARCHAR(255) NOT NULL,
    "mime_type" VARCHAR(128) NOT NULL,
    "size" BIGINT NOT NULL,
    "storage_path" VARCHAR(512) NOT NULL,
    "uploaded_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "created_by_id" UUID NOT NULL,
    "organization_id" UUID,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,
    "deleted_at" TIMESTAMPTZ(3),

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

-- =============================================================================
-- 3. CreateIndex × 13
-- =============================================================================

-- meeting_agenda_sections
CREATE INDEX "meeting_agenda_sections_meeting_id_order_idx" ON "platform_meeting_attendance"."meeting_agenda_sections"("meeting_id", "order");
CREATE INDEX "meeting_agenda_sections_meeting_id_deleted_at_idx" ON "platform_meeting_attendance"."meeting_agenda_sections"("meeting_id", "deleted_at");
CREATE INDEX "meeting_agenda_sections_organization_id_deleted_at_idx" ON "platform_meeting_attendance"."meeting_agenda_sections"("organization_id", "deleted_at");

-- meeting_agenda_items
CREATE INDEX "meeting_agenda_items_section_id_order_idx" ON "platform_meeting_attendance"."meeting_agenda_items"("section_id", "order");
CREATE INDEX "meeting_agenda_items_section_id_deleted_at_idx" ON "platform_meeting_attendance"."meeting_agenda_items"("section_id", "deleted_at");
CREATE INDEX "meeting_agenda_items_organization_id_deleted_at_idx" ON "platform_meeting_attendance"."meeting_agenda_items"("organization_id", "deleted_at");

-- meeting_agenda_item_upload_tasks
CREATE INDEX "meeting_agenda_item_upload_tasks_assignee_user_id_status_de_idx" ON "platform_meeting_attendance"."meeting_agenda_item_upload_tasks"("assignee_user_id", "status", "deleted_at");
CREATE INDEX "meeting_agenda_item_upload_tasks_agenda_item_id_deleted_at_idx" ON "platform_meeting_attendance"."meeting_agenda_item_upload_tasks"("agenda_item_id", "deleted_at");
CREATE INDEX "meeting_agenda_item_upload_tasks_organization_id_deleted_at_idx" ON "platform_meeting_attendance"."meeting_agenda_item_upload_tasks"("organization_id", "deleted_at");

-- meeting_agenda_item_attachments
CREATE INDEX "meeting_agenda_item_attachments_agenda_item_id_deleted_at_idx" ON "platform_meeting_attendance"."meeting_agenda_item_attachments"("agenda_item_id", "deleted_at");
CREATE INDEX "meeting_agenda_item_attachments_organization_id_deleted_at_idx" ON "platform_meeting_attendance"."meeting_agenda_item_attachments"("organization_id", "deleted_at");

-- meeting_attachments
CREATE INDEX "meeting_attachments_meeting_id_uploaded_at_idx" ON "platform_meeting_attendance"."meeting_attachments"("meeting_id", "uploaded_at");
CREATE INDEX "meeting_attachments_meeting_id_deleted_at_idx" ON "platform_meeting_attendance"."meeting_attachments"("meeting_id", "deleted_at");
CREATE INDEX "meeting_attachments_organization_id_deleted_at_idx" ON "platform_meeting_attendance"."meeting_attachments"("organization_id", "deleted_at");

-- =============================================================================
-- 4. AddForeignKey × 18（含 3 个跨 schema FK 到 platform_iam.users 与 corp_hr.organizations）
-- =============================================================================

-- meeting_agenda_sections FK
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_sections" ADD CONSTRAINT "meeting_agenda_sections_meeting_id_fkey" FOREIGN KEY ("meeting_id") REFERENCES "platform_meeting_attendance"."meetings"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_sections" ADD CONSTRAINT "meeting_agenda_sections_created_by_id_fkey" FOREIGN KEY ("created_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_sections" ADD CONSTRAINT "meeting_agenda_sections_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "corp_hr"."organizations"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- meeting_agenda_items FK
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_items" ADD CONSTRAINT "meeting_agenda_items_section_id_fkey" FOREIGN KEY ("section_id") REFERENCES "platform_meeting_attendance"."meeting_agenda_sections"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_items" ADD CONSTRAINT "meeting_agenda_items_presenter_user_id_fkey" FOREIGN KEY ("presenter_user_id") REFERENCES "platform_iam"."users"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_items" ADD CONSTRAINT "meeting_agenda_items_created_by_id_fkey" FOREIGN KEY ("created_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_items" ADD CONSTRAINT "meeting_agenda_items_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "corp_hr"."organizations"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- meeting_agenda_item_upload_tasks FK
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_item_upload_tasks" ADD CONSTRAINT "meeting_agenda_item_upload_tasks_agenda_item_id_fkey" FOREIGN KEY ("agenda_item_id") REFERENCES "platform_meeting_attendance"."meeting_agenda_items"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_item_upload_tasks" ADD CONSTRAINT "meeting_agenda_item_upload_tasks_assignee_user_id_fkey" FOREIGN KEY ("assignee_user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_item_upload_tasks" ADD CONSTRAINT "meeting_agenda_item_upload_tasks_assigned_by_id_fkey" FOREIGN KEY ("assigned_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_item_upload_tasks" ADD CONSTRAINT "meeting_agenda_item_upload_tasks_created_by_id_fkey" FOREIGN KEY ("created_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_item_upload_tasks" ADD CONSTRAINT "meeting_agenda_item_upload_tasks_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "corp_hr"."organizations"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- meeting_agenda_item_attachments FK
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_item_attachments" ADD CONSTRAINT "meeting_agenda_item_attachments_agenda_item_id_fkey" FOREIGN KEY ("agenda_item_id") REFERENCES "platform_meeting_attendance"."meeting_agenda_items"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_item_attachments" ADD CONSTRAINT "meeting_agenda_item_attachments_uploaded_by_id_fkey" FOREIGN KEY ("uploaded_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_item_attachments" ADD CONSTRAINT "meeting_agenda_item_attachments_created_by_id_fkey" FOREIGN KEY ("created_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_agenda_item_attachments" ADD CONSTRAINT "meeting_agenda_item_attachments_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "corp_hr"."organizations"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- meeting_attachments FK
ALTER TABLE "platform_meeting_attendance"."meeting_attachments" ADD CONSTRAINT "meeting_attachments_meeting_id_fkey" FOREIGN KEY ("meeting_id") REFERENCES "platform_meeting_attendance"."meetings"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_attachments" ADD CONSTRAINT "meeting_attachments_uploaded_by_id_fkey" FOREIGN KEY ("uploaded_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_attachments" ADD CONSTRAINT "meeting_attachments_created_by_id_fkey" FOREIGN KEY ("created_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "platform_meeting_attendance"."meeting_attachments" ADD CONSTRAINT "meeting_attachments_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "corp_hr"."organizations"("id") ON DELETE SET NULL ON UPDATE CASCADE;
