remedify-assessment-be/create table assessment microservice.sql

CREATE TABLE "test" (
    "id" BIGSERIAL PRIMARY KEY,
    "userId" BIGINT,
    "title" TEXT,
    "description" TEXT,
    "textbookId" BIGINT,
    "validFrom" DATE,
    "validTill" DATE,
    "createdAt" DATE,
    "updatedAt" DATE,
    "createdBy" TEXT,
    "modifiedBy" TEXT,
    "deletedAt" DATE,
    "version" NUMERIC,
    "status" TEXT
);
CREATE TABLE "test_detail" (
    "id" BIGSERIAL PRIMARY KEY,
    "testId" BIGINT,
    "questionType" TEXT,
    "hardness" NUMERIC,
    "count" NUMERIC,
    "validFrom" DATE,
    "validTill" DATE,
    "createdAt" DATE,
    "updatedAt" DATE,
    "createdBy" TEXT,
    "modifiedBy" TEXT,
    "deletedAt" DATE,
    "version" NUMERIC,
    "status" TEXT
);
CREATE TABLE "question" (
    "id" BIGSERIAL PRIMARY KEY,
    "content" TEXT,
    "questionType" TEXT,
    "hardness" NUMERIC,
    "validFrom" DATE,
    "validTill" DATE,
    "createdAt" DATE,
    "updatedAt" DATE,
    "createdBy" TEXT,
    "modifiedBy" TEXT,
    "deletedAt" DATE,
    "version" NUMERIC,
    "status" TEXT
);
CREATE TABLE "test_question_rel" (
    "id" BIGSERIAL PRIMARY KEY,
    "testId" BIGINT,
    "questionId" BIGINT,
    "validFrom" DATE,
    "validTill" DATE,
    "createdAt" DATE,
    "updatedAt" DATE,
    "createdBy" TEXT,
    "modifiedBy" TEXT,
    "deletedAt" DATE,
    "version" NUMERIC,
    "status" TEXT
);
CREATE TABLE "test_assign" (
    "id" BIGSERIAL PRIMARY KEY,
    "testId" BIGINT,
    "userId" BIGINT,
    "assignedAt" DATE,
    "startTime" DATE,
    "endTime" DATE,
    "validFrom" DATE,
    "validTill" DATE,
    "createdAt" DATE,
    "updatedAt" DATE,
    "createdBy" TEXT,
    "modifiedBy" TEXT,
    "deletedAt" DATE,
    "version" NUMERIC,
    "status" TEXT
);
CREATE TABLE "user_answers" (
    "id" BIGSERIAL PRIMARY KEY,
    "testId" BIGINT,
    "userId" BIGINT,
    "questionId" BIGINT,
    "answers" TEXT,
    "validFrom" DATE,
    "validTill" DATE,
    "createdAt" DATE,
    "updatedAt" DATE,
    "createdBy" TEXT,
    "modifiedBy" TEXT,
    "deletedAt" DATE,
    "version" NUMERIC,
    "status" TEXT
);
CREATE TABLE "results" (
    "id" BIGSERIAL PRIMARY KEY,
    "testId" BIGINT,
    "userId" BIGINT,
    "score" NUMERIC,
    "passing" BOOLEAN,
    "description" TEXT,
    "validFrom" DATE,
    "validTill" DATE,
    "createdAt" DATE,
    "updatedAt" DATE,
    "createdBy" TEXT,
    "modifiedBy" TEXT,
    "deletedAt" DATE,
    "version" NUMERIC,
    "status" TEXT
);

CREATE TABLE "feedback" (
    "id" BIGSERIAL PRIMARY KEY,
    "testId" BIGINT,
    "userId" BIGINT,
    "feedbackText" TEXT,
    "rating" NUMERIC,
    "validFrom" DATE,
    "validTill" DATE,
    "createdAt" DATE,
    "updatedAt" DATE,
    "createdBy" TEXT,
    "modifiedBy" TEXT,
    "deletedAt" DATE,
    "version" NUMERIC,
    "status" TEXT
);
CREATE TABLE "question_tag" (
    "id" BIGSERIAL PRIMARY KEY,
    "questionId" BIGINT,
    "tagName" TEXT,
    "validFrom" DATE,
    "validTill" DATE,
    "createdAt" DATE,
    "updatedAt" DATE,
    "createdBy" TEXT,
    "modifiedBy" TEXT,
    "deletedAt" DATE,
    "version" NUMERIC,
    "status" TEXT
);

CREATE TABLE "question_additional_details" (
    "id" BIGSERIAL PRIMARY KEY,
    "questionId" BIGINT,
    "addlDataType" TEXT,
    "addlDataName" TEXT,
    "validFrom" DATE,
    "validTill" DATE,
    "createdAt" DATE,
    "updatedAt" DATE,
    "createdBy" TEXT,
    "modifiedBy" TEXT,
    "deletedAt" DATE,
    "version" NUMERIC,
    "status" TEXT
);
CREATE TABLE "test_tag" (
    "id" BIGSERIAL PRIMARY KEY,
    "testId" BIGINT,
    "tagName" TEXT,
    "validFrom" DATE,
    "validTill" DATE,
    "createdAt" DATE,
    "updatedAt" DATE,
    "createdBy" TEXT,
    "modifiedBy" TEXT,
    "deletedAt" DATE,
    "version" NUMERIC,
    "status" TEXT
);
CREATE TABLE "test_additional_details" (
    "id" BIGSERIAL PRIMARY KEY,
    "testId" BIGINT,
    "addlDataType" TEXT,
    "addlDataName" TEXT,
    "validFrom" DATE,
    "validTill" DATE,
    "createdAt" DATE,
    "updatedAt" DATE,
    "createdBy" TEXT,
    "modifiedBy" TEXT,
    "deletedAt" DATE,
    "version" NUMERIC,
    "status" TEXT
);
CREATE TABLE "ranking" (
    "id" BIGSERIAL PRIMARY KEY,
    "bookId" BIGINT,
    "testId" BIGINT,
    "userId" BIGINT,
    "rank" NUMERIC,
    "validFrom" DATE,
    "validTill" DATE,
    "createdAt" DATE,
    "updatedAt" DATE,
    "createdBy" TEXT,
    "modifiedBy" TEXT,
    "deletedAt" DATE,
    "version" NUMERIC,
    "status" TEXT
);