"""charts

Revision ID: 4c2fc51d95fd
Revises: e272fdce3d1d
Create Date: 2018-08-14 12:54:30.586897

"""

# revision identifiers, used by Alembic.
revision = "4c2fc51d95fd"
down_revision = "e272fdce3d1d"
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

import json

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "charts",
        sa.Column("id", sa.String(), nullable=False),
        sa.Column("title", sa.String(), nullable=False),
        sa.Column("subtitle", sa.String(), nullable=True),
        sa.Column("background_id", sa.String(), nullable=True),
        sa.Column("created_by", sa.String(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False, server_default=sa.text('now()')),
        sa.Column("deleted_at", sa.DateTime(), nullable=True),
        sa.Column("public", sa.Boolean(), nullable=False),
        sa.Column("api_path", sa.String(), nullable=False),
        sa.Column("api_settings", postgresql.JSONB(none_as_null=True), nullable=True),
        sa.ForeignKeyConstraint(["created_by"], ["users.username"]),
        sa.PrimaryKeyConstraint("id"),
    )

    existing_charts = [
        {
            "id": "whitelist1",
            "bg": "whitelist1",
            "title": "Listen To This!",
            "subtitle": "The most important tracks on The Whitelist right now.",
            "nextApi": "/api/charts/curated/top5",
        },
        {
            "id": "whitelist2",
            "bg": "whitelist1",
            "title": "Listen To This US 🇺🇸!",
            "subtitle": "The most important US tracks on The Whitelist right now.",
            "nextApi": "/api/charts/curated/top5us",
        },
        {
            "id": "whitelist3",
            "bg": "whitelist1",
            "title": "Listen To This Oz/NZ 🇦🇺🇳🇿!",
            "subtitle": "The most important US tracks on The Whitelist right now.",
            "nextApi": "/api/charts/curated/top5aus",
        },
        {
            "id": "spotify2",
            "bg": "spotify1",
            "title": "New and Popular on Spotify",
            "subtitle": "The most popular unsigned tracks released in the last 10 days",
            "nextApi": "/api/charts/SpotifyEmerging/10",
        },
        {
            "id": "soundcloud1",
            "title": "SoundCloud 10\u00A0by\u00A010",
            "subtitle": "Tracks reaching 10k streams in their first 10 days.",
            "nextApi": "/api/charts/SoundCloudNewTracks",
            "apiSettings": {
                "publishedDaysAgo": 10,
                "minPlays": 10000,
                "isScouted": True,
                "maxFollowers": 1000000,
                "countries": None,
            },
        },
        {
            "id": "soundcloud2",
            "bg": "soundcloud1",
            "title": "SoundCloud 5\u00A0by\u00A010 Oz & NZ",
            "subtitle": "🇦🇺 🇳🇿 Asia-Pacific tracks reaching 5k streams in their first 10 days.",
            "nextApi": "/api/charts/SoundCloudNewTracks",
            "apiSettings": {
                "filters": {
                    "publishedDaysAgo": 10,
                    "minPlays": 5000,
                    "isScouted": True,
                    "maxFollowers": 1000000,
                    "countries": ["AU", "NZ"],
                }
            },
        },
        {
            "id": "soundcloud3",
            "bg": "soundcloud1",
            "title": "SoundCloud 10\u00A0by\u00A010 US & Can",
            "subtitle": "🇺🇸 🇨🇦 US and Canadian tracks reaching 10k streams in their first 10 days.",
            "nextApi": "/api/charts/SoundCloudNewTracks",
            "apiSettings": {
                "filters": {
                    "publishedDaysAgo": 10,
                    "minPlays": 10000,
                    "isScouted": True,
                    "maxFollowers": 1000000,
                    "countries": ["US", "CA"],
                }
            },
        },
        {
            "id": "instagram2",
            "bg": "instagram1",
            "title": "Your Insta Scoutings",
            "subtitle": "New follows by your scouts.",
            "nextApi": "/api/charts/InstagramMyScoutings",
        },
        {
            "id": "instagram1",
            "title": "Instagram Exploders",
            "subtitle": "Artists getting a lot of new followers on Instagram.",
            "nextApi": "/api/charts/InstagramFollowerJump",
        },
        {
            "id": "YouTubeNew",
            "bg": "youtube1",
            "title": "New-Tube",
            "subtitle": "Videos published recently and getting early buzz",
            "nextApi": "/api/charts/YouTubeNew",
        },
        {
            "id": "mostscouted1",
            "bg": "whitelist1",
            "title": "Most Scouted Recently",
            "subtitle": "Scoutings in the last 60 days.",
            "nextApi": "/api/charts/MostScoutedRecently",
        },
        {
            "id": "spotify1",
            "title": "Emerging on Spotify",
            "subtitle": "Popular songs from artists not signed to a major.",
            "nextApi": "/api/charts/SpotifyEmerging/120",
        },
    ]

    op.execute('''
    create index sc_tracks_charts_idx1
    on sc_tracks (
      artist_scid,
      duration,
      ((created_at)::date),
      ((coalesce(data ->> 'playback_count', '0'))::int),
      ((coalesce(data ->> 'likes_count', '0'))::int),
      ((coalesce(data ->> 'comment_count', '0'))::int),
      scid
    );
    ''')

    connect = op.get_bind()

    for chart in existing_charts:
        connect.execute("""
            insert into charts 
            (id, title, subtitle, background_id, api_path, public, api_settings)
            values
            (%s, %s, %s, %s, %s, true, (%s)::jsonb)
        """,
            (
                chart["id"],
                chart["title"],
                chart["subtitle"],
                (chart.get("bg") or chart["id"]),
                chart["nextApi"],
                json.dumps(chart.get("apiSettings")) if chart.get("apiSettings") else None,
            ),
        )

    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_table("charts")
    ### end Alembic commands ###