CREATE OR REPLACE FUNCTION public.finalreport_getbysemesteryearnum(pyear integer, pnum integer)
  RETURNS TABLE("SemesterYear" integer, "SemesterNum" integer, "PlanExternalID" integer,
                "SubjectExternalID" character varying, "RecordBookExternalID" character varying,
                "SemesterRate" bigint, "ExamRate" integer, "Exam2Rate" integer, "Exam3Rate" integer,
                "ExtraRate" integer, "Extra2Rate" integer)
LANGUAGE plpgsql
AS $function$
BEGIN
  return query
  SELECT *
  FROM view_final_report
  WHERE view_final_report.SemesterYear = pyear and
        view_final_report.SemesterNum = pnum;
END
$function$;

DROP FUNCTION IF EXISTS public.finalreport_getbysemesteryearplan(pyear integer, pnum integer, pplanexternalid integer);
CREATE OR REPLACE FUNCTION public.finalreport_getbysemesteryearplan(pyear integer, pnum integer, pplanexternalid integer)
  RETURNS TABLE("SemesterYear" integer, "SemesterNum" integer, "PlanExternalID" integer,
                "SubjectExternalID" character varying, "RecordBookExternalID" character varying,  "ExamType" exam_credit_grading_credit,
                "SemesterRate" bigint, "ExamRate" integer, "Exam2Rate" integer, "Exam3Rate" integer,
                "ExtraRate" integer, "Extra2Rate" integer)
LANGUAGE plpgsql
AS $function$
BEGIN
  return query
  SELECT *
  FROM view_final_report
  WHERE view_final_report.SemesterYear = pyear and
        view_final_report.SemesterNum = pnum and
        view_final_report.PlanExternalID = pplanexternalid;
END
$function$;

DROP FUNCTION IF EXISTS public.finalreport_getbysemesteryeardiscipline(pyear integer, pnum integer, pdisciplineexternalid character varying);
CREATE OR REPLACE FUNCTION public.finalreport_getbysemesteryeardiscipline(pyear integer, pnum integer, pdisciplineexternalid character varying)
  RETURNS TABLE("SemesterYear" integer, "SemesterNum" integer, "PlanExternalID" integer,
                "SubjectExternalID" character varying, "RecordBookExternalID" character varying, "ExamType" exam_credit_grading_credit,
                "SemesterRate" bigint, "ExamRate" integer, "Exam2Rate" integer, "Exam3Rate" integer,
                "ExtraRate" integer, "Extra2Rate" integer)
LANGUAGE plpgsql
AS $function$
BEGIN
  return query
  SELECT *
  FROM view_final_report
  WHERE view_final_report.SemesterYear = pyear and
        view_final_report.SemesterNum = pnum and
        view_final_report.SubjectExternalID = pdisciplineexternalid;
END
$function$;

drop view if exists public.view_final_report;
CREATE OR REPLACE VIEW public.view_final_report
  AS SELECT DISTINCT semesters.year AS semesteryear,
                     semesters.num AS semesternum,
                     study_plans.externalid AS planexternalid,
                     subjects.externalid AS subjectexternalid,
                     record_books.externalid AS recordbookexternalid,
                     disciplines.examtype as examtype,
                     ( SELECT sum(rating_table.rate) AS sum
                       FROM rating_table
                         JOIN submodules ON rating_table.submoduleid = submodules.id
                         JOIN modules ON submodules.moduleid = modules.id
                       WHERE modules.disciplineid = disciplines.id AND modules.type = 'regular'::regular_exam_bonus_extra AND rating_table.recordbookid = record_books.id
                       GROUP BY modules.disciplineid, rating_table.recordbookid) AS semesterrate,
                     ( SELECT rating_table.rate
                       FROM rating_table
                         JOIN submodules ON rating_table.submoduleid = submodules.id
                         JOIN modules ON submodules.moduleid = modules.id
                       WHERE modules.disciplineid = disciplines.id AND modules.type = 'exam'::regular_exam_bonus_extra AND rating_table.recordbookid = record_books.id
                       LIMIT 1) AS examrate,
                     ( SELECT rating_table.rate
                       FROM rating_table
                         JOIN submodules ON rating_table.submoduleid = submodules.id
                         JOIN modules ON submodules.moduleid = modules.id
                       WHERE modules.disciplineid = disciplines.id AND modules.type = 'exam'::regular_exam_bonus_extra AND rating_table.recordbookid = record_books.id
                       OFFSET 1
                       LIMIT 1) AS exam2rate,
                     ( SELECT rating_table.rate
                       FROM rating_table
                         JOIN submodules ON rating_table.submoduleid = submodules.id
                         JOIN modules ON submodules.moduleid = modules.id
                       WHERE modules.disciplineid = disciplines.id AND modules.type = 'exam'::regular_exam_bonus_extra AND rating_table.recordbookid = record_books.id
                       OFFSET 2
                       LIMIT 1) AS exam3rate,
                     ( SELECT rating_table.rate
                       FROM rating_table
                         JOIN submodules ON rating_table.submoduleid = submodules.id
                         JOIN modules ON submodules.moduleid = modules.id
                       WHERE modules.disciplineid = disciplines.id AND modules.type = 'extra'::regular_exam_bonus_extra AND rating_table.recordbookid = record_books.id
                       LIMIT 1) AS extrarate,
                     ( SELECT rating_table.rate
                       FROM rating_table
                         JOIN submodules ON rating_table.submoduleid = submodules.id
                         JOIN modules ON submodules.moduleid = modules.id
                       WHERE modules.disciplineid = disciplines.id AND modules.type = 'extra'::regular_exam_bonus_extra AND rating_table.recordbookid = record_books.id
                       OFFSET 1
                       LIMIT 1) AS extra2rate
     FROM semesters
       JOIN disciplines ON disciplines.semesterid = semesters.id
       JOIN disciplines_study_plans ON disciplines_study_plans.disciplineid = disciplines.id
       JOIN study_plans ON study_plans.id = disciplines_study_plans.studyplanid
       JOIN subjects ON subjects.id = disciplines.subjectid
       JOIN view_disciplines_recordbooks ON view_disciplines_recordbooks.disciplineid = disciplines.id AND 'attach'::attach_detach = COALESCE(view_disciplines_recordbooks.type, 'attach'::attach_detach)
       JOIN record_books ON record_books.id = view_disciplines_recordbooks.recordbookid;

-- Permissions

ALTER TABLE public.view_final_report OWNER TO postgres;
GRANT ALL ON TABLE public.view_final_report TO postgres;