"""add_data_for_empty_media_plans

Revision ID: e2cc39f66d0c
Revises: efcd8e303ef1
Create Date: 2022-11-07 13:17:48.456636

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

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

revision = 'e2cc39f66d0c'
down_revision = 'efcd8e303ef1'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    media_plan_phase = get_table(bind, "MediaPlanPhase")
    campaign = get_table(bind, "Campaign")

    op.alter_column('Campaign', 'media_plan_phase_id',
               existing_type=sa.BIGINT(),
               type_=sa.Integer(),
               existing_nullable=True)

    op.drop_column('MediaPlanPhase', 'objective_text')

    empty_media_plans_data = bind.execute("""
        select MP.id, MP.project_id
        from "MediaPlan" AS MP
        left outer join "MediaPlanPhase" AS MPP ON MPP.media_plan_id = MP.id
        where MPP.id is null;
        """
    ).fetchall()
    for media_plan_id, project_id in empty_media_plans_data:
        phase_id = bind.execute(
            insert(media_plan_phase)
                .values(media_plan_id=media_plan_id, name="Phase", order=0)
                .returning(media_plan_phase.c.id)
        ).fetchone()[0]
        bind.execute(
            insert(campaign)
                .values(project_id=project_id, order_in_phase=0, workflow_status="Draft", media_plan_phase_id=phase_id)
        )


def downgrade():
    op.add_column('MediaPlanPhase', sa.Column('objective_text', sa.VARCHAR(), autoincrement=False, nullable=True))

    op.alter_column('Campaign', 'media_plan_phase_id',
               existing_type=sa.Integer(),
               type_=sa.BIGINT(),
               existing_nullable=True)