# Generated by Django 2.1.1 on 2018-09-26 13:29

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [("coursera", "0011_create_last_activity_view")]

    operations = [
        migrations.RunSQL(
            # For each course and user, the last_activity_per_module view
            # selects the last module in which that user had any activity,
            # along with the timestamp of that activity and the module in
            # which this activity was.
            #
            # This view is used to determine during which module learners left
            # the course.
            #
            # course-analytics/
            # - leaving_learners_per_module
            sql=[
                """
                CREATE MATERIALIZED VIEW IF NOT EXISTS
                    last_activity_per_module
                AS
                SELECT
                    MD5(MD5(MD5(module_id) || last_activity_view.id) || eitdigital_user_id)::varchar(50) as id,
                    module_id,
                    last_activity_view.id::varchar(50) as last_activity_id,
                    eitdigital_user_id,
                    course_progress_ts
                FROM
                    course_branch_modules_view
                    JOIN course_branches USING (course_branch_id)
                    JOIN course_branch_lessons_view USING (course_branch_id, module_id)
                    JOIN course_branch_items_view USING (course_branch_id, lesson_id)
                    JOIN last_activity_view USING (course_id, item_id)
                    LEFT JOIN course_grades USING (course_id, eitdigital_user_id)
                WHERE
                    course_passing_state_id IS NULL
                    OR course_passing_state_id NOT IN (1, 2)
                """,
                """
                CREATE UNIQUE INDEX ON last_activity_per_module (id)
                """,
                """
                CREATE INDEX ON last_activity_per_module (module_id)
                """,
                """
                CREATE INDEX ON last_activity_per_module (last_activity_id)
                """,
                """
                CREATE INDEX ON last_activity_per_module (eitdigital_user_id)
                """,
            ],
            reverse_sql="""
            DROP MATERIALIZED VIEW IF EXISTS last_activity_per_module
            """,
        )
    ]
