-- CreateTable
CREATE TABLE "issue_statuses" (
    "id" SERIAL NOT NULL,
    "name" VARCHAR(100) NOT NULL,
    "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_statuses_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "issue_categories" (
    "id" SERIAL NOT NULL,
    "name" VARCHAR(100) NOT NULL,
    "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_categories_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "meeting_requests" (
    "id" SERIAL NOT NULL,
    "title" VARCHAR(100) NOT NULL,
    "description" TEXT NOT NULL,
    "meeting_request_letter" VARCHAR(255) NOT NULL,
    "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_requests_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "issues" (
    "id" SERIAL NOT NULL,
    "title" VARCHAR(100) NOT NULL,
    "description" TEXT NOT NULL,
    "recommendation" TEXT NOT NULL,
    "attachment" VARCHAR(255) NOT NULL,
    "issue_status_id" INTEGER NOT NULL,
    "issue_category_id" INTEGER NOT NULL,
    "meeting_request_id" INTEGER NOT NULL,
    "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 "issues_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "government_agency_meeting_request" (
    "stakeholder_id" INTEGER NOT NULL,
    "meeting_request_id" INTEGER NOT NULL,

    CONSTRAINT "government_agency_meeting_request_pkey" PRIMARY KEY ("stakeholder_id", "meeting_request_id")
);

-- CreateTable
CREATE TABLE "government_agency_issue" (
    "stakeholder_id" INTEGER NOT NULL,
    "issue_id" INTEGER NOT NULL,

    CONSTRAINT "government_agency_issue_pkey" PRIMARY KEY ("stakeholder_id", "issue_id")
);

-- CreateIndex
CREATE INDEX "issue_statuses_user_id_idx" ON "issue_statuses"("user_id");

-- CreateIndex
CREATE INDEX "issue_statuses_deleted_at_idx" ON "issue_statuses"("deleted_at");

-- CreateIndex
CREATE INDEX "issue_categories_user_id_idx" ON "issue_categories"("user_id");

-- CreateIndex
CREATE INDEX "issue_categories_deleted_at_idx" ON "issue_categories"("deleted_at");

-- CreateIndex
CREATE INDEX "meeting_requests_user_id_idx" ON "meeting_requests"("user_id");

-- CreateIndex
CREATE INDEX "meeting_requests_deleted_at_idx" ON "meeting_requests"("deleted_at");

-- CreateIndex
CREATE INDEX "issues_issue_status_id_idx" ON "issues"("issue_status_id");

-- CreateIndex
CREATE INDEX "issues_issue_category_id_idx" ON "issues"("issue_category_id");

-- CreateIndex
CREATE INDEX "issues_meeting_request_id_idx" ON "issues"("meeting_request_id");

-- CreateIndex
CREATE INDEX "issues_user_id_idx" ON "issues"("user_id");

-- CreateIndex
CREATE INDEX "issues_deleted_at_idx" ON "issues"("deleted_at");

-- CreateIndex
CREATE INDEX "government_agency_meeting_request_stakeholder_id_idx" ON "government_agency_meeting_request"("stakeholder_id");

-- CreateIndex
CREATE INDEX "government_agency_meeting_request_meeting_request_id_idx" ON "government_agency_meeting_request"("meeting_request_id");

-- CreateIndex
CREATE INDEX "government_agency_issue_stakeholder_id_idx" ON "government_agency_issue"("stakeholder_id");

-- CreateIndex
CREATE INDEX "government_agency_issue_issue_id_idx" ON "government_agency_issue"("issue_id");

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

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

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

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

-- AddForeignKey
ALTER TABLE "issues" ADD CONSTRAINT "issues_issue_category_id_fkey" FOREIGN KEY ("issue_category_id") REFERENCES "issue_categories"("id") ON DELETE CASCADE ON UPDATE CASCADE;

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

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

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

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

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

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