"""metrics_fields_updates

Revision ID: 710bb10e6e8d
Revises: e71c2b434237
Create Date: 2021-03-23 08:45:21.659976

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = '710bb10e6e8d'
down_revision = 'e71c2b434237'
branch_labels = None
depends_on = None

description_updates = [
    {
        "id": 22,  # Reach
        "old_description": "Sum of estimated unique people reached across all platforms.",
        "new_description": "Sum of estimated unique people reached across all platforms. Value may differ from other ad platforms because Decibel calculates this value by summing unique people each day instead of filtering for unique people across the entire date range.",
    },
    {
        "id": 37,  # Frequency
        "old_description": "Average number of times a person viewed your ad across all platforms.",
        "new_description": "Average number of times a person viewed your ad across all platforms. Value may differ from other ad platforms because Decibel calculates this value by summing unique people each day instead of filtering for unique people across the entire date range.",
    },
    {
        "id": 30,  # CPM Reach
        "old_description": "Cost per 1000 people reached across all platforms.",
        "new_description": "Cost per 1000 people reached across all platforms. Value may differ from other ad platforms because Decibel calculates this value by summing unique people each day instead of filtering for unique people across the entire date range.",
    },
    {
        "id": 28,  # U Outbound Clicks
        "old_description": "Sum of unique link clicks that navigate to a non-Facebook-owned website.",
        "new_description": "Sum of unique link clicks that navigate to a non-Facebook-owned website. Value may differ from other ad platforms because Decibel calculates this value by summing unique people each day instead of filtering for unique people across the entire date range.",
    },
    {
        "id": 36,  # U Clicks
        "old_description": "Sum of unique clicks.",
        "new_description": "Sum of unique clicks. Value may differ from other ad platforms because Decibel calculates this value by summing unique people each day instead of filtering for unique people across the entire date range.",
    },
    {
        "id": 52,  # U Outbound CTR
        "old_description": "Click-through-rate for unique link clicks that navigate to a non-Facebook-owned website.",
        "new_description": "Click-through-rate for unique link clicks that navigate to a non-Facebook-owned website. Value may differ from other ad platforms because Decibel calculates this value by summing unique people each day instead of filtering for unique people across the entire date range.",
    },
    {
        "id": 59,  # U CTR
        "old_description": "Unique click-through-rate.",
        "new_description": "Unique click-through-rate. Value may differ from other ad platforms because Decibel calculates this value by summing unique people each day instead of filtering for unique people across the entire date range. ",
    },
    {
        "id": 64,  # Video 75% Watched
        "old_description": None,
        "new_description": "Percentage of views where at least 75% of the video(s) was played."
    },
    {
        "id": 63,  # Video 25% Watched
        "old_description": None,
        "new_description": "Percentage of views where at least 25% of the video(s) was played."
    },
    {
        "id": 61,  # Cost Per Comment
        "old_description": None,
        "new_description": "Cost per comment, defined by each platform."
    },
    {
        "id": 52,  # Budget
        "old_description": None,
        "new_description": "Currency value allocated to the entity selected."
    }
]

name_updates = [
    {
        "id": 19,
        "old_name": "CP Click",
        "new_name": "Cost Per Click (CPC)",
        "old_total_name": "Avg CP Click",
        "new_total_name": "Total Cost Per Click (CPC)"
    },
    {
        "id": 51,
        "old_name": "CP Conversion",
        "new_name": "Cost Per Conversion",
        "old_total_name": "Avg CP Conv",
        "new_total_name": "Total Cost Per Conversion"
    },
    {
        "id": 43,
        "old_name": "CP Engagement",
        "new_name": "Cost Per Engagement",
        "old_total_name": "Avg CP Engagement",
        "new_total_name": "Total Cost Per Engagement"
    },
    {
        "id": 33,
        "old_name": "CP Landing Page View",
        "new_name": "Cost Per Landing Page View",
        "old_total_name": "Avg CP Landing Page View",
        "new_total_name": "Total Cost Per Landing Page View"
    },
    {
        "id": 32,
        "old_name": "CP Video View",
        "new_name": "Cost Per Video View",
        "old_total_name": "Avg CP Video View",
        "new_total_name": "Total Cost Per Video View"
    },
    {
        "id": 61,
        "old_name": "CP Comment",
        "new_name": "Cost Per Comment",
        "old_total_name": None,
        "new_total_name": "Total Cost Per Comment"
    },
    {
        "id": 4,
        "old_name": "CP Lead",
        "new_name": "Cost Per Lead",
        "old_total_name": "Avg CP Lead",
        "new_total_name": "Total Cost Per Lead"
    },
    {
        "id": 45,
        "old_name": "CP Reaction",
        "new_name": "Cost Per Reaction",
        "old_total_name": "Avg CP Reaction",
        "new_total_name": "Total Cost Per Reaction"
    },
    {
        "id": 20,
        "old_name": "CP Thruplay",
        "new_name": "Cost Per Thruplay",
        "old_total_name": "Avg CP Thruplay",
        "new_total_name": "Total Cost Per Thruplay"
    },
    {
        "id": 44,
        "old_name": "CP U Click",
        "new_name": "Cost Per Unique Click",
        "old_total_name": "Avg CP U Click",
        "new_total_name": "Total Cost Per Unique Click"
    },
    {
        "id": 15,
        "old_name": "CP U Conversion",
        "new_name": "Cost Per Unique Conversion",
        "old_total_name": "Avg CP U Conv",
        "new_total_name": "Total Cost Per Unique Conversion"
    },
    {
        "id": 21,
        "old_name": "CP U Outbound Click",
        "new_name": "Cost Per Unique Outbound Click",
        "old_total_name": "Avg U Outbound Clicks",
        "new_total_name": "Total Cost Per Unique Outbound Click"
    },
    {
        "id": 36,
        "old_name": "U Clicks",
        "new_name": "Unique Clicks",
        "old_total_name": "Total U Clicks",
        "new_total_name": "Total Unique Clicks"
    },
    {
        "id": 59,
        "old_name": "U CTR",
        "new_name": "Unique CTR",
        "old_total_name": None,
        "new_total_name": "Total Unique CTR"
    },
    {
        "id": 28,
        "old_name": "U Outbound Clicks",
        "new_name": "Unique Outbound Clicks",
        "old_total_name": "Total U Outbound Clicks",
        "new_total_name": "Total Unique Outbound Clicks"
    },
    {
        "id": 42,
        "old_name": "U Outbound CTR",
        "new_name": "Unique Outbound CTR",
        "old_total_name": "Avg Outbound CTR",
        "new_total_name": "Total Unique Outbound CTR"
    },
    {
        "id": 37,
        "old_name": "Frequency",
        "new_name": "Frequency",
        "old_total_name": "Avg Frequency",
        "new_total_name": "Total Frequency"
    },
    {
        "id": 30,
        "old_name": "CPM Reach",
        "new_name": "CPM Reach",
        "old_total_name": "Avg CPM Reach",
        "new_total_name": "Total CPM Reach"
    },
    {
        "id": 31,
        "old_name": "CP Share",
        "new_name": "Cost Per Share",
        "old_total_name": "Avg CP Share",
        "new_total_name": "Total Cost Per Share"
    },
    {
        "id": 23,
        "old_name": "CTR",
        "new_name": "CTR",
        "old_total_name": "Avg CTR",
        "new_total_name": "Total CTR"
    },
    {
        "id": 41,
        "old_name": "Video 95% Watched",
        "new_name": "Video 100% Watched",
        "old_total_name": "Avg Video 95% Watched",
        "new_total_name": "Average Video 100% Watched"
    },
    {
        "id": 39,
        "old_name": "Video 50% Watched",
        "new_name": "Video 50% Watched",
        "old_total_name": "Avg Video 50% Watched",
        "new_total_name": "Average Video 50% Watched"
    },
    {
        "id": 64,
        "old_name": "Video 75% Watched",
        "new_name": "Video 75% Watched",
        "old_total_name": None,
        "new_total_name": "Average Video 75% Watched"
    },
    {
        "id": 63,
        "old_name": "Video 25% Watched",
        "new_name": "Video 25% Watched",
        "old_total_name": None,
        "new_total_name": "Average Video 25% Watched"
    },
    {
        "id": 52,
        "old_name": "Budget",
        "new_name": "Budget",
        "old_total_name": None,
        "new_total_name": "Total Budget"
    }
]

title_updates = [
    {
        "id": 59,
        "old_title": None,
        "new_title": "CTR",
        "old_subtitle": None,
        "new_subtitle": "Unique"
    },
    {
        "id": 4,
        "old_title": "CP Lead",
        "new_title": "Leads",
        "old_subtitle": None,
        "new_subtitle": "Cost Per"
    },
    {
        "id": 61,
        "old_title": None,
        "new_title": "Comments",
        "old_subtitle": None,
        "new_subtitle": "Cost Per"
    },
    {
        "id": 63,
        "old_title": None,
        "new_title": "25% Watched",
        "old_subtitle": None,
        "new_subtitle": "Video"
    },
    {
        "id": 64,
        "old_title": None,
        "new_title": "75% Watched",
        "old_subtitle": None,
        "new_subtitle": "Video"
    },
    {
        "id": 41,
        "old_title": "95% Watched",
        "new_title": "100% Watched",
        "old_subtitle": None,
        "new_subtitle": "Video"
    }
]


def upgrade():
    bind = op.get_bind()
    meta = sa.MetaData(bind=bind)
    meta.reflect()
    metrics_table = meta.tables["MetricsField"]

    for update in description_updates:
        metric_id = update["id"]
        new_description = update["new_description"]
        op.execute(
            metrics_table.update().where(metrics_table.c.id == metric_id).values(description=new_description)
        )

    for update in name_updates:
        metric_id = update["id"]
        new_name = update["new_name"]
        new_total_name = update["new_total_name"]
        op.execute(
            metrics_table.update().where(metrics_table.c.id == metric_id).values(
                name=new_name,
                total_name=new_total_name
            )
        )

    for update in title_updates:
        metric_id = update["id"]
        new_title = update["new_title"]
        new_subtitle = update["new_subtitle"]
        op.execute(
            metrics_table.update().where(metrics_table.c.id == metric_id).values(
                title=new_title,
                subtitle=new_subtitle
            )
        )

    op.execute(
        metrics_table.delete().where(metrics_table.c.id == 34)  # Delete extra Cost Per Unique Outbound Click field
    )

    op.execute(
        metrics_table.update().where(metrics_table.c.subtitle == "CP").values(subtitle="Cost Per")
    )
    op.execute(
        metrics_table.update().where(metrics_table.c.subtitle == "CP Unique").values(subtitle="Cost Per Unique")
    )


def downgrade():
    bind = op.get_bind()
    meta = sa.MetaData(bind=bind)
    meta.reflect()
    metrics_table = meta.tables["MetricsField"]

    for update in description_updates:
        metric_id = update["id"]
        old_description = update["old_description"]
        op.execute(
            metrics_table.update().where(metrics_table.c.id == metric_id).values(description=old_description)
        )

    for update in name_updates:
        metric_id = update["id"]
        old_name = update["old_name"]
        old_total_name = update["old_total_name"]
        op.execute(
            metrics_table.update().where(metrics_table.c.id == metric_id).values(
                name=old_name,
                total_name=old_total_name
            )
        )

    for update in title_updates:
        metric_id = update["id"]
        old_title = update["old_title"]
        old_subtitle = update["old_subtitle"]
        op.execute(
            metrics_table.update().where(metrics_table.c.id == metric_id).values(
                title=old_title,
                subtitle=old_subtitle
            )
        )

    cp_u_outbound_click_field = {
            "id": 34,
            "name": "Cost Per Unique Outbound Click",
            "type_id": 1,
            "format_id": 4,
            "objective_id": 9,
            "title": "Outbound Click",
            "subtitle": "CP Unique",
            "total_name": "Total Cost Per Unique Outbound Click",
            "description": "Cost per unique outbound click across all platforms."
        }

    op.execute(
        metrics_table.insert().values(**cp_u_outbound_click_field)
    )
    op.execute(
        metrics_table.update().where(metrics_table.c.subtitle == "Cost Per").values(subtitle="CP")
    )
    op.execute(
        metrics_table.update().where(metrics_table.c.subtitle == "Cost Per Unique").values(subtitle="CP Unique")
    )