64 lines
4.2 KiB
SQL
64 lines
4.2 KiB
SQL
ALTER TABLE "instructor_platforms"
|
|
ADD COLUMN "control_duration_minutes" INTEGER NOT NULL DEFAULT 30,
|
|
ADD COLUMN "control_warning_minutes" INTEGER NOT NULL DEFAULT 5;
|
|
|
|
CREATE TABLE "platform_control_sessions" (
|
|
"id" UUID NOT NULL DEFAULT gen_random_uuid(),
|
|
"organization_id" UUID,
|
|
"organization_name" TEXT NOT NULL,
|
|
"platform_id" UUID,
|
|
"instructor_platform_id" UUID,
|
|
"holder_user_id" UUID NOT NULL,
|
|
"group_id" UUID,
|
|
"status" TEXT NOT NULL DEFAULT 'ACTIVE',
|
|
"started_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"ends_at" TIMESTAMPTZ(3) NOT NULL,
|
|
"warning_at" TIMESTAMPTZ(3) NOT NULL,
|
|
"warning_sent_at" TIMESTAMPTZ(3),
|
|
"released_at" TIMESTAMPTZ(3),
|
|
"end_reason" TEXT,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "platform_control_sessions_pkey" PRIMARY KEY ("id"),
|
|
CONSTRAINT "platform_control_sessions_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "platform_control_sessions_instructor_platform_id_fkey" FOREIGN KEY ("instructor_platform_id") REFERENCES "instructor_platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "platform_control_sessions_holder_user_id_fkey" FOREIGN KEY ("holder_user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "platform_control_sessions_group_id_fkey" FOREIGN KEY ("group_id") REFERENCES "student_groups"("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
CREATE TABLE "platform_control_queue_entries" (
|
|
"id" UUID NOT NULL DEFAULT gen_random_uuid(),
|
|
"organization_id" UUID,
|
|
"organization_name" TEXT NOT NULL,
|
|
"platform_id" UUID,
|
|
"instructor_platform_id" UUID,
|
|
"user_id" UUID NOT NULL,
|
|
"group_id" UUID,
|
|
"status" TEXT NOT NULL DEFAULT 'WAITING',
|
|
"requested_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"granted_at" TIMESTAMPTZ(3),
|
|
"canceled_at" TIMESTAMPTZ(3),
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "platform_control_queue_entries_pkey" PRIMARY KEY ("id"),
|
|
CONSTRAINT "platform_control_queue_entries_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "platform_control_queue_entries_instructor_platform_id_fkey" FOREIGN KEY ("instructor_platform_id") REFERENCES "instructor_platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "platform_control_queue_entries_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "platform_control_queue_entries_group_id_fkey" FOREIGN KEY ("group_id") REFERENCES "student_groups"("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
|
|
CREATE INDEX "platform_control_sessions_organization_id_idx" ON "platform_control_sessions"("organization_id");
|
|
CREATE INDEX "platform_control_sessions_organization_name_idx" ON "platform_control_sessions"("organization_name");
|
|
CREATE INDEX "platform_control_sessions_platform_id_status_idx" ON "platform_control_sessions"("platform_id", "status");
|
|
CREATE INDEX "platform_control_sessions_instructor_platform_id_status_idx" ON "platform_control_sessions"("instructor_platform_id", "status");
|
|
CREATE INDEX "platform_control_sessions_holder_user_id_status_idx" ON "platform_control_sessions"("holder_user_id", "status");
|
|
CREATE INDEX "platform_control_sessions_ends_at_status_idx" ON "platform_control_sessions"("ends_at", "status");
|
|
|
|
CREATE INDEX "platform_control_queue_entries_organization_id_idx" ON "platform_control_queue_entries"("organization_id");
|
|
CREATE INDEX "platform_control_queue_entries_organization_name_idx" ON "platform_control_queue_entries"("organization_name");
|
|
CREATE INDEX "platform_control_queue_entries_platform_id_status_requested_at_idx" ON "platform_control_queue_entries"("platform_id", "status", "requested_at");
|
|
CREATE INDEX "platform_control_queue_entries_instructor_platform_id_status_requested_at_idx" ON "platform_control_queue_entries"("instructor_platform_id", "status", "requested_at");
|
|
CREATE INDEX "platform_control_queue_entries_user_id_status_idx" ON "platform_control_queue_entries"("user_id", "status");
|