/*
 Navicat Premium Data Transfer

 Source Server         : local_postgresql
 Source Server Type    : PostgreSQL
 Source Server Version : 150003 (150003)
 Source Host           : localhost:5432
 Source Catalog        : little_busters
 Source Schema         : public

 Target Server Type    : PostgreSQL
 Target Server Version : 150003 (150003)
 File Encoding         : 65001

 Date: 21/08/2023 18:32:52
*/


-- ----------------------------
-- Table structure for activity
-- ----------------------------
DROP TABLE IF EXISTS "public"."activity";
CREATE TABLE "public"."activity" (
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL DEFAULT 0,
  "name" varchar(64) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
  "term" varchar(11) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '2023-2024-1'::character varying,
  "start_time" timestamp(6) NOT NULL DEFAULT now(),
  "end_time" timestamp(6) NOT NULL DEFAULT now(),
  "create_time" timestamp(6) NOT NULL DEFAULT now(),
  "edit_time" timestamp(6) NOT NULL DEFAULT now(),
  "deleted" bool NOT NULL DEFAULT false
)
;
COMMENT ON COLUMN "public"."activity"."id" IS '选课活动id';
COMMENT ON COLUMN "public"."activity"."name" IS '活动id';
COMMENT ON COLUMN "public"."activity"."term" IS '学期，格式如2023-2024-1';
COMMENT ON COLUMN "public"."activity"."start_time" IS '开始时间';
COMMENT ON COLUMN "public"."activity"."end_time" IS '结束时间';

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS "public"."class";
CREATE TABLE "public"."class" (
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "course" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "start_week" int2 NOT NULL,
  "end_week" int2 NOT NULL,
  "time" int2 NOT NULL,
  "academic_term" varchar(11) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '2023-2024-1'::character varying,
  "place" varchar(255) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
  "ratio" float4 NOT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT now(),
  "edit_time" timestamp(6) NOT NULL DEFAULT now(),
  "deleted" bool NOT NULL DEFAULT false,
  "distribution" jsonb NOT NULL DEFAULT '{}'::jsonb,
  "name" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "week_day" int2 NOT NULL DEFAULT 1
)
;
COMMENT ON COLUMN "public"."class"."id" IS '课堂id';
COMMENT ON COLUMN "public"."class"."course" IS '课程id';
COMMENT ON COLUMN "public"."class"."start_week" IS '开始周';
COMMENT ON COLUMN "public"."class"."end_week" IS '结束周';
COMMENT ON COLUMN "public"."class"."time" IS '节次时间，即第几节课';
COMMENT ON COLUMN "public"."class"."academic_term" IS '学年学期，格式如2023-2024-2';
COMMENT ON COLUMN "public"."class"."place" IS '上课地点';
COMMENT ON COLUMN "public"."class"."ratio" IS '考试成绩占比';
COMMENT ON COLUMN "public"."class"."distribution" IS '平时分成绩分布，usual_score参考依据';
COMMENT ON COLUMN "public"."class"."name" IS '课堂名称';
COMMENT ON COLUMN "public"."class"."week_day" IS '星期几';

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS "public"."course";
CREATE TABLE "public"."course" (
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "course_hours" int2 NOT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT now(),
  "edit_time" timestamp(6) NOT NULL DEFAULT now(),
  "deleted" bool NOT NULL DEFAULT false,
  "spring_term" bool NOT NULL DEFAULT true,
  "target_major" varchar[] COLLATE "pg_catalog"."default" NOT NULL DEFAULT ARRAY[''::text],
  "target_grade" int2[] NOT NULL
)
;
COMMENT ON COLUMN "public"."course"."id" IS '课程id';
COMMENT ON COLUMN "public"."course"."name" IS '课程名称';
COMMENT ON COLUMN "public"."course"."course_hours" IS '课时';
COMMENT ON COLUMN "public"."course"."spring_term" IS '是否春季期课程（false即是秋季期）';
COMMENT ON COLUMN "public"."course"."target_major" IS '上课专业';
COMMENT ON COLUMN "public"."course"."target_grade" IS '上课专业';

-- ----------------------------
-- Table structure for course_selection
-- ----------------------------
DROP TABLE IF EXISTS "public"."course_selection";
CREATE TABLE "public"."course_selection" (
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "student" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "schedule" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT now(),
  "edit_time" timestamp(6) NOT NULL DEFAULT now(),
  "deleted" bool NOT NULL DEFAULT false,
  "activity" varchar(64) COLLATE "pg_catalog"."default" NOT NULL
)
;
COMMENT ON COLUMN "public"."course_selection"."id" IS '选课记录id';
COMMENT ON COLUMN "public"."course_selection"."student" IS '学生id';
COMMENT ON COLUMN "public"."course_selection"."schedule" IS '课堂id';
COMMENT ON COLUMN "public"."course_selection"."activity" IS '选课时候的活动id';

-- ----------------------------
-- Table structure for experiment_item
-- ----------------------------
DROP TABLE IF EXISTS "public"."experiment_item";
CREATE TABLE "public"."experiment_item" (
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "course" int8 NOT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT now(),
  "edit_time" timestamp(6) NOT NULL DEFAULT now(),
  "deleted" bool NOT NULL DEFAULT false
)
;
COMMENT ON COLUMN "public"."experiment_item"."id" IS '实验项目id';
COMMENT ON COLUMN "public"."experiment_item"."name" IS '项目名称';
COMMENT ON COLUMN "public"."experiment_item"."course" IS '课程id';

-- ----------------------------
-- Table structure for experiment_score
-- ----------------------------
DROP TABLE IF EXISTS "public"."experiment_score";
CREATE TABLE "public"."experiment_score" (
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "student" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "experiment" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "score" int2 NOT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT now(),
  "edit_time" timestamp(6) NOT NULL DEFAULT now(),
  "deleted" bool NOT NULL DEFAULT false
)
;
COMMENT ON COLUMN "public"."experiment_score"."id" IS '成绩id';
COMMENT ON COLUMN "public"."experiment_score"."student" IS '学生id';
COMMENT ON COLUMN "public"."experiment_score"."experiment" IS '实验id';
COMMENT ON COLUMN "public"."experiment_score"."score" IS '实验分';

-- ----------------------------
-- Table structure for major
-- ----------------------------
DROP TABLE IF EXISTS "public"."major";
CREATE TABLE "public"."major" (
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT now(),
  "edit_time" timestamp(6) NOT NULL DEFAULT now(),
  "deleted" bool NOT NULL DEFAULT false
)
;
COMMENT ON COLUMN "public"."major"."id" IS '专业id';
COMMENT ON COLUMN "public"."major"."name" IS '专业名称';

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS "public"."score";
CREATE TABLE "public"."score" (
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "student" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "usual_score" float4 NOT NULL,
  "exam_score" float4 NOT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT now(),
  "edit_time" timestamp(6) NOT NULL DEFAULT now(),
  "deleted" bool NOT NULL DEFAULT false,
  "class" varchar(64) COLLATE "pg_catalog"."default" NOT NULL
)
;
COMMENT ON COLUMN "public"."score"."id" IS '成绩id';
COMMENT ON COLUMN "public"."score"."student" IS '学生id';
COMMENT ON COLUMN "public"."score"."usual_score" IS '平时分';
COMMENT ON COLUMN "public"."score"."exam_score" IS '考试分';
COMMENT ON COLUMN "public"."score"."class" IS '课堂id';

-- ----------------------------
-- Table structure for student_info
-- ----------------------------
DROP TABLE IF EXISTS "public"."student_info";
CREATE TABLE "public"."student_info" (
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "student_number" varchar(12) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(16) COLLATE "pg_catalog"."default" NOT NULL,
  "major" varchar(16) COLLATE "pg_catalog"."default" NOT NULL,
  "grade" int2 NOT NULL DEFAULT 2,
  "class" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT now(),
  "edit_time" timestamp(6) NOT NULL DEFAULT now(),
  "deleted" bool NOT NULL DEFAULT false
)
;
COMMENT ON COLUMN "public"."student_info"."id" IS '学生id';
COMMENT ON COLUMN "public"."student_info"."student_number" IS '学号';
COMMENT ON COLUMN "public"."student_info"."name" IS '学生姓名';
COMMENT ON COLUMN "public"."student_info"."major" IS '专业id';
COMMENT ON COLUMN "public"."student_info"."grade" IS '年级，使用大x表示，如大二：2';
COMMENT ON COLUMN "public"."student_info"."class" IS '班级';

-- ----------------------------
-- Table structure for teacher_class
-- ----------------------------
DROP TABLE IF EXISTS "public"."teacher_class";
CREATE TABLE "public"."teacher_class" (
  "teacher" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "class" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT now(),
  "edit_time" timestamp(6) NOT NULL DEFAULT now(),
  "deleted" bool NOT NULL DEFAULT false
)
;
COMMENT ON COLUMN "public"."teacher_class"."teacher" IS '教师id';
COMMENT ON COLUMN "public"."teacher_class"."class" IS '课程班级id';

-- ----------------------------
-- Table structure for teacher_info
-- ----------------------------
DROP TABLE IF EXISTS "public"."teacher_info";
CREATE TABLE "public"."teacher_info" (
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT now(),
  "edit_time" timestamp(6) NOT NULL DEFAULT now(),
  "deleted" bool NOT NULL DEFAULT false
)
;
COMMENT ON COLUMN "public"."teacher_info"."id" IS '教师id';
COMMENT ON COLUMN "public"."teacher_info"."name" IS '教师姓名';

-- ----------------------------
-- Table structure for user_basic
-- ----------------------------
DROP TABLE IF EXISTS "public"."user_basic";
CREATE TABLE "public"."user_basic" (
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "username" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "password" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "type" int2 NOT NULL,
  "create_time" timestamp(6) NOT NULL DEFAULT now(),
  "edit_time" timestamp(6) NOT NULL DEFAULT now(),
  "deleted" bool NOT NULL DEFAULT false
)
;
COMMENT ON COLUMN "public"."user_basic"."id" IS '用户uuid';
COMMENT ON COLUMN "public"."user_basic"."username" IS '用户名，登陆用';
COMMENT ON COLUMN "public"."user_basic"."password" IS '密码';
COMMENT ON COLUMN "public"."user_basic"."type" IS '用户类型，0：学生，1：教师，2：管理员';

-- ----------------------------
-- Function structure for cs_timestamp
-- ----------------------------
DROP FUNCTION IF EXISTS "public"."cs_timestamp"();
CREATE OR REPLACE FUNCTION "public"."cs_timestamp"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
begin
	new.update_time= current_timestame;
	return new;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Indexes structure for table activity
-- ----------------------------
CREATE INDEX "act_idx_end" ON "public"."activity" USING brin (
  "end_time" "pg_catalog"."timestamp_minmax_ops"
);
CREATE INDEX "act_idx_id" ON "public"."activity" USING hash (
  "id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops"
);
CREATE INDEX "act_idx_start" ON "public"."activity" USING brin (
  "start_time" "pg_catalog"."timestamp_minmax_ops"
);
CREATE INDEX "act_idx_term" ON "public"."activity" USING btree (
  "term" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table activity
-- ----------------------------
ALTER TABLE "public"."activity" ADD CONSTRAINT "activity_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Indexes structure for table class
-- ----------------------------
CREATE INDEX "cs_idx_course" ON "public"."class" USING btree (
  "course" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "cs_idx_id" ON "public"."class" USING btree (
  "id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "cs_idx_term" ON "public"."class" USING btree (
  "academic_term" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table class
-- ----------------------------
ALTER TABLE "public"."class" ADD CONSTRAINT "class_pkey" PRIMARY KEY ("id", "course");

-- ----------------------------
-- Indexes structure for table course
-- ----------------------------
CREATE INDEX "cours_idx_major" ON "public"."course" USING gin (
  "target_major" COLLATE "pg_catalog"."default" "pg_catalog"."array_ops"
) WITH (GIN_PENDING_LIST_LIMIT = 64);
CREATE INDEX "course_idx_grade" ON "public"."course" USING gin (
  "target_grade" "pg_catalog"."array_ops"
) WITH (GIN_PENDING_LIST_LIMIT = 64);
CREATE INDEX "course_idx_id" ON "public"."course" USING hash (
  "id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops"
);
COMMENT ON INDEX "public"."course_idx_id" IS 'id索引';

-- ----------------------------
-- Primary Key structure for table course
-- ----------------------------
ALTER TABLE "public"."course" ADD CONSTRAINT "course_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Indexes structure for table course_selection
-- ----------------------------
CREATE INDEX "csel_idex_student" ON "public"."course_selection" USING btree (
  "student" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "csel_idx_activity" ON "public"."course_selection" USING btree (
  "activity" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "csel_idx_course" ON "public"."course_selection" USING btree (
  "schedule" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "csel_idx_id" ON "public"."course_selection" USING btree (
  "id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table course_selection
-- ----------------------------
ALTER TABLE "public"."course_selection" ADD CONSTRAINT "course_selection_pkey" PRIMARY KEY ("id", "student", "schedule");

-- ----------------------------
-- Primary Key structure for table experiment_item
-- ----------------------------
ALTER TABLE "public"."experiment_item" ADD CONSTRAINT "course_experiment_pkey" PRIMARY KEY ("id", "course");

-- ----------------------------
-- Primary Key structure for table experiment_score
-- ----------------------------
ALTER TABLE "public"."experiment_score" ADD CONSTRAINT "experiment_score_pkey" PRIMARY KEY ("id", "student", "experiment");

-- ----------------------------
-- Indexes structure for table major
-- ----------------------------
CREATE INDEX "major_idx_id" ON "public"."major" USING hash (
  "id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops"
);
CREATE INDEX "major_idx_name" ON "public"."major" USING btree (
  "name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table major
-- ----------------------------
ALTER TABLE "public"."major" ADD CONSTRAINT "major_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Indexes structure for table score
-- ----------------------------
CREATE INDEX "score_idx_class" ON "public"."score" USING btree (
  "class" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "score_idx_id" ON "public"."score" USING hash (
  "id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops"
);
CREATE INDEX "score_idx_student" ON "public"."score" USING btree (
  "student" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table score
-- ----------------------------
ALTER TABLE "public"."score" ADD CONSTRAINT "score_pkey" PRIMARY KEY ("id", "student", "class");

-- ----------------------------
-- Indexes structure for table student_info
-- ----------------------------
CREATE INDEX "stu_dix_grade" ON "public"."student_info" USING btree (
  "grade" "pg_catalog"."int2_ops" ASC NULLS LAST
);
CREATE INDEX "stu_idx_id" ON "public"."student_info" USING hash (
  "id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops"
);
CREATE INDEX "stu_idx_major" ON "public"."student_info" USING btree (
  "major" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "stu_idx_num" ON "public"."student_info" USING btree (
  "student_number" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table student_info
-- ----------------------------
ALTER TABLE "public"."student_info" ADD CONSTRAINT "student_info_pkey" PRIMARY KEY ("id", "student_number");

-- ----------------------------
-- Indexes structure for table teacher_class
-- ----------------------------
CREATE INDEX "tc_idx_course" ON "public"."teacher_class" USING btree (
  "class" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "tc_idx_teacher" ON "public"."teacher_class" USING btree (
  "teacher" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table teacher_class
-- ----------------------------
ALTER TABLE "public"."teacher_class" ADD CONSTRAINT "teacher_course_pkey" PRIMARY KEY ("teacher", "class");

-- ----------------------------
-- Indexes structure for table teacher_info
-- ----------------------------
CREATE INDEX "ti_idx_id" ON "public"."teacher_info" USING hash (
  "id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops"
);

-- ----------------------------
-- Primary Key structure for table teacher_info
-- ----------------------------
ALTER TABLE "public"."teacher_info" ADD CONSTRAINT "teacher_info_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Indexes structure for table user_basic
-- ----------------------------
CREATE INDEX "usr_idx_id" ON "public"."user_basic" USING hash (
  "id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops"
);
CREATE INDEX "usr_idx_username" ON "public"."user_basic" USING btree (
  "username" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table user_basic
-- ----------------------------
ALTER TABLE "public"."user_basic" ADD CONSTRAINT "user_pkey" PRIMARY KEY ("id", "username", "password");