"""taxonomy_v4

Revision ID: ffef3bd223a1
Revises: e2d4ff9617b6
Create Date: 2021-02-03 11:44:12.946442

"""
from alembic import op
import sqlalchemy as sa
from migrations.fixtures.taxonomy_v4 import taxonomy_v4, root_campaign_type

# revision identifiers, used by Alembic.
revision = 'ffef3bd223a1'
down_revision = 'e2d4ff9617b6'
branch_labels = None
depends_on = None

def __insert_campaign_type(campaign_type_category_table):
    op.execute(
        campaign_type_category_table.insert().values(
            id=root_campaign_type["id"],
            name=root_campaign_type["name"],
            category_name=root_campaign_type["name"],
            widget_id=root_campaign_type["widget_id"],
            required=True,
        )
    )

def upgrade():
    op.execute(
        """
            DELETE FROM "Campaign" WHERE external_id IS NULL;
        """
    )
    op.drop_constraint('fk_CampaignType_campaign_id_Campaign', 'CampaignType', type_='foreignkey')
    op.drop_constraint('fk_CampaignType_category_id_CampaignTypeCategory', 'CampaignType', type_='foreignkey')
    op.drop_constraint('fk_CampaignType_value_id_CampaignTypeValue', 'CampaignType', type_='foreignkey')
    op.execute("""
        DELETE FROM "CampaignType" WHERE value_id=205
    """)
    op.execute("""
        TRUNCATE
            "CampaignTypeCategory",
            "CampaignTypeCategoryValue",
            "CampaignTypeGroup",
            "CampaignTypeValue"
        RESTART IDENTITY CASCADE;
    """)

    bind = op.get_bind()
    meta = sa.MetaData(bind=bind)
    meta.reflect()
    campaign_type_category_table = meta.tables["CampaignTypeCategory"]
    campaign_type_group_table = meta.tables["CampaignTypeGroup"]
    campaign_type_value_table = meta.tables["CampaignTypeValue"]
    campaign_type_category_value_table = meta.tables["CampaignTypeCategoryValue"]
    __insert_campaign_type(campaign_type_category_table)
    for group in taxonomy_v4:
        op.execute(
            campaign_type_group_table.insert().values(
                id=group["group_id"],
                name=group["category"]
            )
        )

        for campaign_type in group["campaign_types"]:
            selector = campaign_type.get("selector")
            if selector:
                op.execute(
                    campaign_type_category_table.insert().values(
                        id=selector["id"],
                        name=selector["name"],
                        category_name=group["category"],
                        required=True,
                        widget_id=selector["widget_id"],
                        group_id=group["group_id"],
                        max_length=campaign_type.get("limit"),
                        default_value=campaign_type.get("default")
                    )
                )

            op.execute(
                campaign_type_value_table.insert().values(
                    id=campaign_type["id"],
                    name=campaign_type["name"],
                    category_id=1,
                    group_id=group["group_id"],
                )
            )

            if campaign_type["paid_or_gratis"]:
                gratis = campaign_type["gratis"]
                op.execute(
                    campaign_type_category_table.insert().values(
                        id=gratis["id"],
                        name=gratis["name"],
                        category_name=group["category"],
                        required=True,
                        widget_id=gratis["widget_id"],
                        group_id=group["group_id"],
                        default_value=gratis["values"][1]["id"]
                    )
                )
                op.execute(
                    campaign_type_category_value_table.insert().values(
                        category_id=gratis["id"],
                        parent_value_id=campaign_type["id"]
                    )
                )
                for gratis_val in gratis["values"]:
                    op.execute(
                        campaign_type_value_table.insert().values(
                            id=gratis_val["id"],
                            name=gratis_val["name"],
                            category_id=gratis["id"],
                        )
                    )
            if selector:
                op.execute(
                    campaign_type_category_value_table.insert().values(
                        category_id=selector["id"],
                        parent_value_id=campaign_type["id"]
                    )
                )

                for platform in campaign_type.get("platforms", []):
                    op.execute(
                        campaign_type_value_table.insert().values(
                            id=platform["id"],
                            name=platform["name"],
                            category_id=selector["id"],
                            group_number=platform.get("group_number")
                        )
                    )
    op.create_foreign_key(op.f('fk_CampaignType_campaign_id_Campaign'), 'CampaignType', 'Campaign', ['campaign_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(op.f('fk_CampaignType_category_id_CampaignTypeCategory'), 'CampaignType', 'CampaignTypeCategory', ['category_id'], ['id'])
    op.create_foreign_key(op.f('fk_CampaignType_value_id_CampaignTypeValue'), 'CampaignType', 'CampaignTypeValue', ['value_id'], ['id'])

def downgrade():
    pass