"""add_taxonomy_v3

Revision ID: 35ac159d7b02
Revises: 643ead0cd31d
Create Date: 2020-08-12 10:56:20.987414

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


# revision identifiers, used by Alembic.


revision = '35ac159d7b02'
down_revision = '643ead0cd31d'
branch_labels = None
depends_on = None

def __insert_widgets(widgets_table):
    for widget in widgets:
        smt = widgets_table.insert().values(id=widget["id"], name=widget["name"])
        op.execute(smt)

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.add_column('CampaignTypeValue', sa.Column('group_number', sa.Integer(), nullable=True))
    bind = op.get_bind()
    meta = sa.MetaData(bind=bind)
    meta.reflect()
    widgets_table = meta.tables["CampaignTypeWidget"]
    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_widgets(widgets_table)
    __insert_campaign_type(campaign_type_category_table)
    for group in taxonomy_v3:
        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["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=root_campaign_type["id"],
                    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"],
                        )
                    )

            op.execute(
                campaign_type_category_value_table.insert().values(
                    category_id=selector["id"],
                    parent_value_id=campaign_type["id"]
                )
            )

            for platform in campaign_type["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")
                    )
                )



def downgrade():
    op.drop_column('CampaignTypeValue', 'group_number')
    bind = op.get_bind()
    bind.execute("""DELETE FROM "Campaign" """)
    bind.execute("""
        TRUNCATE
            "CampaignTypeCategory",
            "CampaignTypeCategoryValue",
            "CampaignTypeCity",
            "CampaignTypeGroup",
            "CampaignTypeValue",
            "CampaignTypeWidget"
        RESTART IDENTITY CASCADE;
    """)