"""merge_editor_and_teammate

Revision ID: c85746d5b86d
Revises: aa7ad7644aa2
Create Date: 2021-07-06 13:01:37.871414

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
from migrations.utils import get_table

revision = 'c85746d5b86d'
down_revision = 'aa7ad7644aa2'
branch_labels = None
depends_on = None


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

    user_project_table = get_table(bind, "UserProject")
    campaign_type_group = get_table(bind, "UserProjectCampaignTypeGroup")

    bind.execute(
        user_project_table.update().values(role=user_project_table.c.role - 1).where(user_project_table.c.role > 2)
    )

    editors = bind.execute(f'''
        SELECT id
            FROM "UserProject"
            LEFT JOIN "UserProjectCampaignTypeGroup"
                ON "UserProjectCampaignTypeGroup".user_project_id = "UserProject".id
            WHERE role = 2 AND (
                "UserProjectCampaignTypeGroup".user_project_id IS NULL
                AND "UserProjectCampaignTypeGroup".campaign_type_group_id IS NULL
            );
    ''').fetchall()

    for id in editors:
        for campaign_type_group_id in [1,2,3]:
            op.execute(
                campaign_type_group.insert()
                    .values(user_project_id=id[0], campaign_type_group_id=campaign_type_group_id)
            )


def downgrade():
    pass