"""refactor_metrics_format

Revision ID: 411af9efd200
Revises: 4a52a3db8fc7
Create Date: 2021-08-05 09:13:37.843390

"""
from alembic import op
import sqlalchemy as sa
from migrations.fixtures.metrics_format_info import metrics_field, metrics_field_format, metrics_field_type


# revision identifiers, used by Alembic.
revision = '411af9efd200'
down_revision = '4a52a3db8fc7'
branch_labels = None
depends_on = None


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

    for metrics_format in metrics_field_format:
        op.execute(
            metrics_format_table.update().values(
                name=metrics_format["name"]
            ).where(metrics_format_table.c.id == metrics_format["id"])
        )

    for metrics_type in metrics_field_type:
        op.execute(
            metrics_type_table.update().values(
                name=metrics_type["name"]
            ).where(metrics_type_table.c.id == metrics_type["id"])
        )

    for metrics in metrics_field:
        format_id = bind.execute(f''' SELECT id FROM "MetricsFieldFormat" WHERE name = '{metrics["format_name"]}' ''').fetchone()[0]
        type_id = bind.execute(f''' SELECT id FROM "MetricsFieldType" WHERE name = '{metrics["field_type"]}' ''').fetchone()[0]

        op.execute(
            metrics_table.update().values(
              format_id=format_id,
              type_id=type_id
            ).where(metrics_table.c.id == metrics["field_id"])
        )

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###