"""cleanup_extra_metrics_fields

Revision ID: 764bf0e5188d
Revises: 132a25208482
Create Date: 2020-11-13 09:08:44.762257

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '764bf0e5188d'
down_revision = '132a25208482'
branch_labels = None
depends_on = None

metrics_fields_data = [
  {
    "id": 8,
    "name": "Sales",
    "type_id": 5,
    "format_id": 3,
    "objective_id": 6,
    "title": "Sales",
    "total_name": "Total Sales",
    "description": "Sum of sales."
  },
  {
    "id": 9,
    "name": "Streams",
    "type_id": 2,
    "format_id": 3,
    "objective_id": 7,
    "title": "Streams",
    "total_name": "Total Streams",
    "description": "Sum of streams across Spotify, Apple Music, and Amazon Music."
  },
  {
    "id": 11,
    "name": "CP Stream",
    "type_id": 2,
    "format_id": 4,
    "objective_id": 7,
    "title": "CP Stream",
    "total_name": "Avg CP Stream",
    "description": "Cost per stream across all DSPs."
  },
  {
    "id": 10,
    "name": "CP Sale",
    "type_id": 5,
    "format_id": 4,
    "objective_id": 6,
    "title": "Sale",
    "subtitle": "CP",
    "total_name": "Avg CP Sale",
    "description": "Cost per sale"
  },
  {
    "id": 53,
    "name": "Lean Forward Streams",
    "type_id": 2,
    "format_id": 3,
  },
  {
    "id": 54,
    "name": "Lean Back Streams",
    "type_id": 2,
    "format_id": 3,
  },
  {
    "id": 32,
    "name": "CP Video View",
    "type_id": 6,
    "format_id": 4,
    "objective_id": 16,
    "title": "Video View",
    "subtitle": "CP",
    "total_name": "Avg CP Video View",
    "description": "Cost per video view, defined by each platform"
  },
  {
    "id": 48,
    "name": "CP Video 95% Watched",
    "type_id": 6,
    "format_id": 4,
    "objective_id": 16,
    "title": "95% Watched",
    "subtitle": "CP Video",
    "total_name": "Avg CP 95% Watched",
    "description": "Cost per video view where 95% of the video was watched"
  },
  {
    "id": 46,
    "name": "CP Video 50% Watched",
    "type_id": 6,
    "format_id": 4,
    "objective_id": 16,
    "title": "50% Watched",
    "subtitle": "CP Video",
    "total_name": "Avg CP 50% Watched",
    "description": "Cost per video view where 50% of the video was watched"
  }
]

objective_metrics_data = [
  {
    "id": 29,
    "objective_id": 7,
    "metrics_field_id": 8,
    "index": 1
  },
  {
    "id": 38,
    "objective_id": 8,
    "metrics_field_id": 9,
    "index": 1
  },
  {
    "id": 20,
    "objective_id": 6,
    "metrics_field_id": 9,
    "index": 1
  },
  {
    "id": 39,
    "objective_id": 8,
    "metrics_field_id": 11,
    "index": 2
  },
  {
    "id": 21,
    "objective_id": 6,
    "metrics_field_id": 11,
    "index": 2
  },
  {
    "id": 30,
    "objective_id": 7,
    "metrics_field_id": 10,
    "index": 2
  },
  {
    "id": 110,
    "objective_id": 17,
    "metrics_field_id": 32,
    "index": 2
  },
  {
    "id": 112,
    "objective_id": 17,
    "metrics_field_id": 46,
    "index": 4
  },
  {
    "id": 94,
    "objective_id": 15,
    "metrics_field_id": 32,
    "index": 4
  },
  {
    "id": 96,
    "objective_id": 15,
    "metrics_field_id": 46,
    "index": 6
  },
  {
    "id": 114,
    "objective_id": 17,
    "metrics_field_id": 48,
    "index": 6
  }
]


def get_table(bind, table_name: str):
    meta = sa.MetaData(bind=bind)
    meta.reflect()
    return meta.tables[table_name]


def upgrade():
    bind = op.get_bind()
    metrics_field_table = get_table(bind, "MetricsField")

    for field in metrics_fields_data:
        op.execute(metrics_field_table.delete().where(metrics_field_table.c.id == field["id"]))


def downgrade():
    bind = op.get_bind()
    objective_metrics_table = get_table(bind, "ObjectiveMetricsField")
    metrics_field_table = get_table(bind, "MetricsField")

    for field in metrics_fields_data:
        op.execute(metrics_field_table.insert().values(**field))

    for item in objective_metrics_data:
        op.execute(objective_metrics_table.insert().values(**item))