# Generated by Django 2.1.2 on 2018-10-05 10:25

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [("coursera", "0015_create_clickstream_views")]

    operations = [
        migrations.RunSQL(
            [
                """
                CREATE MATERIALIZED VIEW
                    course_item_grades_view
                AS
                SELECT DISTINCT ON (course_id, course_item_id, eitdigital_user_id)
                    MD5(MD5(MD5(course_id) || course_item_id) || eitdigital_user_id)::varchar(50) as id,
                    course_id,
                    MD5(MD5(course_branch_id) || course_item_id)::varchar(50) as item_id,
                    eitdigital_user_id,
                    course_item_grade_ts,
                    course_item_passing_state_desc,
                    course_item_grade_overall,
                    course_item_grade_verified,
                    course_item_grade_pending
                FROM
                    course_item_grades
                    JOIN course_branches USING (course_id)
                    JOIN course_item_passing_states USING (course_item_passing_state_id)
                ORDER BY
                    course_id,
                    course_item_id,
                    eitdigital_user_id,
                    authoring_course_branch_created_ts DESC
                """,
                """
                CREATE UNIQUE INDEX ON course_item_grades_view (id)
                """,
                """
                CREATE INDEX ON course_item_grades_view (course_id)
                """,
                """
                CREATE INDEX ON course_item_grades_view (item_id)
                """,
                """
                CREATE INDEX ON course_item_grades_view (eitdigital_user_id)
                """,
            ],
            reverse_sql="DROP MATERIALIZED VIEW IF EXISTS course_item_grades_view",
        )
    ]
