"""add_additional_data_to_metrics_fields

Revision ID: 0ae01f8e0283
Revises: ef11ffd133c7
Create Date: 2020-10-21 09:18:36.104812

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '0ae01f8e0283'
down_revision = '9b0736854b95'
branch_labels = None
depends_on = None

fields = [
    {"field_id": 2, "title": "Leeds", "subtitle": None, "total_name": "Total Leads", "description": "Sum of lead events driven across all platforms."}, #Leads
    {"field_id": 4, "title": "CP Lead", "subtitle": None, "total_name": "Avg CP Lead", "description": "Cost per lead event driven across all platforms."}, #CP Lead
    {"field_id": 6, "title": "Conversions", "subtitle": None, "total_name": "Total Conversions", "description": "Sum of conversions across all platforms."}, #Conversions
    {"field_id": 8, "title": "Sales", "subtitle": None, "total_name": "Total Sales", "description": "Sum of sales."}, #Sales
    {"field_id": 9, "title": "Streams", "subtitle": None, "total_name": "Total Streams", "description": "Sum of streams across Spotify, Apple Music, and Amazon Music."}, # Streams
    {"field_id": 10, "title": "CP", "subtitle": "Sale", "total_name": "Avg CP Sale", "description": "Cost per sale"}, # CP Sale
    {"field_id": 11, "title": "CP Stream", "subtitle": None, "total_name": "Avg CP Stream", "description": "Cost per stream across all DSPs."}, #CP Stream
    {"field_id": 12, "title": "Custom Conv", "subtitle": "Unique", "total_name": "Total Unique Custom Conv", "description": "Sum of unique custom conversions across all platforms."}, #U Custom Conv
    {"field_id": 13, "title": "Custom Conv", "subtitle": "CP Unique", "total_name": "Avg CP Unique Custom Conv", "description": "Cost per unique custom conversion across all platforms."},# CP U Custom Conv
    {"field_id": 14, "title": "Conversions", "subtitle": "Unique", "total_name": "Total U Conv", "description": "Sum of unique conversions across all platforms."}, # U Conversions
    {"field_id": 15, "title": "Conversions", "subtitle": "CP Unique", "total_name": "Avg CP U Conv", "description": "Cost per unique conversion across all platforms."},# CP U Conversion
    {"field_id": 16, "title": "Spend", "subtitle": None, "total_name": "Total Spend", "description": "Currency value spent to the entity selected."}, # Spend
    {"field_id": 17, "title": "Clicks", "subtitle": None, "total_name": "Total Clicks", "description": "Sum of clicks across all platforms."}, # Clicks
    {"field_id": 18, "title": "Thruplays", "subtitle": None, "total_name": "Total Thruplays", "description": "Sum of Thruplays (Facebook & Instagram Only)"}, # Thruplays
    {"field_id": 19, "title": "Click", "subtitle": "CP", "total_name": "Avg CP Click", "description": "Cost per click across all platforms."}, #CP Click
    {"field_id": 20, "title": "Thruplay", "subtitle": "CP", "total_name": "Avg CP Thruplay", "description": "Cost per Thruplay (Facebook & Instagram Only)"}, # CP Thruplay
    {"field_id": 21, "title": "Outbound Click", "subtitle": "CP Unique", "total_name": "Avg U Outbound Clicks", "description": "Cost per unique outbound click across all platforms."}, # CP U Outbound Click
    {"field_id": 22, "title": "Reach", "subtitle": None, "total_name": "Total Reach", "description": "Sum of people reached across all platforms."}, # Reach
    {"field_id": 23, "title": "CTR", "subtitle": None, "total_name": "Avg CTR", "description": "Click-through-rate"}, # CTR
    {"field_id": 25, "title": "Shares", "subtitle": None, "total_name": "Total Shares", "description": "Sum of shares, defined by each platform"}, # Shares
    {"field_id": 26, "title": "Video Views", "subtitle": None, "total_name": "Total Video Views", "description": "Sum of video views, defined by each platform"}, #Video Views
    {"field_id": 27, "title": "Landing Page Views", "subtitle": None, "total_name": "Total Landing Page Views", "description": "Sum of landing page views across all platforms."}, # Landing Page Views
    {"field_id": 28, "title": "Outbound Clicks", "subtitle": "Unique", "total_name": "Total U Outbound Clicks", "description": "Sum of unique outbound clicks across all platforms."}, # U Outbound Clicks
    {"field_id": 30, "title": "CPM Reach", "subtitle": None, "total_name": "Avg CPM Reach", "description": "Cost per 1000 people reached across all platforms."}, # CPM Reach
    {"field_id": 31, "title": "Share", "subtitle": "CP", "total_name": "Avg CP Share", "description": "Cost per share, defined by each platform"}, #CP Share
    {"field_id": 32, "title": "Video View", "subtitle": "CP", "total_name": "Avg CP Video View", "description": "Cost per video view, defined by each platform"}, # CP Video View
    {"field_id": 33, "title": "Landing Page Views", "subtitle": "CP", "total_name": "Avg CP Landing Page View", "description": "Cost per landing page view across all platforms."}, # CP Landing Page Views
    {"field_id": 34, "title": "Outbound Click", "subtitle": "CP Unique", "total_name": "Avg U Outbound Clicks", "description": "Cost per unique outbound click across all platforms."}, #CP U Outbound Clicks
    {"field_id": 35, "title": "Engagement", "subtitle": None, "total_name": "Total Engagement", "description": "Sum of engagement events, defined by each platform"}, #Engagement
    {"field_id": 36, "title": "Clicks", "subtitle": "Unique", "total_name": "Total U Clicks", "description": "Sum of unique clicks"}, # U Clicks
    {"field_id": 37, "title": "Frequency", "subtitle": None, "total_name": "Avg Frequency", "description": "Average number of times a person viewed your ad across all platforms."}, #Frequency
    {"field_id": 38, "title": "Reactions", "subtitle": None, "total_name": "Total Reactions", "description": "Sum of reactions, defined by each platform"}, # Reactions
    {"field_id": 39, "title": "50% Watched", "subtitle": "Video", "total_name": "Total 50% Watched", "description": "Sum of video views where 50% of the video was watched"}, # Video 50% Watched
    {"field_id": 40, "title": "Impressions", "subtitle": None, "total_name": "Total Impressions", "description": "Sum of impressions, defined by each platform"}, # Impressions
    {"field_id": 41, "title": "95% Watched", "subtitle": "Video", "total_name": "Total 95% Watched", "description": "Sum of video views where 95% of the video was watched"}, # Video 95% Watched
    {"field_id": 42, "title": "Outbound CTR", "subtitle": "Unique", "total_name": "Avg Outbound CTR", "description": "Click-through-rate for unique outbound clicks across all platforms."}, # U Outbound CTR
    {"field_id": 43, "title": "Engagement", "subtitle": "CP", "total_name": "Avg CP Engagement", "description": "Cost per engagement event, defined by each platform"}, # CP Engagement
    {"field_id": 44, "title": "Click", "subtitle": "CP Unique", "total_name": "Avg CP U Click", "description": "Cost per unique click"}, # CP U Click
    {"field_id": 45, "title": "Reaction", "subtitle": "CP", "total_name": "Avg CP Reaction", "description": "Cost per reaction, defined by each platform"}, # CP Reaction
    {"field_id": 46, "title": "50% Watched", "subtitle": "CP Video", "total_name": "Avg CP 50% Watched", "description": "Cost per video view where 75% of the video was watched"}, # CP Video 50% Watched
    {"field_id": 47, "title": "CPM", "subtitle": None, "total_name": "Avg CPM", "description": "Cost per 1000 impressions"}, # CPM
    {"field_id": 48, "title": "95% Watched", "subtitle": "CP Video", "total_name": "Avg CP 95% Watched", "description": "Cost per video view where 95% of the video was watched"}, # CP Video 95% Watched
    {"field_id": 49, "title": "Start Date", "subtitle": None, "total_name": "First Start Date", "description": "First day of the flight for the entity selected."}, # Start Date
    {"field_id": 50, "title": "End Date", "subtitle": None, "total_name": "Last End Date", "description": "Last day of the flight for the entity selected."}, # End Date
    {"field_id": 51, "title": "Conversion", "subtitle": "CP", "total_name": "Avg CP Conv", "description": "Cost per conversion across all platforms."}, # CP Conversion
]

def upgrade():
    op.add_column('MetricsField', sa.Column('title', sa.String(), nullable=True))
    op.add_column('MetricsField', sa.Column('subtitle', sa.String(), nullable=True))
    op.add_column('MetricsField', sa.Column('total_name', sa.String(), nullable=True))
    op.add_column('MetricsField', sa.Column('description', sa.String(), nullable=True))

    bind = op.get_bind()
    meta = sa.MetaData(bind=bind)
    meta.reflect()
    metrics_field_table = meta.tables["MetricsField"]
    for field in fields:
        op.execute(
            metrics_field_table.update().where(metrics_field_table.c.id == field.get("field_id")).values(
                title=field.get("title"),
                subtitle=field.get("subtitle"),
                total_name=field.get("total_name"),
                description=field.get("description"),
            )
        )


def downgrade():
    op.drop_column('MetricsField', 'title')
    op.drop_column('MetricsField', 'subtitle')
    op.drop_column('MetricsField', 'total_name')
    op.drop_column('MetricsField', 'description')

    # ### commands auto generated by Alembic - please adjust! ###
    # op.drop_constraint(op.f('uq_MarketingAccount_external_id'), 'MarketingAccount', type_='unique')
    # ### end Alembic commands ###