drop table if exists University;
drop table if exists Teacher;
drop table if exists Subject;
drop table if exists _Group;
drop table if exists Student;
drop table if exists TeacherSubject;
drop table if exists LR;
drop table if exists mark_LR;
drop table if exists all_subject_group;

create table University (
	id int primary key,
	name text,
	city text
);

create table Teacher (
	id int primary key,
	login text,
	name text,
	cathedral text,
	rank text,
	password text,
	is_admin boolean,
	id_university int references University (id)
);

create table Subject (
	id int primary key,
	name text
);

create table _Group (
	id int primary key,
	number int
);

create table Student(
	id int primary key,
	login text,
	name text,
	id_group int references _Group (id),
	id_university int references University (id),
	password text
);

create table TeacherSubject(
	id int primary key,
	id_teacher int references Teacher (id),
	name text
);

create table LR(
	id int primary key,
	id_subject int references Subject (id),
	name text
);

create table mark_LR(
	id int primary key,
	mark int,
	date timestamp,
	id_LR int references LR (id),
	id_student int references Student (id)
);

create table all_subject_group(
	id int primary key,
	id_group int references _Group (id),
	id_subject int references Subject (id)
);

insert into University values (1,'ГУАП','Санкт-Петербург');
insert into University values (2,'Герцена','Санкт-Петербург');
insert into University values (3,'МГУ','Москва');

insert into Teacher values (1,'admin','ADM','','','123',true,1);
insert into Teacher values (2,'admin','ADM','','','123',true,2);