CREATE SEQUENCE seq_subgroups
  START WITH 1
  INCREMENT BY 1
  NO MINVALUE
  NO MAXVALUE
  CACHE 1;

-- DROP SEQUENCE public.seq_subgroups;
select setval('seq_subgroups', 1, true);

create table subgroups (
  id integer default nextval('seq_subgroups'::regclass) NOT NULL,
  title character varying(20) NOT NULL,
  teacher_id integer NOT NULL,
  discipline_id integer NOT NULL
);

ALTER TABLE ONLY subgroups
  ADD CONSTRAINT subgroups_pkey PRIMARY KEY (id);

ALTER TABLE ONLY subgroups
  ADD CONSTRAINT subgroups_ibfk_1 FOREIGN KEY (discipline_id) REFERENCES disciplines(id);

ALTER TABLE ONLY subgroups
  ADD CONSTRAINT subgroups_ibfk_2 FOREIGN KEY (teacher_id) REFERENCES teachers(id);

CREATE SEQUENCE seq_students_subgroups
  START WITH 1
  INCREMENT BY 1
  NO MINVALUE
  NO MAXVALUE
  CACHE 1;

create table students_subgroups (
  id integer default nextval('seq_students_subgroups'::regclass) NOT NULL,
  recordbook_id integer NOT NULL,
  subgroup_id integer NOT NULL
);

ALTER TABLE ONLY students_subgroups
  ADD CONSTRAINT students_subgroups_pkey PRIMARY KEY (id);

ALTER TABLE ONLY students_subgroups
  ADD CONSTRAINT students_subgroups_ibfk_1 FOREIGN KEY (recordbook_id) REFERENCES record_books(id);

ALTER TABLE ONLY students_subgroups
  ADD CONSTRAINT students_subgroups_ibfk_2 FOREIGN KEY (subgroup_id) REFERENCES subgroups(id);



CREATE SEQUENCE seq_subgroups_teachers
  START WITH 1
  INCREMENT BY 1
  NO MINVALUE
  NO MAXVALUE
  CACHE 1;

create table subgroups_teachers (
  id integer default nextval('seq_subgroups_teachers'::regclass) NOT NULL,
  subgroup_id integer NOT NULL,
  teacher_id integer NOT NULL
);

ALTER TABLE ONLY subgroups_teachers
  ADD CONSTRAINT subgroups_teachers_pkey PRIMARY KEY (id);

ALTER TABLE ONLY subgroups_teachers
  ADD CONSTRAINT subgroups_teachers_ibfk_1 FOREIGN KEY (subgroup_id) REFERENCES subgroups(id);

ALTER TABLE ONLY subgroups_teachers
  ADD CONSTRAINT subgroups_teachers_ibfk_2 FOREIGN KEY (teacher_id) REFERENCES teachers(id);

CREATE OR REPLACE FUNCTION public.CreateSubgroup(ptitle character varying, pteacherid integer, pdisciplineid integer)
  RETURNS integer
LANGUAGE plpgsql
AS $function$
declare vID int default -1;
begin
  INSERT INTO subgroups (title, teacher_id, discipline_id)
  VALUES (ptitle, pteacherid, pdisciplineid) returning id into vID;
  RETURN vID;
end
$function$;

CREATE OR REPLACE FUNCTION public.subgroup_delete(psubgroupid integer)
  RETURNS integer
LANGUAGE plpgsql
AS $function$
begin
  delete from subgroups
  where subgroups.id = psubgroupid;
  RETURN 0;
end
$function$;

CREATE OR REPLACE FUNCTION public.Subgroup_GetInfo(psubgroupid integer)
  RETURNS TABLE("Title" character varying, "TeacherID" integer, "DisciplineID" integer)
LANGUAGE sql
AS $function$
SELECT subgroups.title AS "Title",
       subgroups.teacher_id AS "TeacherID",
       subgroups.discipline_id AS "DisciplineID"
FROM subgroups
WHERE subgroups.id = psubgroupid;
$function$;

CREATE OR REPLACE FUNCTION public.Discipline_GetSubgroups(pdisciplineid integer)
  RETURNS TABLE("ID" integer, "Title" character varying, "TeacherID" integer)
LANGUAGE sql
AS $function$
SELECT subgroups.id AS "ID",
       subgroups.title AS "Title",
       subgroups.teacher_id AS "TeacherID",
       subgroups.discipline_id AS "DisciplineID"
FROM subgroups
WHERE subgroups.discipline_id = pdisciplineid;
$function$;

CREATE OR REPLACE FUNCTION public.Subgroup_BindStudent(pstudentid integer, psubgroupid integer)
  RETURNS integer
LANGUAGE plpgsql
AS $function$
declare vID int default -1;
begin
  insert into students_subgroups(recordbook_id, subgroup_id)
  values(pstudentid, psubgroupid)  returning id into vID;
  RETURN vID;
end
$function$;

CREATE OR REPLACE FUNCTION public.Subgroup_UnbindStudent(pstudentid integer, psubgroupid integer)
  RETURNS integer
LANGUAGE plpgsql
AS $function$
declare vID int default -1;
begin
  delete from students_subgroups
  where students_subgroups.recordbook_id = pstudentid and students_subgroups.subgroup_id = psubgroupid;
  RETURN 0;
end
$function$;

CREATE OR REPLACE FUNCTION public.Subgroup_GetStudents(psubgroupid integer)
  RETURNS TABLE("ID" integer)
LANGUAGE sql
AS $function$

select students_subgroups.recordbook_id from students_subgroups
where students_subgroups.subgroup_id = psubgroupid;

$function$;


CREATE OR REPLACE FUNCTION public.Subgroup_BindTeacher(pteacherid integer, psubgroupid integer)
  RETURNS integer
LANGUAGE plpgsql
AS $function$
declare vID int default -1;
begin
  insert into subgroups_teachers(teacher_id, subgroup_id)
  values(pteacherid, psubgroupid)  returning id into vID;
  RETURN vID;
end
$function$;