-- CreateEnum
CREATE TYPE "PlenaryStatus" AS ENUM ('Draft', 'Sent');

-- CreateEnum
CREATE TYPE "RgcDecisionStatus" AS ENUM ('Draft', 'In Progress', 'Solved');

-- CreateTable
CREATE TABLE "plenaries" (
    "id" SERIAL NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    "meeting_date" DATE NOT NULL,
    "deadline" DATE,
    "document_reference" VARCHAR(255),
    "status" "PlenaryStatus" NOT NULL DEFAULT 'Draft',
    "user_id" INTEGER NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,
    "deleted_at" TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE "plenary_ministries" (
    "plenary_id" INTEGER NOT NULL,
    "stakeholder_id" INTEGER NOT NULL,

    CONSTRAINT "plenary_ministries_pkey" PRIMARY KEY ("plenary_id","stakeholder_id")
);

-- CreateTable
CREATE TABLE "plenary_rgc_decisions" (
    "id" SERIAL NOT NULL,
    "plenary_id" INTEGER NOT NULL,
    "stakeholder_id" INTEGER NOT NULL,
    "category_id" INTEGER NOT NULL,
    "meeting_date" DATE NOT NULL,
    "status" "RgcDecisionStatus" NOT NULL DEFAULT 'Draft',
    "focal_person" VARCHAR(255) NOT NULL,
    "decision" TEXT NOT NULL,
    "verification_source" TEXT,
    "user_id" INTEGER NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,
    "deleted_at" TIMESTAMP(3),

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

-- CreateIndex
CREATE INDEX "plenaries_user_id_idx" ON "plenaries"("user_id");

-- CreateIndex
CREATE INDEX "plenaries_meeting_date_idx" ON "plenaries"("meeting_date");

-- CreateIndex
CREATE INDEX "plenaries_status_idx" ON "plenaries"("status");

-- CreateIndex
CREATE INDEX "plenaries_deleted_at_idx" ON "plenaries"("deleted_at");

-- CreateIndex
CREATE INDEX "plenary_ministries_stakeholder_id_idx" ON "plenary_ministries"("stakeholder_id");

-- CreateIndex
CREATE INDEX "plenary_rgc_decisions_plenary_id_idx" ON "plenary_rgc_decisions"("plenary_id");

-- CreateIndex
CREATE INDEX "plenary_rgc_decisions_stakeholder_id_idx" ON "plenary_rgc_decisions"("stakeholder_id");

-- CreateIndex
CREATE INDEX "plenary_rgc_decisions_category_id_idx" ON "plenary_rgc_decisions"("category_id");

-- CreateIndex
CREATE INDEX "plenary_rgc_decisions_user_id_idx" ON "plenary_rgc_decisions"("user_id");

-- CreateIndex
CREATE INDEX "plenary_rgc_decisions_status_idx" ON "plenary_rgc_decisions"("status");

-- CreateIndex
CREATE INDEX "plenary_rgc_decisions_deleted_at_idx" ON "plenary_rgc_decisions"("deleted_at");

-- AddForeignKey
ALTER TABLE "plenary_ministries" ADD CONSTRAINT "plenary_ministries_plenary_id_fkey" FOREIGN KEY ("plenary_id") REFERENCES "plenaries"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "plenary_rgc_decisions" ADD CONSTRAINT "plenary_rgc_decisions_plenary_id_fkey" FOREIGN KEY ("plenary_id") REFERENCES "plenaries"("id") ON DELETE CASCADE ON UPDATE CASCADE;
