======================================================================================
-- This table will be used to convert numeric statistics into human readable answers
-- that way, a score of 83 till 87 can be converted into a "B" grade for a class, or a 
-- score of 347 can say that somebody is at high risk for cancer or similar.
--
-- if an entry is an overarching_translation_p it is a comment that summarizes the
-- entire survey, not just subsections

create table survey_point_translation (
        survey_id                       integer constraint survey_pt_trans_survey_id_fk
                                        references surveys,
        overarching_translation_p       boolean default 'f',
        section_id                      integer constraint survey_pt_trans_sect_id_fk
                                        references survey_sections,
        points_min                      integer,
        points_max                      integer,
        feedback                        text
);

======================================================================================


-- This table holds the feedback on survey objects (either an entire survey, a
-- survey_section, or a survey_question).
-- if there is one possible response (i.e. in a multiple choice test, if somebody
-- gets a question wrong there is a standard response giving justification for the
-- answer) - as defined by the grading_comments_one_response_p field in the table
-- survey_questions. This is bound to the acs_objects table, so you can either comment
-- on a survey, survery_section, or survey_question
--
-- ?? I NEED TO ADD A CONSTRAINT THAT SORT ORDER MUST BE UNIQUE PER OBJECT
--
create table survey_scoring_feedback (
     feedback_id                        integer constraint survey_gc_comment_id_fk
                                        references acs_objects (object_id)
                                        on delete cascade
                                        constraint survey_gc_comment_id_pk
                                        primary key,
     object_id                          integer constraint survey_grad_com_ob_id_fk
                                        references acs_objects,
     feedback_sort_order                integer constraint survey_gc_comment_so_nn
                                        not null,
     feedback_text                      text constraint survey_gc_comment_text_nn
                                        not null
);



======================================================================================

--
-- THESE ARE THE VIEWS I NEED TO CREATE
--
--
--
-- Create view survey_grading_section_totals
--    SUM of "grading_points" from survey_question_reponses per user per question_id where
--           question_id is part of survey_section.
--    (this would a pretty complicated view - I hope it can be done)
--
--
-- Create view survey_grading_total_score
--    SUM of "grading_points" for all subsections of a survey info will be pulled off of the
--     survey_grading_section_subtotals view which is mentioned above.
--
--
--
--
-- create view survey_statistics_by_question
--     select count(*) of entries grouped by question from
--      survey_question_responses, as well as SUM
--        of values as grouped by question
--
-- Create view survey_statistics_by_section
--     select count(*) of entries grouped by section from the view
--       survey_grading_sections_total, as well as the sum of all
--       the values per section
--
-- Create view survey_statistics
--     select count(*) of entries grouped by survey from view
--       survey_grading_total_score, as well as the sum of all the