"""multiple_media_plans

Revision ID: efcd8e303ef1
Revises: 8035c2589858
Create Date: 2022-10-20 13:33:49.875707

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy import select
from migrations.utils import get_table

# revision identifiers, used by Alembic.
revision = 'efcd8e303ef1'
down_revision = '8035c2589858'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    project = get_table(bind, "Project")
    project_phase = get_table(bind, "ProjectPhase")

    # Crete MediaPlan table
    media_plan = op.create_table('MediaPlan',
    sa.Column('id', sa.BigInteger(), nullable=False),
    sa.Column('name', sa.String(), nullable=False, server_default="Untitled"),
    sa.Column('project_id', sa.BigInteger(), nullable=False),
    sa.Column('release_name', sa.String(), nullable=True),
    sa.Column('release_type_id', sa.Integer(), nullable=True),
    sa.Column('created_at', sa.TIMESTAMP(timezone=True), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
    sa.Column('create_user_id', sa.Integer(), nullable=True),
    sa.Column('order', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['create_user_id'], ['User.id'], name=op.f('fk_MediaPlan_create_user_id_User')),
    sa.ForeignKeyConstraint(['project_id'], ['Project.id'], name=op.f('fk_MediaPlan_project_id_Project'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['release_type_id'], ['ReleaseType.id'], name=op.f('fk_MediaPlan_release_type_id_ReleaseType')),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_MediaPlan')),
    sa.UniqueConstraint('project_id', 'name', name='uq_MediaPlan_project_id_name')
    )

    # Crete MediaPlanPhase table
    media_plan_phase = op.create_table('MediaPlanPhase',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.Column('order', sa.Integer(), nullable=True),
    sa.Column('objective_text', sa.String(), nullable=True),
    sa.Column('media_plan_id', sa.BigInteger(), nullable=False),
    sa.ForeignKeyConstraint(['media_plan_id'], ['MediaPlan.id'], name=op.f('fk_MediaPlanPhase_media_plan_id_MediaPlan'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_MediaPlanPhase')),
    sa.UniqueConstraint('media_plan_id', 'order', deferrable='True', initially='deferred', name='uq_MediaPlanPhase_media_plan_id_order'),
    )
    op.create_index(op.f('ix_MediaPlanPhase_media_plan_id'), 'MediaPlanPhase', ['media_plan_id'], unique=False)

    # Create media plans for each project
    projects_data = bind.execute(select([project.c.id, project.c.release_name, project.c.release_type_id])).fetchall()

    data_for_media_plan = []
    for project_id, release_name, release_type_id in projects_data:
        data_for_media_plan.append(
            {
                "project_id": project_id,
                "release_name": release_name,
                "release_type_id": release_type_id,
                "name": "Untitled",
                "order": 0,
            }
        )
    op.bulk_insert(media_plan, data_for_media_plan)

    phases_data = bind.execute(
        select([
            project_phase.c.id,
            project_phase.c.name,
            project_phase.c.order,
            media_plan.c.id.label("media_plan_id"),
        ])
        .select_from(
            project_phase
            .join(media_plan, media_plan.c.project_id == project_phase.c.project_id)
        )
    ).fetchall()

    data_for_media_plan_phases = []
    for phase_id, phase_name, phase_order, media_plan_id in phases_data:
        data_for_media_plan_phases.append(
            {
                "id": phase_id,
                "name": phase_name,
                "order": phase_order,
                "media_plan_id": media_plan_id
             }
        )
    op.bulk_insert(media_plan_phase, data_for_media_plan_phases)

    if phases_data:
        max_phase_id = max([phase_id for phase_id, _, _, _ in phases_data])
        bind.execute(f'''ALTER SEQUENCE "MediaPlanPhase_id_seq" RESTART WITH {max_phase_id + 1}''')

    op.add_column('Campaign', sa.Column('media_plan_phase_id', sa.BigInteger(), nullable=True))
    op.create_index(op.f('ix_Campaign_media_plan_phase_id'), 'Campaign', ['media_plan_phase_id'], unique=False)
    op.create_foreign_key(op.f('fk_Campaign_media_plan_phase_id_MediaPlanPhase'), 'Campaign', 'MediaPlanPhase', ['media_plan_phase_id'], ['id'], ondelete='RESTRICT')

    op.create_unique_constraint(
        'uq_Campaign_media_plan_phase_id_order_in_phase',
        'Campaign',
        ['media_plan_phase_id', 'order_in_phase'],
        deferrable='True',
        initially='deferred'
    )

    bind.execute('''
            UPDATE "Campaign"
            SET media_plan_phase_id=project_phase_id
            WHERE project_phase_id is not NULL
        ''')

    op.drop_table('ProjectPhaseProductTrack')
    op.drop_table('ProjectPhasePlaylist')

    op.add_column('ApprovalRequest', sa.Column('media_plan_id', sa.BigInteger(), nullable=True))
    op.drop_constraint('fk_ApprovalRequest_project_id_Project', 'ApprovalRequest', type_='foreignkey')
    op.create_foreign_key(op.f('fk_ApprovalRequest_media_plan_id_MediaPlan'), 'ApprovalRequest', 'MediaPlan', ['media_plan_id'], ['id'], ondelete='CASCADE')

    approval_request = get_table(bind, "ApprovalRequest")

    media_plan_proj_data = bind.execute(select([media_plan.c.id, media_plan.c.project_id])).fetchall()
    for media_plan_id, project_id in media_plan_proj_data:
        op.execute(
            approval_request.update()
            .where(approval_request.c.project_id==project_id)
            .values(media_plan_id=media_plan_id)
        )

    op.drop_column('ApprovalRequest', 'project_id')
    op.drop_index('ix_Campaign_project_phase_id', table_name='Campaign')
    op.drop_constraint('uq_Campaign_project_phase_id_order_in_phase', 'Campaign', type_='unique')

    op.drop_constraint('fk_CampaignPlatforms_platform_group_id_CampaignPlatformGroups', 'CampaignPlatforms', type_='foreignkey')
    op.create_foreign_key(op.f('fk_CampaignPlatforms_platform_group_id_CampaignPlatformGroups'), 'CampaignPlatforms', 'CampaignPlatformGroups', ['platform_group_id'], ['id'], ondelete='SET NULL')

    op.drop_constraint('fk_Project_release_type_id_ReleaseType', 'Project', type_='foreignkey')
    op.drop_column('Project', 'release_name')
    op.drop_column('Project', 'release_type_id')


def downgrade():
    op.add_column('Project', sa.Column('release_type_id', sa.INTEGER(), autoincrement=False, nullable=True))
    op.add_column('Project', sa.Column('release_name', sa.VARCHAR(), autoincrement=False, nullable=True))
    op.create_foreign_key('fk_Project_release_type_id_ReleaseType', 'Project', 'ReleaseType', ['release_type_id'], ['id'])
    op.drop_constraint(op.f('fk_CampaignPlatforms_platform_group_id_CampaignPlatformGroups'), 'CampaignPlatforms', type_='foreignkey')
    op.create_foreign_key('fk_CampaignPlatforms_platform_group_id_CampaignPlatformGroups', 'CampaignPlatforms', 'CampaignPlatformGroups', ['platform_group_id'], ['id'])
    op.add_column('Campaign', sa.Column('project_phase_id', sa.INTEGER(), autoincrement=False, nullable=True))
    op.drop_constraint(op.f('fk_Campaign_media_plan_phase_id_MediaPlanPhase'), 'Campaign', type_='foreignkey')
    op.create_foreign_key('fk_Campaign_project_phase_id_ProjectPhase', 'Campaign', 'ProjectPhase', ['project_phase_id'], ['id'], ondelete='RESTRICT')
    op.drop_constraint('uq_Campaign_media_plan_phase_id_order_in_phase', 'Campaign', type_='unique')
    op.drop_index(op.f('ix_Campaign_media_plan_phase_id'), table_name='Campaign')
    op.create_unique_constraint('uq_Campaign_project_phase_id_order_in_phase', 'Campaign', ['project_phase_id', 'order_in_phase'])

    op.create_index('ix_Campaign_project_phase_id', 'Campaign', ['project_phase_id'], unique=False)

    op.drop_column('Campaign', 'media_plan_phase_id')
    op.add_column('ApprovalRequest', sa.Column('project_id', sa.BIGINT(), autoincrement=False, nullable=True))
    op.drop_constraint(op.f('fk_ApprovalRequest_media_plan_id_MediaPlan'), 'ApprovalRequest', type_='foreignkey')
    op.create_foreign_key('fk_ApprovalRequest_project_id_Project', 'ApprovalRequest', 'Project', ['project_id'], ['id'], ondelete='CASCADE')
    op.drop_column('ApprovalRequest', 'media_plan_id')
    op.create_table('ProjectPhasePlaylist',
    sa.Column('phase_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('playlist_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['phase_id'], ['ProjectPhase.id'], name='fk_ProjectPhasePlaylist_phase_id_ProjectPhase', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['playlist_id'], ['Playlist.id'], name='fk_ProjectPhasePlaylist_playlist_id_Playlist', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('phase_id', 'playlist_id', name='pk_ProjectPhasePlaylist')
    )

    op.create_table('ProjectPhaseProductTrack',
    sa.Column('phase_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.Column('product_track_id', sa.INTEGER(), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['phase_id'], ['ProjectPhase.id'], name='fk_ProjectPhaseProductTrack_phase_id_ProjectPhase', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['product_track_id'], ['ProductTrack.id'], name='fk_ProjectPhaseProductTrack_product_track_id_ProductTrack', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('phase_id', 'product_track_id', name='pk_ProjectPhaseProductTrack')
    )

    op.drop_index(op.f('ix_MediaPlanPhase_media_plan_id'), table_name='MediaPlanPhase')
    op.drop_table('MediaPlanPhase')
    op.drop_table('MediaPlan')