"""old_ccp_projects_removing

Revision ID: 85a91068148c
Revises: 68c9fcfc5fa8
Create Date: 2022-05-02 14:16:48.264145

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = '85a91068148c'
down_revision = '68c9fcfc5fa8'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    import_user_editor_id = bind.execute('''select id from "User" where email = 'ccp@ccp.com' ''').scalar()
    get_projects_id = bind.execute(f'''select p.id from "Project" p
            left join (select p.id id, coalesce(min(pp.start_date), p.initial_start_date) start_date
            from "Project" p
                left join "ProjectPhase" pp on pp.project_id = p.id
                group by p.id) as metadata on metadata.id = p.id
                left join (select project_id, count(project_id) number from "Campaign" group by project_id) as camp
                    on camp.project_id = p.id
                where metadata.start_date < '2020-01-01' and p.source='CCP' and (
                (p.edit_user_id = {import_user_editor_id} and not p.owner_id is null and camp.number = 0) or
                p.owner_id is null)
            GROUP BY p.id''').fetchall()
    projects_id = [proj[0] for proj in get_projects_id]
    if projects_id:
        bind.execute(f'''UPDATE "Campaign" set project_id = null, purchase_order_id = null where project_id in
            ({", ".join(map(str, projects_id))})''')
        bind.execute(f'''UPDATE "ArtistMoment" set project_id = null where project_id in
                        ({", ".join(map(str, projects_id))})''')
        bind.execute(f'DELETE FROM "ProjectCampaign" where project_id in ({", ".join(map(str, projects_id))})')
        bind.execute(f'DELETE FROM "ProjectPhase" where project_id in ({", ".join(map(str, projects_id))})')
        bind.execute(f'DELETE FROM "LinkfireProject" where project_id in ({", ".join(map(str, projects_id))})')
        bind.execute(f'DELETE FROM "ProjectTargetItem" where project_id in ({", ".join(map(str, projects_id))})')
        bind.execute(f'DELETE FROM "UserProject" where project_id in ({", ".join(map(str, projects_id))})')
        bind.execute(f'DELETE FROM "PRSProjectBudget" where project_id in ({", ".join(map(str, projects_id))})')
        bind.execute(f'DELETE FROM "PRSPurchaseOrder" where project_id in ({", ".join(map(str, projects_id))})')
        bind.execute(f'DELETE FROM "ProjectProductFamily" where project_id in ({", ".join(map(str, projects_id))})')
        bind.execute(f'DELETE FROM "Project" where id in ({", ".join(map(str, projects_id))})')


def downgrade():
    pass