insert into base.versions(id,version,updated_at,description)
values(next_id('base.versions'),'0.34.3',now(),'增加论文抽检');

create table base.holidays (id bigint not null, project_id integer not null, start_on date not null, switch_to date, updated_at timestamptz default current_timestamp not null);
create table base.mentors_projects (mentor_id bigint not null, project_id integer not null);
create table cfg.std_register_sessions (id bigint not null, begin_at timestamptz not null, end_at timestamptz not null, grades varchar(255) not null, level_id integer not null, project_id integer not null, semester_id integer not null);

create table degree.thesis_checks (id bigint not null, advisor varchar(255), cert_major_name varchar(255) not null, defense_doc_id bigint, degree_major_code varchar(255) not null, degree_major_name varchar(255) not null, depart_name varchar(255), depart_no varchar(255), dual_degree boolean, edu_type varchar(255), enroll_on date not null, first_season boolean default false not null, graduate_on date not null, joint_degree boolean, joint_org_code varchar(255), keywords varchar(255), language_id integer, major_minor_degree boolean, minor_degree boolean, paper_doc_id bigint, proposal_doc_id bigint, research_field varchar(255), season_id bigint not null, second_degree boolean, thesis_type varchar(255), title varchar(255) not null, updated_at timestamptz default current_timestamp not null, writer_id bigint not null, writer_name varchar(255) not null);
create table degree.thesis_docs (id bigint not null, file_ext varchar(100) not null, file_path varchar(300) not null, stage integer not null, updated_at timestamptz default current_timestamp not null, writer_id bigint not null);
create table std.foreigners (id bigint not null, csc_no varchar(20), passport_expired date, passport_no varchar(20), residence_expired date, residence_no varchar(20), std_id bigint not null, visa_expired date, visa_no varchar(20));
alter table base.squads alter campus_id drop not null;
alter table cfg.edu_cert_signup_configs add column max_subject integer default 0;
alter table cfg.edu_cert_signup_configs alter max_subject set not null;
alter table degree.thesis_papers add column language_id integer;
alter table degree.thesis_papers add column research_field varchar(255);
alter table edu.cert_exempt_applies alter certificate set not null;
alter table edu.cert_signups add column exam_room varchar(100);
alter table edu.cert_signups add column seat_no integer default 0;
alter table edu.cert_signups alter seat_no set not null;
alter table edu.cert_signups alter exam_no type varchar(100);
alter table edu.certificate_grades alter certificate set not null;
alter table edu.course_grades add column provider varchar(80);
alter table edu.execution_plans drop campus_id cascade;
alter table edu.execution_plans drop std_type_id cascade;
alter table edu.programs drop campus_id cascade;
alter table base.holidays add constraint pk_o7r9qruakt8wxjndgnml60u7r primary key (id);
alter table base.mentors_projects add constraint pk_p14hk70e30r9eyqy9ol7jn5xm primary key (mentor_id,project_id);
alter table cfg.std_register_sessions add constraint pk_ofhf9115x2ra5xlq0vxmx13t5 primary key (id);
alter table cfg.std_std_alter_configs add constraint pk_nyp7vi8oa6vdk3x8sx15fkdrs primary key (id);
alter table degree.thesis_checks add constraint pk_h3wix678400peq8mufhpn76u9 primary key (id);
alter table degree.thesis_docs add constraint pk_tjx4cxymbuw05ywcmv3mm73cr primary key (id);
alter table std.foreigners add constraint pk_tr2lwiprket01b5xwsnoci4qd primary key (id);
alter table base.holidays add constraint fk_gm32jevhgjqqdwyyh5y45g3ww foreign key (project_id) references base.projects (id);
alter table base.mentors_projects add constraint fk_435ph07t4g90f2r8hxiyk3mqp foreign key (mentor_id) references base.mentors (id);
alter table base.mentors_projects add constraint fk_28tn4tnjdjf7a8f3bk4guohno foreign key (project_id) references base.projects (id);
alter table cfg.std_register_sessions add constraint fk_hvxyk0duy5yf4cac9vrqtvx8r foreign key (level_id) references code.education_levels (id);
alter table cfg.std_register_sessions add constraint fk_h28tdmfwfv1m9d8qakqadowvc foreign key (project_id) references base.projects (id);
alter table cfg.std_register_sessions add constraint fk_i6h5l4gje0vo5dolmu8trkpf5 foreign key (semester_id) references base.semesters (id);
alter table cfg.std_std_alter_configs add constraint fk_cl3rmb51sy4dh1g5bpeddmvdd foreign key (alter_type_id) references code.std_alter_types (id);
alter table cfg.std_std_alter_configs add constraint fk_977dt9mfmw7rb6v13rhq7rc92 foreign key (project_id) references base.projects (id);
alter table cfg.std_std_alter_configs add constraint fk_5dk49lm7n5hboxafbtych954q foreign key (status_id) references code.student_statuses (id);
alter table degree.thesis_checks add constraint fk_n8mhag3ki0l5e61bfrv0xdmyi foreign key (language_id) references code.languages (id);
alter table degree.thesis_checks add constraint fk_6emgfig7bqj088xanwrb5k735 foreign key (season_id) references base.graduate_seasons (id);
alter table degree.thesis_checks add constraint fk_qovasc4mkuhgo2smr4yg44iob foreign key (proposal_doc_id) references degree.thesis_docs (id);
alter table degree.thesis_checks add constraint fk_rm090hmlrmtac2o3clrn6q5ud foreign key (defense_doc_id) references degree.thesis_docs (id);
alter table degree.thesis_checks add constraint fk_qrfjf2f7wb8irycu5c7cjf70l foreign key (paper_doc_id) references degree.thesis_papers (id);
alter table degree.thesis_checks add constraint fk_7r7hal30aly581v98tjr77q4h foreign key (writer_id) references degree.writers (id);
alter table degree.thesis_docs add constraint fk_ahcxfk3p5bedhayllqv34g946 foreign key (writer_id) references degree.writers (id);
alter table degree.thesis_papers add constraint fk_1hsyfo8bqkcf8195rsi41d4jq foreign key (language_id) references code.languages (id);
alter table edu.execution_plans drop constraint if exists fk_61jturqr8eof1nel9uu88mjow cascade;
alter table edu.execution_plans drop constraint if exists fk_d2x58dtqujadn1gvb62d333rj cascade;
alter table edu.programs drop constraint if exists fk_fggj5qrnsyynms21jedryve85 cascade;
alter table std.foreigners add constraint fk_hv593ojmt7ametqoymsbqr3ab foreign key (std_id) references base.students (id);
alter table degree.thesis_checks add constraint uk_ogbi6lgf5f4fvxmghph3h9irs unique (season_id,writer_id);
alter table degree.thesis_docs add constraint uk_9yxnw8wag8f7k4aexkwmrjjnq unique (writer_id,stage);
alter table edu.cert_signups add constraint uk_ox2hp3da9vvpwtuirtsrgtrdt unique (std_id,semester_id,subject_id);
alter table edu.certificate_grades add constraint uk_s5wek526ejranitx9q1nx2uxb unique (std_id,subject_id,acquired_on);
alter table edu.course_audit_results add constraint uk_f9jth1bg1w3pq7io6y2wtmw7a unique (group_result_id,course_id);
create index idx_ppbhk85s88rolvwv4tm8d2kmv on base.mentors_projects (mentor_id);
comment on table base.holidays is '假期@edu';
comment on column base.holidays.id is '非业务主键:datetime';
comment on column base.holidays.project_id is '项目ID';
comment on column base.holidays.start_on is '起始日期';
comment on column base.holidays.switch_to is '排课调整到';
comment on column base.holidays.updated_at is '更新时间';
comment on table base.mentors_projects is '项目列表@std';
comment on column base.mentors_projects.mentor_id is '辅导员ID';
comment on column base.mentors_projects.project_id is '项目ID';
comment on table cfg.std_register_sessions is '注册批次@register.config';
comment on column cfg.std_register_sessions.id is '非业务主键:datetime';
comment on column cfg.std_register_sessions.begin_at is '开始时间';
comment on column cfg.std_register_sessions.end_at is '结束时间';
comment on column cfg.std_register_sessions.grades is '注册年级';
comment on column cfg.std_register_sessions.level_id is '培养层次ID';
comment on column cfg.std_register_sessions.project_id is '项目ID';
comment on column cfg.std_register_sessions.semester_id is '学年学期ID';
comment on table cfg.std_std_alter_configs is '学籍异动配置@alter.config';
comment on column cfg.std_std_alter_configs.id is '非业务主键:datetime';
comment on column cfg.std_std_alter_configs.alter_end_on is '异动结束日期';
comment on column cfg.std_std_alter_configs.alter_graduate_on is '异动毕业日期';
comment on column cfg.std_std_alter_configs.alter_type_id is '学籍异动类别ID';
comment on column cfg.std_std_alter_configs.attributes is '异动影响属性';
comment on column cfg.std_std_alter_configs.inschool is '异动后是否在校';
comment on column cfg.std_std_alter_configs.project_id is '项目ID';
comment on column cfg.std_std_alter_configs.status_id is '学生学籍状态ID';
comment on table degree.thesis_checks is '论文抽检信息@thesis';
comment on column degree.thesis_checks.id is '非业务主键:datetime';
comment on column degree.thesis_checks.advisor is '指导老师';
comment on column degree.thesis_checks.cert_major_name is '证书专业名称';
comment on column degree.thesis_checks.defense_doc_id is '答辩评分表ID';
comment on column degree.thesis_checks.degree_major_code is '学位专业代码';
comment on column degree.thesis_checks.degree_major_name is '学位专业名称';
comment on column degree.thesis_checks.depart_name is '院系名称';
comment on column degree.thesis_checks.depart_no is '院系代码';
comment on column degree.thesis_checks.dual_degree is '是否双学位';
comment on column degree.thesis_checks.edu_type is '学位类型';
comment on column degree.thesis_checks.enroll_on is '入学年月';
comment on column degree.thesis_checks.first_season is '是否第一届毕业生';
comment on column degree.thesis_checks.graduate_on is '毕业年月';
comment on column degree.thesis_checks.joint_degree is '是否联合学位';
comment on column degree.thesis_checks.joint_org_code is '联合培养单位代码';
comment on column degree.thesis_checks.keywords is '论文关键词';
comment on column degree.thesis_checks.language_id is '撰写语种ID';
comment on column degree.thesis_checks.major_minor_degree is '是否主辅修学位';
comment on column degree.thesis_checks.minor_degree is '是否辅修学位';
comment on column degree.thesis_checks.paper_doc_id is '论文ID';
comment on column degree.thesis_checks.proposal_doc_id is '开题报告ID';
comment on column degree.thesis_checks.research_field is '研究领域';
comment on column degree.thesis_checks.season_id is '毕业季ID';
comment on column degree.thesis_checks.second_degree is '是否第二学位';
comment on column degree.thesis_checks.thesis_type is '毕业论文类型';
comment on column degree.thesis_checks.title is '题目';
comment on column degree.thesis_checks.updated_at is '更新时间';
comment on column degree.thesis_checks.writer_id is '学生ID';
comment on column degree.thesis_checks.writer_name is '学生姓名';
comment on table degree.thesis_docs is '学位论文相关文档@thesis';
comment on column degree.thesis_docs.id is '非业务主键:datetime';
comment on column degree.thesis_docs.file_ext is '附件类型';
comment on column degree.thesis_docs.file_path is '附件路径';
comment on column degree.thesis_docs.stage is '所处阶段';
comment on column degree.thesis_docs.updated_at is '更新时间';
comment on column degree.thesis_docs.writer_id is '写作学生ID';
comment on table std.foreigners is '留学生信息@info';
comment on column std.foreigners.id is '非业务主键:datetime';
comment on column std.foreigners.csc_no is 'CSC编号';
comment on column std.foreigners.passport_expired is '护照到期时间';
comment on column std.foreigners.passport_no is '护照编号';
comment on column std.foreigners.residence_expired is '居住许可证到期时间';
comment on column std.foreigners.residence_no is '居住许可证编号';
comment on column std.foreigners.std_id is '学籍信息实现ID';
comment on column std.foreigners.visa_expired is '签证到期时间';
comment on column std.foreigners.visa_no is '签证编号';
