"""remove_old_ccp_projects

Revision ID: 6e16011951ae
Revises: 81019616f4dd
Create Date: 2022-03-29 11:59:22.726377

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '6e16011951ae'
down_revision = '81019616f4dd'
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 camp.number = 0 and
                    (p.edit_user_id = {import_user_editor_id} and not p.owner_id is null or p.owner_id is null)
            GROUP BY p.id''').fetchall()
    projects_id = tuple([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 {projects_id}')
        bind.execute(f'UPDATE "ArtistMoment" set project_id = null where project_id in {projects_id}')
        bind.execute(f'DELETE FROM "ProjectCampaign" where project_id in {projects_id}')
        bind.execute(f'DELETE FROM "ProjectPhase" where project_id in {projects_id}')
        bind.execute(f'DELETE FROM "LinkfireProject" where project_id in {projects_id}')
        bind.execute(f'DELETE FROM "ProjectTargetItem" where project_id in {projects_id}')
        bind.execute(f'DELETE FROM "UserProject" where project_id in {projects_id}')
        bind.execute(f'DELETE FROM "PRSProjectBudget" where project_id in {projects_id}')
        bind.execute(f'DELETE FROM "PRSPurchaseOrder" where project_id in {projects_id}')
        bind.execute(f'DELETE FROM "ProjectProductFamily" where project_id in {projects_id}')
        bind.execute(f'DELETE FROM "Project" where id in {projects_id}')


def downgrade():
    pass