"""update_video_xx_watched_metrics

Revision ID: 6ce596b9d0bc
Revises: da91ef40447e
Create Date: 2020-11-03 10:57:56.571251

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '6ce596b9d0bc'
down_revision = 'bdf0bc9b4cf2'
branch_labels = None
depends_on = None


def get_metrics_field_table(bind):
    meta = sa.MetaData(bind=bind)
    meta.reflect()

    return meta.tables["MetricsField"]


def get_format_id(bind, name):
    return bind.execute(f''' SELECT id FROM "MetricsFieldFormat" WHERE name = '{name}' ''').fetchone()[0]


def upgrade():
    bind = op.get_bind()
    metrics_field_table = get_metrics_field_table(bind)
    format_id = get_format_id(bind, "percentage")

    changes = {
        41: {"format_id": format_id, "total_name": "Avg 95% Watched", "description": "Average number of video views where 95% of the video was watched"},
        39: {"format_id": format_id, "total_name": "Avg 50% Watched", "description": "Average number of video views where 50% of the video was watched"},
        64: {"format_id": format_id},
        63: {"format_id": format_id},
        46: {"description": "Cost per video view where 50% of the video was watched"},
        2: {"title": "Leads"}
    }

    for field_id, values in changes.items():
        op.execute(
            metrics_field_table.update().where(metrics_field_table.c.id == field_id).values(**values)
        )


def downgrade():
    bind = op.get_bind()
    metrics_field_table = get_metrics_field_table(bind)
    format_id = get_format_id(bind, "integer")

    changes = {
        41: {"format_id": format_id, "total_name": "Total 95% Watched", "description": "Sum of video views where 95% of the video was watched"},
        39: {"format_id": format_id, "total_name": "Total 50% Watched", "description": "Sum of video views where 50% of the video was watched"},
        64: {"format_id": format_id},
        63: {"format_id": format_id},
        46: {"description": "Cost per video view where 75% of the video was watched"},
        2: {"title": "Leeds"}
    }

    for field_id, values in changes.items():
        op.execute(
            metrics_field_table.update().where(metrics_field_table.c.id == field_id).values(**values)
        )