"""fix_project_start_dates

Revision ID: d46af466811e
Revises: d474e086de55
Create Date: 2020-11-23 14:16:27.296062

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'd46af466811e'
down_revision = 'faf37068df31'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    bind.execute('''
            WITH project_campaign_start_date as (
                SELECT "Project".id, MIN(C.start_date) as date
                FROM "Project"
                JOIN "Campaign" C ON C.project_id = "Project".id
                GROUP BY "Project".id
            ),
            project_phase_start_date as (
                SELECT "Project".id, MIN(PP.start_date) as date
                FROM "Project"
                JOIN "ProjectPhase" PP ON PP.project_id = "Project".id
                GROUP BY "Project".id
            ),
            project_start_date as (
                SELECT P.id, LEAST(project_campaign_start_date.date, project_phase_start_date.date) as date
                FROM "Project" P
                JOIN project_phase_start_date ON project_phase_start_date.id = P.id
                LEFT OUTER JOIN project_campaign_start_date ON project_campaign_start_date.id = P.id
            )

            UPDATE "ProjectPhase"
            SET start_date = project_start_date.date
            FROM project_start_date
            WHERE "ProjectPhase".project_id = project_start_date.id;
        ''')


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###