-- CreateEnum
CREATE TYPE "IssueResolveStatus" AS ENUM ('SOLVED', 'IN_PROGRESS', 'NOT_ADDRESSED');

-- CreateTable
CREATE TABLE "escalates" (
    "id" SERIAL NOT NULL,
    "name" TEXT NOT NULL,
    "user_id" INTEGER NOT NULL,
    "contact_person_id" INTEGER NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,
    "deleted_at" TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE "issue_resolve_agencies" (
    "id" SERIAL NOT NULL,
    "issue_resolve_id" INTEGER NOT NULL,
    "stakeholder_id" INTEGER NOT NULL,

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

-- CreateTable
CREATE TABLE "issue_resolves" (
    "id" SERIAL NOT NULL,
    "meeting_summary_id" INTEGER NOT NULL,
    "issue_id" INTEGER NOT NULL,
    "status" "IssueResolveStatus" NOT NULL DEFAULT 'NOT_ADDRESSED',
    "escalate_id" INTEGER,
    "rgc_decision" JSONB,
    "next_step" JSONB,
    "remark" JSONB,
    "document_reference" VARCHAR(255),
    "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 "issue_resolves_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "meeting_summaries" (
    "id" SERIAL NOT NULL,
    "meeting_request_id" INTEGER NOT NULL,
    "meeting_id" INTEGER,
    "document_reference" VARCHAR(255),
    "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 "meeting_summaries_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE INDEX "escalates_user_id_idx" ON "escalates"("user_id");

-- CreateIndex
CREATE INDEX "escalates_deleted_at_idx" ON "escalates"("deleted_at");

-- CreateIndex
CREATE INDEX "issue_resolve_agencies_issue_resolve_id_idx" ON "issue_resolve_agencies"("issue_resolve_id");

-- CreateIndex
CREATE INDEX "issue_resolve_agencies_stakeholder_id_idx" ON "issue_resolve_agencies"("stakeholder_id");

-- CreateIndex
CREATE INDEX "issue_resolves_meeting_summary_id_idx" ON "issue_resolves"("meeting_summary_id");

-- CreateIndex
CREATE INDEX "issue_resolves_issue_id_idx" ON "issue_resolves"("issue_id");

-- CreateIndex
CREATE INDEX "issue_resolves_escalate_id_idx" ON "issue_resolves"("escalate_id");

-- CreateIndex
CREATE INDEX "issue_resolves_user_id_idx" ON "issue_resolves"("user_id");

-- CreateIndex
CREATE INDEX "issue_resolves_status_idx" ON "issue_resolves"("status");

-- CreateIndex
CREATE INDEX "issue_resolves_deleted_at_idx" ON "issue_resolves"("deleted_at");

-- CreateIndex
CREATE INDEX "meeting_summaries_meeting_request_id_idx" ON "meeting_summaries"("meeting_request_id");

-- CreateIndex
CREATE INDEX "meeting_summaries_meeting_id_idx" ON "meeting_summaries"("meeting_id");

-- CreateIndex
CREATE INDEX "meeting_summaries_user_id_idx" ON "meeting_summaries"("user_id");

-- CreateIndex
CREATE INDEX "meeting_summaries_deleted_at_idx" ON "meeting_summaries"("deleted_at");

-- AddForeignKey
ALTER TABLE "escalates" ADD CONSTRAINT "escalates_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "issue_resolve_agencies" ADD CONSTRAINT "issue_resolve_agencies_issue_resolve_id_fkey" FOREIGN KEY ("issue_resolve_id") REFERENCES "issue_resolves"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "issue_resolve_agencies" ADD CONSTRAINT "issue_resolve_agencies_stakeholder_id_fkey" FOREIGN KEY ("stakeholder_id") REFERENCES "stakeholders"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "issue_resolves" ADD CONSTRAINT "issue_resolves_meeting_summary_id_fkey" FOREIGN KEY ("meeting_summary_id") REFERENCES "meeting_summaries"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "issue_resolves" ADD CONSTRAINT "issue_resolves_issue_id_fkey" FOREIGN KEY ("issue_id") REFERENCES "issues"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "issue_resolves" ADD CONSTRAINT "issue_resolves_escalate_id_fkey" FOREIGN KEY ("escalate_id") REFERENCES "escalates"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "issue_resolves" ADD CONSTRAINT "issue_resolves_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "meeting_summaries" ADD CONSTRAINT "meeting_summaries_meeting_request_id_fkey" FOREIGN KEY ("meeting_request_id") REFERENCES "meeting_requests"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "meeting_summaries" ADD CONSTRAINT "meeting_summaries_meeting_id_fkey" FOREIGN KEY ("meeting_id") REFERENCES "meetings"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "meeting_summaries" ADD CONSTRAINT "meeting_summaries_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
