--- ANALYSIS ---

-- A number of course_modules are associated with section = 0.  These are uninteresting to the recovery
-- process.  Even though we don't know the exact cause, they appear to be orphaned modules.
-- Further moodle and mysql have a history of importing 0 where null should be.
SELECT to_timestamp(added), * from mdl_course_modules where section = 0 order by 1 desc;

-- This is a much better query than previously used to determine the section corruption failures (See MDL-37939 for original)

-- Query to show what courses have modules in sections that aren't part of the course (postgresql)
----------
        -- array_agg(distinct x) gives the set of section included in that courses course_module and course_section tables
SELECT cs.course, cm.course, array_agg(distinct cm.section), array_agg(distinct cs.id)
  FROM mdl_course_modules cm
   -- join on the course to find all module_sections and section_sections that are attached to a single course label
   JOIN mdl_course_sections cs ON (cm.course = cs.course)
   -- We ignore orphaned modules from the process
   WHERE cm.section <> 0 
  GROUP BY 1,2
  -- It's okay for sections to be defined without course modules.
  -- It's not okay to have a course module in a section that doesn't belong to this course.
  -- <@ operator is array contains.  Based on above comments, we want to see rows that don't meet the above criteria (NOT).
 HAVING NOT (array_agg(distinct cm.section) <@ array_agg(distinct cs.id));


/*

What we are doing here is taking all of the activities in course_modules and find sections that aren't in the
  same course.
  When we find it's not in the same course, we go hunting for the instance of that module.
  We check the instance of the module has a matching course to what we expect.
  This gives us confidence we are putting the module back in the correct course.
  Why are we confident?
   1. course_modules.course IS mod_X.course
   2. course_sections.course <> course_modules.course

  Now the problem is the section is wrong, where do we put this stuff?
    We know that all courses have a section 0.
    So we find the id of that section so we know where to move the activity to.

    When we move the activity we make it invisible to students and indent it 10.
    These adjustments make it obvious to the lecturers that we have put their activities there.
    Rather than it being existing content.

*/

UPDATE mdl_course_modules SET section = stufftofix.fixedsection, visible = 0, indent = 10
  FROM
  (
  SELECT sectzero.id as fixedsection, cm.id as course_module_id,
        coalesce(assign.course, data.course, feedback.course, glossary.course, lesson.course, lbox.course,
                 scorm.course, m21.course, m22.course, m24.course, m28.course, m30.course, m31.course, m32.course, m33.course,
                 m34.course,
                chat.course, choice.course, forum.course, folder.course, page.course, quiz.course,
                url.course,lectopia.course, turn.course, r.course, l.course, survey.course) as mod_course, 
       cm.module, cm.instance, cm.id, cs.course as section_course, cm.course as course_module_course 
  FROM mdl_course_sections cs JOIN mdl_course_modules cm ON cs.id = cm.section 
    LEFT JOIN mdl_assign assign ON (cm.module = 1 AND assign.id = cm.instance)
    LEFT JOIN mdl_chat chat ON (cm.module = 4 AND chat.id = cm.instance)
    LEFT JOIN mdl_choice choice ON (cm.module = 5 AND choice.id = cm.instance)
    LEFT JOIN mdl_data data ON (cm.module = 6 AND data.id = cm.instance)
    LEFT JOIN mdl_feedback feedback ON (cm.module = 8 AND feedback.id = cm.instance)
    LEFT JOIN mdl_forum forum ON (cm.module = 9 AND forum.id = cm.instance)
    LEFT JOIN mdl_glossary glossary ON (cm.module = 10 AND glossary.id = cm.instance)
    LEFT JOIN mdl_label l ON (cm.module = 13 AND l.id = cm.instance)
    LEFT JOIN mdl_lesson lesson ON (cm.module = 15 AND lesson.id = cm.instance)    
    LEFT JOIN mdl_lightboxgallery lbox ON (cm.module = 16 AND lbox.id = cm.instance)    
    LEFT JOIN mdl_quiz quiz ON (cm.module = 17 AND quiz.id = cm.instance)    
    LEFT JOIN mdl_resource r ON (cm.module = 18 AND r.id = cm.instance)
    LEFT JOIN mdl_scorm scorm ON (cm.module = 19 AND scorm.id = cm.instance)
    LEFT JOIN mdl_survey survey ON (cm.module = 20 AND survey.id = cm.instance)
    LEFT JOIN mdl_wiki m21 ON (cm.module = 21 AND m21.id = cm.instance)
    LEFT JOIN mdl_workshop m22 ON (cm.module = 22 AND m22.id = cm.instance)
    LEFT JOIN mdl_folder folder ON (cm.module = 23 AND folder.id = cm.instance)
    LEFT JOIN mdl_imscp m24 ON (cm.module = 24 AND m24.id = cm.instance)
    LEFT JOIN mdl_page page ON (cm.module = 25 AND page.id = cm.instance)
    LEFT JOIN mdl_url url ON (cm.module = 26 AND url.id = cm.instance)
    LEFT JOIN mdl_lectopia lectopia ON (cm.module = 27 AND lectopia.id = cm.instance)
    LEFT JOIN mdl_groupselect m28 ON (cm.module = 28 AND m28.id = cm.instance)
    LEFT JOIN mdl_turnitintool turn ON (cm.module = 29 AND turn.id = cm.instance)
    LEFT JOIN mdl_basiclti m30 ON (cm.module = 30 AND m30.id = cm.instance)
    LEFT JOIN mdl_lti m31 ON (cm.module = 31 AND m31.id = cm.instance)
    LEFT JOIN mdl_assign m32 ON (cm.module = 32 AND m32.id = cm.instance)
    LEFT JOIN mdl_book m33 ON (cm.module = 33 AND m33.id = cm.instance)
    LEFT JOIN mdl_respondusws m34 ON (cm.module = 34 AND m34.id = cm.instance)
    -- Go and get the correct section id to put all this stuff info.
    JOIN (select id, course from mdl_course_sections where section = 0) sectzero ON (sectzero.course = cm.course)
  WHERE cs.course <> cm.course
  AND 
        coalesce(assign.course, data.course, feedback.course, glossary.course, lesson.course, lbox.course,
                 scorm.course, m21.course, m22.course, m24.course, m28.course, m30.course, m31.course, m32.course, m33.course,
                 m34.course,
          chat.course, choice.course, forum.course, folder.course, page.course, quiz.course,
                url.course,lectopia.course, turn.course, r.course, l.course,survey.course)
   = cm.course
   ) stufftofix

  WHERE stufftofix.course_module_id = mdl_course_modules.id;