"""add_linkfire_metrics

Revision ID: 413480848b31
Revises: 0ec001ff264b
Create Date: 2021-06-01 10:25:42.058973

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '413480848b31'
down_revision = '2302afc25984'
branch_labels = None
depends_on = None

linkfire_fields = [
    {
        "id": 85,
        "name": "Linkfire Bounce Rate",
        "title": "Bounce Rate",
        "subtitle": "Linkfire",
        "total_name": "Total Linkfire Bounce Rate",
        "description": "Number of people who landed on the Linkfire page, but did not perform an action.",
        "format_id": 5,
        "type_id": 8
    },
    {
        "id": 72,
        "name": "Linkfire Clicks",
        "title": "Clicks",
        "subtitle": "Linkfire",
        "total_name": "Total Linkfire Clicks",
        "description": "Count of manual clicks triggered on the Linkfire landing page.",
        "format_id": 3,
        "type_id": 8
    },
    {
        "id": 73,
        "name": "Linkfire Conversions",
        "title": "Conversions",
        "subtitle": "Linkfire",
        "total_name": "Total Linkfire Conversions",
        "description": "Count of conversion events logged by Linkfire.",
        "format_id": 3,
        "type_id": 8
    },
    {
        "id": 74,
        "name": "Linkfire CTR",
        "title": "CTR",
        "subtitle": "Linkfire",
        "total_name": "Total Linkfire CTR",
        "description": "Percentage of people who click on a link after seeing the Linkfire page (does not include d2s).",
        "format_id": 5,
        "type_id": 8
    },
    {
        "id": 75,
        "name": "Linkfire Direct to Service",
        "title": "Direct to Service",
        "subtitle": "Linkfire",
        "total_name": "Total Linkfire Direct to Service",
        "description": "Count of automatic Linkfire service redirects",
        "format_id": 3,
        "type_id": 8
    },
    {
        "id": 76,
        "name": "Linkfire Event Rate",
        "title": "Event Rate",
        "subtitle": "Linkfire",
        "total_name": "Total Linkfire Event Rate",
        "description": "Rate at which people triggered a Linkfire Click Event after viewing the Linkfire page.",
        "format_id": 5,
        "type_id": 8
    },
    {
        "id": 77,
        "name": "Linkfire Events",
        "title": "Events",
        "subtitle": "Linkfire",
        "total_name": "Total Linkfire Events",
        "description": "Sum of all Linkfire interactions, including clicks, direct to service, & in page player. (does not count pageviews)",
        "format_id": 3,
        "type_id": 8
    },
    {
        "id": 78,
        "name": "Linkfire In Page Plays",
        "title": "In Page Plays",
        "subtitle": "Linkfire",
        "total_name": "Total Linkfire In Page Plays",
        "description": "Count of Linkfire landing page player plays",
        "format_id": 3,
        "type_id": 8
    },
    {
        "id": 79,
        "name": "Linkfire New Sessions",
        "title": "New Sessions",
        "subtitle": "Linkfire",
        "total_name": "Total Linkfire New Sessions",
        "description": "Number of new sessions triggered on a Linkfire Landing Page.",
        "format_id": 3,
        "type_id": 8
    },
    {
        "id": 80,
        "name": "Linkfire Page Views",
        "title": "Page Views",
        "subtitle": "Linkfire",
        "total_name": "Total Linkfire Page Views",
        "description": "Count of times the Linkfire landing page was loaded for view.",
        "format_id": 3,
        "type_id": 8
    },
    {
        "id": 82,
        "name": "Platform to Linkfire Event Rate",
        "title": "Event Rate",
        "subtitle": "Platform to Linkfire",
        "total_name": "Total Platform to Linkfire Event Rate",
        "description": "Rate at which people saw an ad, then triggered any event on a Linkfire landing page.",
        "format_id": 5,
        "type_id": 8
    },
    {
        "id": 83,
        "name": "Platform to Linkfire Bounce Rate",
        "title": "Bounce Rate",
        "subtitle": "Platform to Linkfire",
        "total_name": "Total Platform to Linkfire Bounce Rate",
        "description": "Rate at which people saw an ad, then only triggered a Linkfire Page View.",
        "format_id": 5,
        "type_id": 8
    },
    {
        "id": 84,
        "name": "Platform to Linkfire Click Rate",
        "title": "Click Rate",
        "subtitle": "Platform to Linkfire",
        "total_name": "Total Platform to Linkfire Click Rate",
        "description": "Rate at which people saw an ad, then clicked on a link on the Linkfire landing page.",
        "format_id": 5,
        "type_id": 8
    },
]

objective_metrics_fields = [
    {"objective_id": 3, "metrics_field_id": 16, "index": 0},
    {"objective_id": 3, "metrics_field_id": 6, "index": 10},
    {"objective_id": 3, "metrics_field_id": 51, "index": 20},
    {"objective_id": 3, "metrics_field_id": 71, "index": 30},
    {"objective_id": 3, "metrics_field_id": 15, "index": 40},
    {"objective_id": 3, "metrics_field_id": 80, "index": 50},
    {"objective_id": 3, "metrics_field_id": 72, "index": 60},
    {"objective_id": 3, "metrics_field_id": 74, "index": 70},
    {"objective_id": 3, "metrics_field_id": 73, "index": 80},
    {"objective_id": 3, "metrics_field_id": 49, "index": 90},
    {"objective_id": 3, "metrics_field_id": 50, "index": 100},

    {"objective_id": 16, "metrics_field_id": 16, "index": 0},
    {"objective_id": 16, "metrics_field_id": 27, "index": 10},
    {"objective_id": 16, "metrics_field_id": 33, "index": 20},
    {"objective_id": 16, "metrics_field_id": 80, "index": 30},
    {"objective_id": 16, "metrics_field_id": 72, "index": 40},
    {"objective_id": 16, "metrics_field_id": 28, "index": 50},
    {"objective_id": 16, "metrics_field_id": 21, "index": 60},
    {"objective_id": 16, "metrics_field_id": 36, "index": 70},
    {"objective_id": 16, "metrics_field_id": 44, "index": 80},
    {"objective_id": 16, "metrics_field_id": 49, "index": 90},
    {"objective_id": 16, "metrics_field_id": 50, "index": 100},
]


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

    # Update Landing Page Views description
    op.execute(
        metrics_table.update().values(
            description="Count of times the Linkfire landing page was loaded for view. (aggregated cross-DSP)"
        ).where(metrics_table.c.id == 27)
    )

    # Wide index range to insert a new metrics
    op.execute(
        metrics_table.update().values(order_index=metrics_table.c.order_index * 100).where(metrics_table.c.type_id == 3)
    )

    op.execute(metrics_table.update().values(order_index=520, type_id=3).where(metrics_table.c.id == 27))
    op.execute(metrics_table.update().values(order_index=570, type_id=3).where(metrics_table.c.id == 33))

    # Insert new metrics type description
    op.execute(metrics_type_table.insert().values(id=8, name="Linkfire", order_index=6))

    # Insert new metrics
    for index, metric in enumerate(linkfire_fields):
        op.execute(metrics_table.insert().values({**metric, "order_index": index}))

    # Drop all objective metrics sets for Convertions & Traffic
    op.execute(objective_metrics_table.delete().where(objective_metrics_table.c.objective_id.in_([3, 16])))

    # Replace objective metrics sets for Convertions & Traffic
    for field in objective_metrics_fields:
        op.execute(objective_metrics_table.insert().values(**field))


old_objective_metrics = [
  {"objective_id": 3, "metrics_field_id": 16, "index": 0},
  {"objective_id": 3, "metrics_field_id": 6, "index": 30},
  {"objective_id": 3, "metrics_field_id": 51, "index": 40},
  {"objective_id": 3, "metrics_field_id": 27, "index": 50},
  {"objective_id": 3, "metrics_field_id": 33, "index": 60},
  {"objective_id": 3, "metrics_field_id": 49, "index": 70},
  {"objective_id": 3, "metrics_field_id": 50, "index": 80},
  {"objective_id": 3, "metrics_field_id": 68, "index": 1000},
  {"objective_id": 3, "metrics_field_id": 69, "index": 1010},
  {"objective_id": 3, "metrics_field_id": 70, "index": 1020},
  {"objective_id": 16, "metrics_field_id": 16, "index": 0},
  {"objective_id": 16, "metrics_field_id": 28, "index": 10},
  {"objective_id": 16, "metrics_field_id": 27, "index": 30},
  {"objective_id": 16, "metrics_field_id": 33, "index": 40},
  {"objective_id": 16, "metrics_field_id": 40, "index": 50},
  {"objective_id": 16, "metrics_field_id": 47, "index": 60},
  {"objective_id": 16, "metrics_field_id": 49, "index": 70},
  {"objective_id": 16, "metrics_field_id": 50, "index": 80},
]


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

    op.execute(metrics_table.update().values(order_index=0, type_id=7).where(metrics_table.c.order_index == 27))
    op.execute(metrics_table.update().values(order_index=0, type_id=7).where(metrics_table.c.id == 33))

    op.execute(
        metrics_table.delete().values(order_index=metrics_table.c.order_index / 100).where(metrics_table.c.type_id == 3)
    )

    op.execute(objective_metrics_table.delete().where(objective_metrics_table.c.objective_id.in_([3, 16])))
    op.execute(objective_metrics_table.insert().values(*old_objective_metrics))

    op.execute(metrics_table.delete().where(metrics_table.c.type_id == 8))
    op.execute(metrics_type_table.delete().where(metrics_type_table.c.id == 8))

    op.execute(
        metrics_table.update().values(
            description="Sum of landing page views across all platforms."
        ).where(metrics_table.c.id == 27)
    )