"""add_metrics_fields

Revision ID: e8ee3046e854
Revises: c17a50352e5c
Create Date: 2020-09-24 07:40:03.910325

"""
from alembic import op
import sqlalchemy as sa
from migrations.fixtures.metrics_fields import all_fields, fields_by_objective



# revision identifiers, used by Alembic.
revision = 'e8ee3046e854'
down_revision = 'c17a50352e5c'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('MetricsFieldFormat',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_MetricsFieldFormat'))
    )
    op.create_table('MetricsFieldType',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_MetricsFieldType'))
    )
    op.create_table('MetricsField',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('type_id', sa.Integer(), nullable=False),
    sa.Column('format_id', sa.Integer(), nullable=False),
    sa.Column('objective_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['format_id'], ['MetricsFieldFormat.id'], name=op.f('fk_MetricsField_format_id_MetricsFieldFormat'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['type_id'], ['MetricsFieldType.id'], name=op.f('fk_MetricsField_type_id_MetricsFieldType'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['objective_id'], ['CampaignObjective.id'], name=op.f('fk_MetricsField_objective_id_CampaignObjective'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_MetricsField'))
    )
    op.create_table('UserMetricsField',
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('metric_field_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['user_id'], ['User.id'], name=op.f('fk_UserMetricsField_user_id_User'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['metric_field_id'], ['MetricsField.id'], name=op.f('fk_UserMetricsField_metric_field_id_MetricsField'), ondelete='CASCADE'),
    )

    bind = op.get_bind()

    all_formats = set([item["format"] for item in all_fields])

    for format in all_formats:
        bind.execute(f''' INSERT INTO "MetricsFieldFormat" (name) VALUES ('{format}')''')

    all_types = set([item["type"] for item in all_fields])

    for type in all_types:
        bind.execute(f''' INSERT INTO "MetricsFieldType" (name) VALUES ('{type}')''')

    for field in all_fields:
        name = field["name"]
        type_id = bind.execute(f''' SELECT id FROM "MetricsFieldType" WHERE name = '{field["type"]}' ''').fetchone()[0]
        format_id = bind.execute(f''' SELECT id FROM "MetricsFieldFormat" WHERE name = '{field["format"]}' ''').fetchone()[0]
        bind.execute(f''' INSERT INTO "MetricsField" (name, type_id, format_id) VALUES ('{name}', {type_id}, {format_id})''')

    for item in fields_by_objective:
        objective_id = bind.execute(f''' SELECT id FROM "CampaignObjective" WHERE name = '{item["objective"]}' ''').fetchone()[0]
        for field in item["metrics"]:
            bind.execute(f''' UPDATE "MetricsField" SET objective_id={objective_id} WHERE name = '{field}' ''')

    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('UserMetricsField')
    op.drop_table('MetricsField')
    op.drop_table('MetricsFieldType')
    op.drop_table('MetricsFieldFormat')
    # ### end Alembic commands ###