-- CreateTable
CREATE TABLE "events" (
    "id" SERIAL NOT NULL,
    "name" TEXT NOT NULL,
    "start_date" DATE NOT NULL,
    "end_date" DATE NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "rooms" (
    "id" SERIAL NOT NULL,
    "code" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "speakers" (
    "id" SERIAL NOT NULL,
    "name" TEXT NOT NULL,
    "institution" TEXT,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "event_rooms" (
    "id" SERIAL NOT NULL,
    "event_id" INTEGER NOT NULL,
    "room_id" INTEGER NOT NULL,
    "category" TEXT,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "sessions" (
    "id" SERIAL NOT NULL,
    "event_room_id" INTEGER NOT NULL,
    "title" TEXT NOT NULL,
    "starts_at" TIMESTAMPTZ(3) NOT NULL,
    "ends_at" TIMESTAMPTZ(3) NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "topics" (
    "id" SERIAL NOT NULL,
    "session_id" INTEGER NOT NULL,
    "title" TEXT NOT NULL,
    "position" INTEGER NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "topic_speakers" (
    "topic_id" INTEGER NOT NULL,
    "speaker_id" INTEGER NOT NULL,
    "position" INTEGER NOT NULL,

    CONSTRAINT "topic_speakers_pkey" PRIMARY KEY ("topic_id","speaker_id")
);

-- CreateIndex
CREATE UNIQUE INDEX "rooms_code_key" ON "rooms"("code");

-- CreateIndex
CREATE INDEX "event_rooms_room_id_idx" ON "event_rooms"("room_id");

-- CreateIndex
CREATE UNIQUE INDEX "event_rooms_event_id_room_id_key" ON "event_rooms"("event_id", "room_id");

-- CreateIndex
CREATE INDEX "sessions_event_room_id_starts_at_idx" ON "sessions"("event_room_id", "starts_at");

-- CreateIndex
CREATE UNIQUE INDEX "topics_session_id_position_key" ON "topics"("session_id", "position");

-- CreateIndex
CREATE INDEX "topic_speakers_speaker_id_idx" ON "topic_speakers"("speaker_id");

-- CreateIndex
CREATE UNIQUE INDEX "topic_speakers_topic_id_position_key" ON "topic_speakers"("topic_id", "position");

-- AddForeignKey
ALTER TABLE "event_rooms" ADD CONSTRAINT "event_rooms_event_id_fkey" FOREIGN KEY ("event_id") REFERENCES "events"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "event_rooms" ADD CONSTRAINT "event_rooms_room_id_fkey" FOREIGN KEY ("room_id") REFERENCES "rooms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_event_room_id_fkey" FOREIGN KEY ("event_room_id") REFERENCES "event_rooms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "topics" ADD CONSTRAINT "topics_session_id_fkey" FOREIGN KEY ("session_id") REFERENCES "sessions"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "topic_speakers" ADD CONSTRAINT "topic_speakers_topic_id_fkey" FOREIGN KEY ("topic_id") REFERENCES "topics"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "topic_speakers" ADD CONSTRAINT "topic_speakers_speaker_id_fkey" FOREIGN KEY ("speaker_id") REFERENCES "speakers"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- Prisma does not model CHECK constraints; preserve these in future migrations.
ALTER TABLE "events" ADD CONSTRAINT "events_date_range_check" CHECK ("end_date" >= "start_date");
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_time_range_check" CHECK ("ends_at" > "starts_at");
ALTER TABLE "topics" ADD CONSTRAINT "topics_position_check" CHECK ("position" > 0);
ALTER TABLE "topic_speakers" ADD CONSTRAINT "topic_speakers_position_check" CHECK ("position" > 0);