"""add_relation_between_objective_and_metric_field

Revision ID: ef11ffd133c7
Revises: 51e5d89aabb3
Create Date: 2020-10-16 11:16:22.803385

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'ef11ffd133c7'
down_revision = '51e5d89aabb3'
branch_labels = None
depends_on = None
objective_metrics_relation = [
    {"objective_id": 3, "fields": [16, 12, 13, 6, 51, 27, 33, 49, 50]},  # Conversion +
    {"objective_id": 4, "fields": [16, 2,  4,  6, 51, 36, 44, 49, 50]},  # Leads

    {"objective_id": 6, "fields": [16, 9, 11, 12, 13, 14, 15, 49, 50]},  # Listeners
    {"objective_id": 7, "fields": [16, 8, 10, 12, 13, 14, 15, 49, 50]},  # Sales
    {"objective_id": 8, "fields": [16, 9, 11, 12, 13, 14, 15, 49, 50]},  # Streams


    {"objective_id": 9, "fields": [6, 35, 43, 22, 30, 40, 47, 49, 50]},  # Brand Aw
    {"objective_id": 10, "fields": [16, 17, 19, 23, 28, 21, 42, 49, 50]},  # Clicks

    {"objective_id": 11, "fields": [16, 40, 47, 22, 30, 35, 43, 49, 50]},  # Impressions

    {"objective_id": 13, "fields": [16, 22, 47, 40, 30, 37, 49, 50]},  # Reach
    {"objective_id": 14, "fields": [16, 35, 43, 25, 31, 38, 45, 49, 50]},  # Reactions

    {"objective_id": 15, "fields": [16, 18, 20, 26, 32, 39, 46, 49, 50]},  # Thruplays
    {"objective_id": 16, "fields": [16, 28, 34, 27, 33, 40, 47, 49, 50]},  # Traffic
    {"objective_id": 17, "fields": [16, 26, 32, 39, 46, 41, 48, 49, 50]},  # Video Views
]

def upgrade():
    op.create_table('ObjectiveMetricsField',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('objective_id', sa.Integer(), nullable=False),
        sa.Column('metrics_field_id', sa.Integer(), nullable=False),
        sa.Column('index', sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(['metrics_field_id'], ['MetricsField.id'], name=op.f('fk_ObjectiveMetricsField_metrics_field_id_MetricsField'), ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['objective_id'], ['CampaignObjective.id'], name=op.f('fk_ObjectiveMetricsField_objective_id_CampaignObjective'), ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id', name=op.f('pk_ObjectiveMetricsField'))
    )
    bind = op.get_bind()
    meta = sa.MetaData(bind=bind)
    meta.reflect()
    objective_metrics_field_table = meta.tables["ObjectiveMetricsField"]
    for objective in objective_metrics_relation:
        for i, field_id in enumerate(objective["fields"]):
            op.execute(
                objective_metrics_field_table.insert().values(
                    objective_id=objective["objective_id"],
                    metrics_field_id=field_id,
                    index=i
                )
            )


def downgrade():
    op.drop_table('ObjectiveMetricsField')