"""remove_gras_project

Revision ID: fc9a4aa5060a
Revises: 1acc5379edb5
Create Date: 2021-03-31 09:15:10.513682

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
from sqlalchemy import select

revision = 'fc9a4aa5060a'
down_revision = '4d2379fbeff5'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()

    __modify_projects_table()

    meta = sa.MetaData(bind=bind)
    meta.reflect()
    gras_projects_table = meta.tables["GRASProject"]
    project_campaigns_table = meta.tables["GRASProjectCampaign"]
    projects_table = meta.tables["Project"]
    prs_budget_table = meta.tables["PRSProjectBudget"]
    prs_po_table = meta.tables["PRSPurchaseOrder"]
    product_families_table = meta.tables["GRASProjectProductFamily"]
    history_table = meta.tables["ProjectHistoryItem"]
    recent_search_table = meta.tables["RecentSearchItem"]

    __convert_gras_projects_to_projects()
    __delete_projects_with_no_prs_code_if_any(projects_table)

    __make_projects_fields_required()

    existing_project_campaigns = __fetch_all_projects_campaigns(
        gras_projects_table, project_campaigns_table, projects_table
    )

    bind.execute(project_campaigns_table.delete())

    __modify_project_camapigns_table()

    existing_project_budgets = __fetch_projects_budgets(gras_projects_table, prs_budget_table, projects_table)
    existing_project_pos = __fetch_projects_purchase_orders(gras_projects_table, projects_table, prs_po_table)
    existing_products = __fetch_product_families(gras_projects_table, product_families_table, projects_table)
    existing_history_items = __fetch_history_items(gras_projects_table, history_table, projects_table)
    existing_recent_search_items = __fetch_recent_search_items(gras_projects_table, recent_search_table, projects_table)

    __modify_budgets_table()
    __modify_purchase_orders_table()
    __modify_product_families_table()
    __modify_projects_history_items_table()
    __modify_recent_search_items_table()

    meta = sa.MetaData(bind=bind)
    meta.reflect()

    project_campaigns_table = meta.tables["ProjectCampaign"]
    prs_budget_table = meta.tables["PRSProjectBudget"]
    prs_po_table = meta.tables["PRSPurchaseOrder"]
    product_families_table = meta.tables["GRASProjectProductFamily"]
    history_table = meta.tables["ProjectHistoryItem"]
    recent_search_table = meta.tables["RecentSearchItem"]

    for project_id, campaign_id, status in existing_project_campaigns:
        insert_statement = project_campaigns_table.insert().values(
            project_id=project_id, campaign_id=campaign_id, status=status
        )
        bind.execute(insert_statement)

    for budget_id, project_id in existing_project_budgets:
        update_statement = (
            prs_budget_table.update().values(project_id=project_id).where(prs_budget_table.c.id == budget_id)
        )
        bind.execute(update_statement)

    for po_id, project_id in existing_project_pos:
        update_statement = (
            prs_po_table.update().values(project_id=project_id).where(prs_po_table.c.id == po_id)
        )
        bind.execute(update_statement)

    for product_id, project_id in existing_products:
        update_statement = (
            product_families_table.update()
            .values(project_id=project_id)
            .where(product_families_table.c.id == product_id)
        )
        bind.execute(update_statement)

    for history_item_id, project_id in existing_history_items:
        update_statement = (
            history_table.update()
            .values(project_id=project_id)
            .where(history_table.c.id == history_item_id)
        )
        bind.execute(update_statement)

    for recent_item_id, project_id in existing_recent_search_items:
        update_statement = (
            recent_search_table.update()
            .values(project_id=project_id)
            .where(recent_search_table.c.id == recent_item_id)
        )
        bind.execute(update_statement)

    op.alter_column('PRSProjectBudget', 'project_id', nullable=False)
    op.alter_column('PRSPurchaseOrder', 'project_id', nullable=False)
    op.alter_column('GRASProjectProductFamily', 'project_id', nullable=False)


def __make_projects_fields_required():
    op.alter_column('Project', 'allocation', server_default='0', nullable=False)
    op.alter_column('Project', 'budget', server_default='0', nullable=False)
    op.alter_column('Project', 'is_protected', server_default='FALSE', nullable=False)
    op.alter_column('Project', 'prs_project_code', nullable=False)
    op.alter_column('Project', 'prs_title', nullable=False)
    op.alter_column('Project', 'prs_updated_at', nullable=False)


def __modify_projects_table():
    op.alter_column('Project', 'title', new_column_name='gras_title')
    op.alter_column('Project', 'gras_title', nullable=True)
    op.alter_column('Project', 'planned_budget', new_column_name='budget')
    op.alter_column('Project', 'end_date', existing_type=sa.DATE(), nullable=True)
    op.alter_column('Project', 'name', existing_type=sa.VARCHAR(), nullable=True)
    op.alter_column('Project', 'owner_id', existing_type=sa.INTEGER(), nullable=True, server_default=None)
    op.add_column('Project', sa.Column('initial_start_date', sa.DATE(), nullable=True))
    op.add_column('Project', sa.Column('allocation', sa.Numeric(precision=12, scale=2), nullable=True))  # NOT Null
    op.add_column('Project', sa.Column('is_protected', sa.Boolean(), server_default='FALSE', nullable=True))  # NOT Null
    op.add_column('Project', sa.Column('prs_project_code', sa.String(), nullable=True))  # NOT Null
    op.add_column('Project', sa.Column('gras_project_code', sa.String(), nullable=True))  # NOT Null
    op.add_column('Project', sa.Column('prs_title', sa.String(), nullable=True))  # NOT Null
    op.add_column('Project', sa.Column('prs_updated_at', sa.TIMESTAMP(timezone=True), nullable=True))  # NOT Null
    op.drop_column('Project', 'currency_id')
    op.create_unique_constraint(
        'uq_Project_prs_project_code', 'Project', ['prs_project_code']
    )


def __modify_project_camapigns_table():
    op.drop_index('ix_GRASProjectCampaign_gras_project_id', table_name='GRASProjectCampaign')
    op.drop_constraint('pk_GRASProjectCampaign', 'GRASProjectCampaign', type_='primary')
    op.drop_constraint('fk_GRASProjectCampaign_campaign_id_Campaign', 'GRASProjectCampaign', type_='foreignkey')
    op.drop_constraint('uq_GRASProjectCampaign_gras_project_id_campaign_id', 'GRASProjectCampaign', type_='unique')
    op.drop_constraint('fk_GRASProjectCampaign_gras_project_id_GRASProject', 'GRASProjectCampaign', type_='foreignkey')
    op.rename_table('GRASProjectCampaign', 'ProjectCampaign')
    op.alter_column('ProjectCampaign', 'gras_project_id', new_column_name='project_id', nullable=False)
    op.create_foreign_key(
        op.f('fk_ProjectCampaign_campaign_id_Campaign'), 'ProjectCampaign', 'Campaign', ['campaign_id'], ['id']
    )
    op.create_foreign_key(
        op.f('fk_ProjectCampaign_project_id_Project'), 'ProjectCampaign', 'Project', ['project_id'], ['id']
    )
    op.create_unique_constraint(
        'uq_ProjectCampaign_project_id_campaign_id', 'ProjectCampaign', ['project_id', 'campaign_id']
    )
    op.create_primary_key('pk_ProjectCampaign', 'ProjectCampaign', ['id'])
    op.create_index(op.f('ix_ProjectCampaign_project_id'), 'ProjectCampaign', ['project_id'], unique=False)


def __modify_product_families_table():
    op.drop_column('GRASProjectProductFamily', 'gras_project_id')
    op.add_column('GRASProjectProductFamily', sa.Column('project_id', sa.BigInteger(), nullable=True))
    op.create_foreign_key(
        op.f('fk_GRASProjectProductFamily_project_id_Project'),
        'GRASProjectProductFamily',
        'Project',
        ['project_id'], ['id']
    )
    op.create_unique_constraint(
        'uq_GRASProjectProductFamily_project_id_product_family_id',
        'GRASProjectProductFamily',
        ['project_id', 'product_family_id']
    )


def __modify_recent_search_items_table():
    op.drop_column('RecentSearchItem', 'gras_project_id')
    op.create_check_constraint(
        op.f("ck_RecentSearchItem_type"),
        "RecentSearchItem",
        f"(artist_id is not null AND type = 0) OR (project_id is not null AND type = 1) OR (campaign_id is not null AND type = 2)")


def __modify_projects_history_items_table():
    op.drop_column('ProjectHistoryItem', 'gras_project_id')


def __modify_purchase_orders_table():
    op.drop_column('PRSPurchaseOrder', 'gras_project_id')
    op.add_column('PRSPurchaseOrder', sa.Column('project_id', sa.BigInteger(), nullable=True))
    op.create_foreign_key(
        op.f('fk_PRSPurchaseOrder_project_id_Project'), 'PRSPurchaseOrder', 'Project', ['project_id'], ['id']
    )
    op.create_unique_constraint(
        'uq_PRSPurchaseOrder_po_number_project_id',
        'PRSPurchaseOrder',
        ["po_number", "project_id", "group_id", "category_id"]
    )


def __modify_budgets_table():
    op.drop_column('PRSProjectBudget', 'gras_project_id')
    op.drop_column('PRSProjectBudget', 'prs_project_code')
    op.add_column('PRSProjectBudget', sa.Column('project_id', sa.BigInteger(), nullable=True))
    op.create_foreign_key(
        op.f('fk_PRSProjectBudget_project_id_Project'), 'PRSProjectBudget', 'Project', ['project_id'], ['id']
    )


def __fetch_recent_search_items(gras_projects_table, recent_search_items_table, projects_table):
    bind = op.get_bind()
    recent_search_items_statement = select(
        [
            recent_search_items_table.c.id,
            projects_table.c.id
        ]
    ).select_from(
        recent_search_items_table
        .join(gras_projects_table, recent_search_items_table.c.gras_project_id == gras_projects_table.c.id)
        .join(projects_table, gras_projects_table.c.prs_project_code == projects_table.c.prs_project_code)
    )
    existing_recent_search_items = bind.execute(recent_search_items_statement).fetchall()
    return existing_recent_search_items


def __fetch_history_items(gras_projects_table, history_items_table, projects_table):
    bind = op.get_bind()
    history_items_statement = select(
        [
            history_items_table.c.id,
            projects_table.c.id
        ]
    ).select_from(
        history_items_table
        .join(gras_projects_table, history_items_table.c.gras_project_id == gras_projects_table.c.id)
        .join(projects_table, gras_projects_table.c.prs_project_code == projects_table.c.prs_project_code)
    )
    existing_history_items = bind.execute(history_items_statement).fetchall()
    return existing_history_items


def __fetch_product_families(gras_projects_table, product_families_table, projects_table):
    bind = op.get_bind()
    project_products_statement = select(
        [
            product_families_table.c.id,
            projects_table.c.id
        ]
    ).select_from(
        product_families_table
        .join(gras_projects_table, product_families_table.c.gras_project_id == gras_projects_table.c.id)
        .join(projects_table, gras_projects_table.c.prs_project_code == projects_table.c.prs_project_code)
    )
    existing_products = bind.execute(project_products_statement).fetchall()
    return existing_products


def __fetch_projects_purchase_orders(gras_projects_table, projects_table, prs_po_table):
    bind = op.get_bind()
    project_po_statement = select(
        [
            prs_po_table.c.id,
            projects_table.c.id
        ]
    ).select_from(
        prs_po_table
        .join(gras_projects_table, prs_po_table.c.gras_project_id == gras_projects_table.c.id)
        .join(projects_table, gras_projects_table.c.prs_project_code == projects_table.c.prs_project_code)
    )
    existing_project_pos = bind.execute(project_po_statement).fetchall()
    return existing_project_pos


def __fetch_projects_budgets(gras_projects_table, prs_budget_table, projects_table):
    bind = op.get_bind()
    project_budgets_statement = select(
        [
            prs_budget_table.c.id,
            projects_table.c.id
        ]
    ).select_from(
        prs_budget_table
        .join(gras_projects_table, prs_budget_table.c.gras_project_id == gras_projects_table.c.id)
        .join(projects_table, gras_projects_table.c.prs_project_code == projects_table.c.prs_project_code)
    )
    existing_project_budgets = bind.execute(project_budgets_statement).fetchall()
    return existing_project_budgets


def __fetch_all_projects_campaigns(gras_projects_table, project_campaigns_table, projects_table):
    bind = op.get_bind()
    project_campaigns_statement = select(
        [
            projects_table.c.id,
            project_campaigns_table.c.campaign_id,
            project_campaigns_table.c.status
        ]
    ).select_from(
        project_campaigns_table
        .join(gras_projects_table, project_campaigns_table.c.gras_project_id == gras_projects_table.c.id)
        .join(projects_table, gras_projects_table.c.prs_project_code == projects_table.c.prs_project_code)
    )
    existing_project_campaigns = bind.execute(project_campaigns_statement).fetchall()
    return existing_project_campaigns


def __delete_projects_with_no_prs_code_if_any(projects_table):
    bind = op.get_bind()
    bind.execute(projects_table.delete().where(projects_table.c.prs_project_code.is_(None)))


def __convert_gras_projects_to_projects():
    bind = op.get_bind()
    meta = sa.MetaData(bind=bind)
    meta.reflect()
    gras_projects_table = meta.tables["GRASProject"]
    projects_table = meta.tables["Project"]
    gras_project_artist_table = meta.tables["GRASProjectArtist"]
    project_targets_table = meta.tables["ProjectTargetItem"]

    prs_user = __get_prs_user()

    for project in bind.execute(select([gras_projects_table])).fetchall():
        claimed_project = __claimed_project_for_gras_project(project.id, projects_table)

        if project.is_claimed and claimed_project:
            update_project_statement = __update_project_statement(claimed_project.id, project, projects_table)
            bind.execute(update_project_statement)
        else:
            gras_artists = __fetch_artists_for_project(gras_project_artist_table, gras_projects_table, project.id)
            insert_project_statement = __insert_project_statement(project, projects_table, prs_user)
            project_id_result = bind.execute(insert_project_statement).fetchone()
            for artist in gras_artists:
                bind.execute(__insert_project_artist_statement(artist, project_id_result, project_targets_table))


def __insert_project_artist_statement(artist, project_id_result, project_targets_table):
    insert_project_artist_statement = project_targets_table.insert().values(
        entity_id=artist.artist_id,
        project_id=project_id_result[0],
        entity_type=artist.type,
        add_type=0 if artist.source == 0 else 2,
    )
    return insert_project_artist_statement


def __insert_project_statement(project, projects_table, prs_user):
    insert_project_statement = projects_table.insert().values(
        allocation=project.allocation or 0,
        budget=project.budget or 0,
        is_protected=project.is_protected,
        gras_project_code=project.external_id,
        gras_title=project.title,
        prs_project_code=project.prs_project_code,
        prs_title=project.prs_title,
        prs_updated_at=project.prs_updated_at,
        label_id=project.label_id,
        initial_start_date=project.start_date,
        end_date=project.end_date,
        is_deleted=project.is_deleted,
        created_at=project.created_at,
        updated_at=project.updated_at,
        create_user_id=prs_user.id,
        edit_user_id=prs_user.id,
    ).returning(projects_table.c.id)
    return insert_project_statement


def __fetch_artists_for_project(gras_project_artist_table, gras_projects_table, project_id: int):
    bind = op.get_bind()
    return bind.execute(
        select([gras_project_artist_table])
        .select_from(
            gras_project_artist_table
            .join(gras_projects_table, gras_projects_table.c.id == gras_project_artist_table.c.gras_project_id)
        )
        .where(gras_projects_table.c.id == project_id)
    ).fetchall()


def __update_project_statement(claimed_project_id: int, project, projects_table):
    update_project_statement = projects_table.update().values(
        allocation=project.allocation or 0,
        budget=project.budget or 0,
        is_protected=project.is_protected,
        prs_project_code=project.prs_project_code,
        gras_project_code=project.external_id,
        prs_title=project.prs_title,
        prs_updated_at=project.prs_updated_at,
        gras_title=project.title,
        initial_start_date=project.start_date
    ).where(projects_table.c.id == claimed_project_id)
    return update_project_statement


def __claimed_project_for_gras_project(project_id: int, projects_table):
    bind = op.get_bind()
    return bind.execute(select([projects_table.c.id]).where(projects_table.c.gras_project_id == project_id)).fetchone()


def __get_prs_user():
    bind = op.get_bind()
    meta = sa.MetaData(bind=bind)
    meta.reflect()
    users_table = meta.tables["User"]
    return bind.execute(select([users_table.c.id]).where(users_table.c.email == 'prs@prs.com')).fetchone()


def downgrade():
    pass