"""add_project_end_date

Revision ID: 50b5bb27d424
Revises: 132a25208482
Create Date: 2020-11-16 12:50:41.384580

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '50b5bb27d424'
down_revision = '764bf0e5188d'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    op.add_column('Project', sa.Column('end_date', sa.Date(), nullable=True))
    bind.execute('''
        WITH project_campaign_end_date as (
            SELECT "Project".id, MAX(C.end_date) as date
            FROM "Project"
            JOIN "Campaign" C ON C.project_id = "Project".id
            GROUP BY "Project".id
        ),
        project_phase_end_date as (
            SELECT "Project".id, MAX(PP.start_date) as date
            FROM "Project"
            JOIN "ProjectPhase" PP ON PP.project_id = "Project".id
            GROUP BY "Project".id
        ),
        project_end_date as (
            SELECT P.id, GREATEST(project_campaign_end_date.date, project_phase_end_date.date) as date
            FROM "Project" P
            JOIN project_phase_end_date ON project_phase_end_date.id = P.id
            LEFT OUTER JOIN project_campaign_end_date ON project_campaign_end_date.id = P.id
        )

        UPDATE "Project"
        SET end_date = project_end_date.date
        FROM project_end_date
        WHERE "Project".id = project_end_date.id;
    ''')
    op.alter_column('Project', 'end_date', nullable=False)


def downgrade():
    op.drop_column('Project', 'end_date')