ALTER TABLE public.study_groups ADD COLUMN specid integer DEFAULT null;

ALTER TABLE public.study_groups
  DROP CONSTRAINT study_groups_facultyid_gradeid_groupnum_formid_key;

ALTER TABLE ONLY public.study_groups
  ADD CONSTRAINT study_groups_facultyid_gradeid_groupnum_formid_spec_key UNIQUE (facultyid, gradeid, groupnum, formid, specid);

ALTER TABLE ONLY public.study_groups
  ADD CONSTRAINT study_groups_ibfk_5 FOREIGN KEY (specid) REFERENCES specializations(id);

-- script to fill study_groups.specid from latest record in groups_years
-- after creating empty specid column
update study_groups set specid=(
  select groups_years.specializationid from groups_years
  where groups_years.groupid=study_groups.id
  order by groups_years.year desc
  limit 1
) where specid is null;

DROP FUNCTION IF EXISTS public.creategroup(pgradeid integer, pgroupnum integer, pspecname character varying, pfacultyid integer, pyear integer, pformid integer);
CREATE OR REPLACE FUNCTION public.creategroup(pgradeid integer, pgroupnum integer, pspecname character varying, pfacultyid integer, pyear integer, pformid integer)
  RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE vGroupID INT DEFAULT null;
        vSpecId INT DEFAULT null;
        vGroupYear INT DEFAULT null;
        vIsSpecMatch BOOL DEFAULT null;

BEGIN
  select specializations.id into vSpecId
  from specializations
  where specializations.facultyid=pFacultyID and specializations."name" like pSpecName;
  if (vSpecId is null) then
    INSERT INTO specializations (Name, Abbr, FacultyID)
    VALUES  (pSpecName, NULL, pFacultyID)
    returning id into vSpecId;
  end if;

  -- create group  vGroupID - UNIQUE (pGradeID, pGroupNum, pFacultyID)
  select study_groups.id into vGroupID
  from study_groups
  where study_groups.gradeid=pgradeID
        and study_groups.groupnum= pGroupNum
        and study_groups.facultyid=pFacultyID
        and study_groups.formid=pFormID
        and study_groups.specid=vSpecId;
  if ( vGroupID  is null) then
    INSERT INTO study_groups (GradeID, GroupNum, FacultyID, FormID, SpecID)
    VALUES (pGradeID, pGroupNum, pFacultyID, pFormID, vSpecId)
    returning id into  vGroupID ;
  end if;


  SELECT groups_years.groupid, groups_years.specializationid = vSpecId
  INTO vGroupYear, vIsSpecMatch
  FROM groups_years
  WHERE groups_years.groupid = vGroupID AND groups_years."year" = pYear
  LIMIT 1;

  IF  (vGroupYear is null) THEN
    INSERT INTO groups_years (GroupID, Year, SpecializationID)
    VALUES (vGroupID, pYear, vSpecId);
  ELSEIF NOT vIsSpecMatch THEN
    RETURN -1;
  END IF;

  RETURN vGroupID;
  EXCEPTION
  when others then
    RETURN -1;
END
$function$;