-- CreateEnum
CREATE TYPE "Status" AS ENUM ('SHOW', 'HIDE');

-- CreateEnum
CREATE TYPE "Role" AS ENUM ('USER', 'ADMIN');

-- CreateTable
CREATE TABLE "kl_user" (
    "id" SERIAL NOT NULL,
    "name" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "status" "Role" NOT NULL DEFAULT 'USER',
    "password" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "modifiedAt" TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE "kl_category_tbl" (
    "id" SERIAL NOT NULL,
    "category_name" TEXT NOT NULL,
    "status" "Status" NOT NULL DEFAULT 'SHOW',
    "tag" TEXT[],
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "modifiedAt" TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE "kl_course_tbl" (
    "id" SERIAL NOT NULL,
    "course_name" TEXT NOT NULL,
    "status" "Status" NOT NULL DEFAULT 'SHOW',
    "tag" TEXT[],
    "category_id" INTEGER NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "modifiedAt" TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE "kl_QNModelset_tbl" (
    "id" SERIAL NOT NULL,
    "qn_title" TEXT NOT NULL,
    "modelset_info" JSONB NOT NULL,
    "qn_set" JSONB NOT NULL,
    "exam_time" TEXT NOT NULL,
    "status" "Status" NOT NULL DEFAULT 'SHOW',
    "tag" TEXT[],
    "createBy" TEXT NOT NULL,
    "course_id" INTEGER NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "modifiedAt" TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE "KL_UserGivenExamSet_tbl" (
    "id" SERIAL NOT NULL,
    "user_id" INTEGER NOT NULL,
    "qn_set_id" INTEGER NOT NULL,
    "qn_ans" JSONB NOT NULL,
    "qn_title" TEXT NOT NULL,
    "obtainedMarks" TEXT NOT NULL,
    "examtimeTaken" TEXT NOT NULL,
    "status" "Status" NOT NULL DEFAULT 'SHOW',
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "modifiedAt" TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE "KL_UserSlectedCourse_tbl" (
    "id" SERIAL NOT NULL,
    "user_id" INTEGER NOT NULL,
    "course_id" INTEGER NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "modifiedAt" TIMESTAMP(3),

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

-- CreateIndex
CREATE UNIQUE INDEX "kl_user_email_key" ON "kl_user"("email");

-- AddForeignKey
ALTER TABLE "kl_course_tbl" ADD CONSTRAINT "kl_course_tbl_category_id_fkey" FOREIGN KEY ("category_id") REFERENCES "kl_category_tbl"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "kl_QNModelset_tbl" ADD CONSTRAINT "kl_QNModelset_tbl_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "kl_course_tbl"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "KL_UserGivenExamSet_tbl" ADD CONSTRAINT "KL_UserGivenExamSet_tbl_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "kl_user"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "KL_UserGivenExamSet_tbl" ADD CONSTRAINT "KL_UserGivenExamSet_tbl_qn_set_id_fkey" FOREIGN KEY ("qn_set_id") REFERENCES "kl_QNModelset_tbl"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "KL_UserSlectedCourse_tbl" ADD CONSTRAINT "KL_UserSlectedCourse_tbl_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "kl_user"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "KL_UserSlectedCourse_tbl" ADD CONSTRAINT "KL_UserSlectedCourse_tbl_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "kl_course_tbl"("id") ON DELETE RESTRICT ON UPDATE CASCADE;