"""fix_history_events

Revision ID: 1487dcffbdb4
Revises: 260e1763d6a0
Create Date: 2021-04-08 14:26:02.014532

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '1487dcffbdb4'
down_revision = '260e1763d6a0'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    bind.execute('''
        WITH campaign_projects as (
            SELECT DISTINCT C.id, PC.project_id FROM "Campaign" C
                JOIN "ProjectCampaign" PC ON PC.campaign_id = C.id AND PC.status = 1
                JOIN "Project" P on PC.project_id = P.id
        )

        UPDATE "ProjectHistoryItem"
            SET project_id = campaign_projects.project_id
        FROM campaign_projects
        WHERE campaign_id = campaign_projects.id;

        UPDATE "Campaign"
            SET project_id = PC.project_id
        FROM "ProjectCampaign" PC
        WHERE PC.campaign_id = "Campaign".id AND PC.status = 1;
    ''')
    
    op.drop_constraint('fk_ProjectProductFamily_project_id_ProductFamily', 'ProjectProductFamily', type_='foreignkey')
    op.drop_constraint('fk_ProjectProductFamily_project_id_Project', 'ProjectProductFamily', type_='foreignkey')
    op.create_foreign_key(op.f('fk_ProjectProductFamily_project_id_Project'), 'ProjectProductFamily', 'Project', ['project_id'], ['id'], ondelete='CASCADE')
    op.create_foreign_key(op.f('fk_ProjectProductFamily_product_family_id_ProductFamily'), 'ProjectProductFamily', 'ProductFamily', ['product_family_id'], ['id'], ondelete='CASCADE')


def downgrade():
    op.drop_constraint(op.f('fk_ProjectProductFamily_product_family_id_ProductFamily'), 'ProjectProductFamily', type_='foreignkey')
    op.drop_constraint(op.f('fk_ProjectProductFamily_project_id_Project'), 'ProjectProductFamily', type_='foreignkey')
    op.create_foreign_key('fk_ProjectProductFamily_project_id_Project', 'ProjectProductFamily', 'Project', ['project_id'], ['id'])
    op.create_foreign_key('fk_ProjectProductFamily_project_id_ProductFamily', 'ProjectProductFamily', 'ProductFamily', ['product_family_id'], ['id'])