-- DROP SCHEMA public;

CREATE SCHEMA public AUTHORIZATION pg_database_owner;

-- DROP SEQUENCE public.curses_id_curse_seq;

CREATE SEQUENCE public.curses_id_curse_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.curses_id_curse_seq1;

CREATE SEQUENCE public.curses_id_curse_seq1
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.gender_id_gender_seq;

CREATE SEQUENCE public.gender_id_gender_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.gender_id_gender_seq1;

CREATE SEQUENCE public.gender_id_gender_seq1
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.subjects_id_subject_seq;

CREATE SEQUENCE public.subjects_id_subject_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.subjects_id_subject_seq1;

CREATE SEQUENCE public.subjects_id_subject_seq1
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.teacher_curses_id_teacher_curses_seq;

CREATE SEQUENCE public.teacher_curses_id_teacher_curses_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.teacher_curses_id_teacher_curses_seq1;

CREATE SEQUENCE public.teacher_curses_id_teacher_curses_seq1
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.teacher_subjects_id_teacher_subjects_seq;

CREATE SEQUENCE public.teacher_subjects_id_teacher_subjects_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.teacher_subjects_id_teacher_subjects_seq1;

CREATE SEQUENCE public.teacher_subjects_id_teacher_subjects_seq1
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.teachers_id_teacher_seq;

CREATE SEQUENCE public.teachers_id_teacher_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.teachers_id_teacher_seq1;

CREATE SEQUENCE public.teachers_id_teacher_seq1
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.user_curses_id_curse_seq;

CREATE SEQUENCE public.user_curses_id_curse_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.user_curses_list_user_curses_id_seq;

CREATE SEQUENCE public.user_curses_list_user_curses_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.user_data_id_data_seq;

CREATE SEQUENCE public.user_data_id_data_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.users_id_user_seq;

CREATE SEQUENCE public.users_id_user_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;-- public.curses определение

-- Drop table

-- DROP TABLE public.curses;

CREATE TABLE public.curses (
	id_curse int4 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
	curse varchar NOT NULL,
	CONSTRAINT curses_pk PRIMARY KEY (id_curse)
);


-- public.gender определение

-- Drop table

-- DROP TABLE public.gender;

CREATE TABLE public.gender (
	id_gender int4 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
	gender varchar NOT NULL,
	CONSTRAINT gender_pk PRIMARY KEY (id_gender)
);
CREATE UNIQUE INDEX gender_id_gender_idx ON public.gender USING btree (id_gender);


-- public.subjects определение

-- Drop table

-- DROP TABLE public.subjects;

CREATE TABLE public.subjects (
	id_subject int4 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
	subject varchar NOT NULL,
	CONSTRAINT subjects_pk PRIMARY KEY (id_subject)
);


-- public.user_curses определение

-- Drop table

-- DROP TABLE public.user_curses;

CREATE TABLE public.user_curses (
	curse_id int4 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
	curse_name varchar NOT NULL,
	curse_time int4 NOT NULL,
	CONSTRAINT user_curses_pk PRIMARY KEY (curse_id)
);


-- public.users определение

-- Drop table

-- DROP TABLE public.users;

CREATE TABLE public.users (
	id_user int4 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
	"role" varchar NOT NULL,
	CONSTRAINT users_pk PRIMARY KEY (id_user)
);


-- public.teachers определение

-- Drop table

-- DROP TABLE public.teachers;

CREATE TABLE public.teachers (
	id_teacher int4 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
	surname varchar NOT NULL,
	"name" varchar NOT NULL,
	softname varchar NULL,
	id_gender int4 NOT NULL,
	birthday date NOT NULL,
	experience int4 NOT NULL,
	e_mail varchar NOT NULL,
	phone varchar NULL,
	CONSTRAINT teachers_pk PRIMARY KEY (id_teacher),
	CONSTRAINT teachers_fk FOREIGN KEY (id_gender) REFERENCES public.gender(id_gender)
);


-- public.user_curses_list определение

-- Drop table

-- DROP TABLE public.user_curses_list;

CREATE TABLE public.user_curses_list (
	user_curses_id int4 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
	id_teacher int4 NOT NULL,
	curse_id int4 NOT NULL,
	CONSTRAINT user_curses_list_pk PRIMARY KEY (user_curses_id),
	CONSTRAINT user_curses_list_teachers_fk FOREIGN KEY (id_teacher) REFERENCES public.teachers(id_teacher),
	CONSTRAINT user_curses_list_user_curses_fk FOREIGN KEY (curse_id) REFERENCES public.user_curses(curse_id)
);


-- public.user_data определение

-- Drop table

-- DROP TABLE public.user_data;

CREATE TABLE public.user_data (
	id_data int4 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
	login varchar NOT NULL,
	"password" varchar NOT NULL,
	id_teacher int4 NULL,
	id_user int4 NOT NULL,
	CONSTRAINT user_data_pk PRIMARY KEY (id_data),
	CONSTRAINT user_data_teachers_fk FOREIGN KEY (id_teacher) REFERENCES public.teachers(id_teacher),
	CONSTRAINT user_data_users_fk FOREIGN KEY (id_user) REFERENCES public.users(id_user)
);


-- public.teacher_curses определение

-- Drop table

-- DROP TABLE public.teacher_curses;

CREATE TABLE public.teacher_curses (
	id_teacher_curses int4 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
	id_teacher int4 NOT NULL,
	id_curse int4 NOT NULL,
	hours int4 NOT NULL,
	CONSTRAINT teacher_curses_pk PRIMARY KEY (id_teacher_curses),
	CONSTRAINT teacher_curses_fk FOREIGN KEY (id_teacher) REFERENCES public.teachers(id_teacher),
	CONSTRAINT teacher_curses_fk_1 FOREIGN KEY (id_curse) REFERENCES public.curses(id_curse)
);


-- public.teacher_subjects определение

-- Drop table

-- DROP TABLE public.teacher_subjects;

CREATE TABLE public.teacher_subjects (
	id_teacher_subjects int4 NOT NULL GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
	id_teacher int4 NOT NULL,
	id_subject int4 NOT NULL,
	CONSTRAINT teacher_subjects_pk PRIMARY KEY (id_teacher_subjects),
	CONSTRAINT teacher_subjects_fk FOREIGN KEY (id_teacher) REFERENCES public.teachers(id_teacher),
	CONSTRAINT teacher_subjects_fk_1 FOREIGN KEY (id_subject) REFERENCES public.subjects(id_subject)
);