# Generated by Django 2.1.2 on 2018-10-15 08:30

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [("coursera", "0040_create_specialization_views")]

    operations = [
        migrations.RunSQL(
            # For each module and user, select the duration between that
            # user's first and last activity in the specific module.
            #
            # Used to calculate the average time that users spend on a module.
            #
            # course-analytics/
            # - average_time_per_module
            [
                """
                CREATE MATERIALIZED VIEW
                    module_duration_view
                AS
                SELECT DISTINCT
                    MD5(MD5(MD5(module_id) || eitdigital_user_id) || module_first_activity_view.course_progress_ts)::varchar(50) as id,
                    module_id,
                    eitdigital_user_id,
                    module_first_activity_view.course_progress_ts AS module_start_ts,
                    module_last_activity_view.course_progress_ts - module_first_activity_view.course_progress_ts AS duration
                FROM
                    module_first_activity_view
                    JOIN module_last_activity_view USING (module_id, eitdigital_user_id)
                """,
                """
                CREATE UNIQUE INDEX ON module_duration_view (id)
                """,
                """
                CREATE INDEX ON module_duration_view (module_id, module_start_ts)
                """,
                """
                CREATE INDEX ON module_duration_view (eitdigital_user_id)
                """,
            ],
            reverse_sql="DROP MATERIALIZED VIEW IF EXISTS module_duration_view",
        )
    ]
