-- CreateTable
CREATE TABLE "progress_report_updates" (
    "id" SERIAL NOT NULL,
    "progress_report_id" INTEGER NOT NULL,
    "ministry_id" INTEGER NOT NULL,
    "issue_id" INTEGER NOT NULL,
    "issue_status_id" INTEGER NOT NULL,
    "issue_resolve_id" INTEGER NOT NULL,
    "indicators" JSONB,
    "progress_solution" JSONB,
    "implementation_challenges" JSONB,
    "requests" JSONB,
    "source_of_verification" TEXT,
    "link_to_verification_source" TEXT,
    "date_of_issue_solution" TIMESTAMP(3),
    "attachement" VARCHAR(500),
    "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 "progress_report_updates_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE INDEX "progress_report_updates_progress_report_id_idx" ON "progress_report_updates"("progress_report_id");

-- CreateIndex
CREATE INDEX "progress_report_updates_ministry_id_idx" ON "progress_report_updates"("ministry_id");

-- CreateIndex
CREATE INDEX "progress_report_updates_issue_id_idx" ON "progress_report_updates"("issue_id");

-- CreateIndex
CREATE INDEX "progress_report_updates_issue_status_id_idx" ON "progress_report_updates"("issue_status_id");

-- CreateIndex
CREATE INDEX "progress_report_updates_indicators_idx" ON "progress_report_updates"("indicators");

-- CreateIndex
CREATE INDEX "progress_report_updates_progress_solution_idx" ON "progress_report_updates"("progress_solution");

-- CreateIndex
CREATE INDEX "progress_report_updates_implementation_challenges_idx" ON "progress_report_updates"("implementation_challenges");

-- CreateIndex
CREATE INDEX "progress_report_updates_requests_idx" ON "progress_report_updates"("requests");

-- CreateIndex
CREATE INDEX "progress_report_updates_source_of_verification_idx" ON "progress_report_updates"("source_of_verification");

-- CreateIndex
CREATE INDEX "progress_report_updates_link_to_verification_source_idx" ON "progress_report_updates"("link_to_verification_source");

-- CreateIndex
CREATE INDEX "progress_report_updates_date_of_issue_solution_idx" ON "progress_report_updates"("date_of_issue_solution");

-- CreateIndex
CREATE INDEX "progress_report_updates_attachement_idx" ON "progress_report_updates"("attachement");

-- CreateIndex
CREATE INDEX "progress_report_updates_user_id_idx" ON "progress_report_updates"("user_id");

-- CreateIndex
CREATE INDEX "progress_report_updates_deleted_at_idx" ON "progress_report_updates"("deleted_at");

-- CreateIndex
CREATE UNIQUE INDEX "progress_report_updates_progress_report_id_ministry_id_issu_key" ON "progress_report_updates"("progress_report_id", "ministry_id", "issue_id");

-- AddForeignKey
ALTER TABLE "progress_report_updates" ADD CONSTRAINT "progress_report_updates_progress_report_id_fkey" FOREIGN KEY ("progress_report_id") REFERENCES "progress_reports"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE "progress_report_updates" ADD CONSTRAINT "progress_report_updates_issue_status_id_fkey" FOREIGN KEY ("issue_status_id") REFERENCES "issue_statuses"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

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

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

-- AddForeignKey
ALTER TABLE "progress_report_updates" ADD CONSTRAINT "progress_report_updates_ministry_id_fkey" FOREIGN KEY ("ministry_id") REFERENCES "stakeholders"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
