# Generated by Django 2.1.2 on 2018-10-11 11:37

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [("coursera", "0022_create_item_rating_view")]

    operations = [
        migrations.RunSQL(
            # Denormalizes assessment_responses and assessment_actions into a
            # single table. This is needed for performance.
            #
            # This view is not used directly.
            #
            # The assessment_last_attempt_responses_view,
            # assessment_attempts_view and
            # assessment_answers_over_time_view depend on this view.
            [
                """
                CREATE MATERIALIZED VIEW
                    assessment_response_actions_view
                AS
                SELECT
                    assessment_id,
                    assessment_action_base_id,
                    assessment_action_id,
                    assessment_action_start_ts,
                    assessment_action_ts,
                    assessment_action_version,
                    assessment_question_id,
                    assessment_response_id,
                    assessment_response_score,
                    assessment_response_weighted_score,
                    assessment_scope_id,
                    assessment_scope_type_id,
                    eitdigital_user_id
                FROM
                    assessment_responses
                    JOIN assessment_actions USING (assessment_id, assessment_action_id, assessment_action_version)
                """,
                """
                CREATE UNIQUE INDEX ON assessment_response_actions_view (assessment_response_id)
                """,
                """
                CREATE INDEX ON assessment_response_actions_view (eitdigital_user_id)
                """,
                """
                CREATE INDEX ON assessment_response_actions_view (assessment_action_id)
                """,
                """
                CREATE INDEX ON assessment_response_actions_view (assessment_id)
                """,
                """
                CREATE INDEX ON assessment_response_actions_view (assessment_question_id)
                """,
            ],
            reverse_sql="DROP MATERIALIZED VIEW IF EXISTS assessment_response_actions_view",
        ),
        migrations.RunSQL(
            # For each unique tuple of assessment, user and
            # assessment_question_order, select the latest response for that
            # question. This view is needed for perforrmance.
            #
            # This view is not used directly.
            #
            # The assessment_last_attempt_view depends on this view.
            [
                """
                CREATE MATERIALIZED VIEW
                    assessment_last_attempt_responses_view
                AS
                SELECT DISTINCT ON (assessment_id, eitdigital_user_id, assessment_question_order)
                    assessment_id,
                    assessment_action_base_id,
                    assessment_action_id,
                    assessment_action_start_ts,
                    assessment_action_ts,
                    assessment_action_version,
                    assessment_question_id,
                    assessment_response_id,
                    assessment_response_score,
                    assessment_response_weighted_score,
                    assessment_scope_id,
                    assessment_scope_type_id,
                    eitdigital_user_id,
                    assessment_question_internal_id,
                    assessment_question_cuepoint,
                    assessment_question_order,
                    assessment_question_weight
                FROM
                    assessment_response_actions_view
                    JOIN assessment_assessments_questions USING (assessment_id, assessment_question_id)
                WHERE
                    assessment_response_weighted_score IS NOT NULL
                    AND assessment_question_weight IS NOT NULL
                ORDER BY
                    assessment_id,
                    eitdigital_user_id,
                    assessment_question_order,
                    assessment_action_ts DESC
                """,
                """
                CREATE UNIQUE INDEX ON assessment_last_attempt_responses_view (assessment_response_id)
                """,
                """
                CREATE INDEX ON assessment_last_attempt_responses_view (eitdigital_user_id)
                """,
                """
                CREATE INDEX ON assessment_last_attempt_responses_view (assessment_action_id)
                """,
                """
                CREATE INDEX ON assessment_last_attempt_responses_view (assessment_id)
                """,
                """
                CREATE INDEX ON assessment_last_attempt_responses_view (assessment_question_id)
                """,
            ],
            reverse_sql="DROP MATERIALIZED VIEW IF EXISTS assessment_last_attempt_responses_view",
        ),
        migrations.RunSQL(
            # For each assessment and user, calculate the score of their last
            # attempt. Also select the timestamp of their last submitted
            # response.
            #
            # This view is used to calculate the averege grade and grade
            # distribution for learner's last attempts.
            #
            # quiz-analytics/
            # - last_attempt_average_grade
            # - last_attempt_grade_distribution
            [
                """
                CREATE MATERIALIZED VIEW
                    assessment_last_attempt_view
                AS
                SELECT
                    MD5(MD5(assessment_id) || eitdigital_user_id)::varchar(50) as id,
                    assessment_id,
                    eitdigital_user_id,
                    MAX(assessment_action_ts) as assessment_action_ts,
                    SUM(assessment_response_weighted_score)::real / SUM(assessment_question_weight) as score
                FROM
                    assessment_last_attempt_responses_view
                GROUP BY
                    assessment_id, eitdigital_user_id
                """,
                """
                CREATE UNIQUE INDEX ON assessment_last_attempt_view (id)
                """,
                """
                CREATE INDEX ON assessment_last_attempt_view (assessment_id)
                """,
                """
                CREATE INDEX ON assessment_last_attempt_view (eitdigital_user_id)
                """,
            ],
            reverse_sql="DROP MATERIALIZED VIEW IF EXISTS assessment_last_attempt_view",
        ),
    ]
