# Generated by Django 2.1.2 on 2018-10-04 11:54

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [("coursera", "0014_merge_20181002_0826")]

    operations = [
        migrations.RunSQL(
            [
                """
                CREATE MATERIALIZED VIEW
                    clickstream_events_view
                AS
                SELECT DISTINCT ON (id)
                    MD5(COALESCE(hashed_user_id, '') || COALESCE(hashed_session_cookie_id, '') || COALESCE(server_timestamp::text, '')
                        || COALESCE(hashed_ip, '') || COALESCE(user_agent, '') || COALESCE(url, '') || COALESCE(initial_referrer_url, '')
                        || COALESCE(browser_language, '') || COALESCE(course_id, '') || COALESCE(country_cd, '')
                        || COALESCE(region_cd, '') || COALESCE(timezone, '') || COALESCE(os, '') || COALESCE(browser, '')
                        || COALESCE(key, '') || COALESCE(value, ''))::varchar(50) as id,
                    hashed_user_id,
                    hashed_session_cookie_id,
                    server_timestamp,
                    hashed_ip,
                    user_agent,
                    url,
                    initial_referrer_url,
                    browser_language,
                    course_id,
                    country_cd,
                    region_cd,
                    timezone,
                    os,
                    browser,
                    key,
                    value::jsonb
                FROM
                    clickstream_events
                """,
                """
                CREATE UNIQUE INDEX ON clickstream_events_view (id)
                """,
                """
                CREATE INDEX ON clickstream_events_view (course_id)
                """,
                """
                CREATE INDEX ON clickstream_events_view (key)
                """,
            ],
            reverse_sql="DROP MATERIALIZED VIEW IF EXISTS clickstream_events_view",
        ),
        migrations.RunSQL(
            # Select all clickstream events of type 'heartbeat'. Denormalize
            # module_id and item_id to point to the primary keys of
            # course_branch_modules_view and course_branch_items_view. Group
            # each timecode into buckets of 5 seconds each.
            #
            # This view is used to calculate the views over runtime of a video.
            #
            # video-analytics/
            # - views_over_runtime
            [
                """
                CREATE MATERIALIZED VIEW
                    heartbeat_events
                AS
                WITH latest_branches AS (
                    SELECT DISTINCT ON (course_id)
                        course_id, course_branch_id
                    FROM
                        course_branches
                    ORDER BY
                        course_id, authoring_course_branch_created_ts DESC NULLS LAST
                )
                SELECT
                    id,
                    course_id,
                    hashed_user_id as eitdigital_user_id,
                    hashed_session_cookie_id,
                    server_timestamp,
                    hashed_ip,
                    user_agent,
                    url,
                    initial_referrer_url,
                    browser_language,
                    country_cd,
                    region_cd,
                    timezone,
                    os,
                    browser,
                    MD5(MD5(course_branch_id) || (value->>'module_id'))::varchar(50) as module_id,
                    MD5(MD5(course_branch_id) || (value->>'item_id'))::varchar(50) as item_id,
                    (div(((value->>'timecode')::real)::int, 5) * 5)::int as timecode
                FROM
                    clickstream_events_view
                    JOIN latest_branches USING (course_id)
                WHERE
                    key = 'heartbeat'
                """,
                """
                CREATE UNIQUE INDEX ON heartbeat_events (id)
                """,
                """
                CREATE INDEX ON heartbeat_events (course_id)
                """,
                """
                CREATE INDEX ON heartbeat_events (module_id)
                """,
                """
                CREATE INDEX ON heartbeat_events (item_id)
                """,
                """
                CREATE INDEX ON heartbeat_events (eitdigital_user_id)
                """,
            ],
            reverse_sql="DROP MATERIALIZED VIEW IF EXISTS heartbeat_events",
        ),
    ]
