"""all_popularity_mat_view

Revision ID: 1db4b7f01a87
Revises: 9f28e0fa909c
Create Date: 2018-04-13 11:41:45.462198

"""

# revision identifiers, used by Alembic.
revision = '1db4b7f01a87'
down_revision = '9f28e0fa909c'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.execute('''
CREATE MATERIALIZED VIEW all_popularity AS 
SELECT spy_track_popularity.track_spyid,
    json_agg(((((('['::text || (('now'::text)::date - (spy_track_popularity.as_of)::date)) || ','::text) || spy_track_popularity.value) || ']'::text))::json ORDER BY spy_track_popularity.as_of) AS vals,
    (array_agg(spy_track_popularity.value ORDER BY spy_track_popularity.as_of DESC))[0] AS most_recent_value,
    max(spy_track_popularity.as_of) AS most_recent_as_of,
    max(
        CASE
            WHEN ((spy_track_popularity.as_of)::date = (('now'::text)::date - 1)) THEN spy_track_popularity.value
            ELSE NULL::integer
        END) AS d1,
    max(
        CASE
            WHEN ((spy_track_popularity.as_of)::date = (('now'::text)::date - 2)) THEN spy_track_popularity.value
            ELSE NULL::integer
        END) AS d2,
    max(
        CASE
            WHEN ((spy_track_popularity.as_of)::date = (('now'::text)::date - 3)) THEN spy_track_popularity.value
            ELSE NULL::integer
        END) AS d3,
    max(
        CASE
            WHEN ((spy_track_popularity.as_of)::date = (('now'::text)::date - 4)) THEN spy_track_popularity.value
            ELSE NULL::integer
        END) AS d4,
    max(
        CASE
            WHEN ((spy_track_popularity.as_of)::date = (('now'::text)::date - 5)) THEN spy_track_popularity.value
            ELSE NULL::integer
        END) AS d5,
    max(
        CASE
            WHEN ((spy_track_popularity.as_of)::date = (('now'::text)::date - 10)) THEN spy_track_popularity.value
            ELSE NULL::integer
        END) AS d10,
    max(
        CASE
            WHEN ((spy_track_popularity.as_of)::date = (('now'::text)::date - 20)) THEN spy_track_popularity.value
            ELSE NULL::integer
        END) AS d20,
    max(
        CASE
            WHEN ((spy_track_popularity.as_of)::date = (('now'::text)::date - 30)) THEN spy_track_popularity.value
            ELSE NULL::integer
        END) AS d30
   FROM spy_track_popularity
  GROUP BY spy_track_popularity.track_spyid;

CREATE INDEX all_popularity_track_spyid_idx
  ON all_popularity (track_spyid);

    
    ''')


def downgrade():
    op.execute('drop MATERIALIZED VIEW all_popularity')