"""fix_empty_project_start_date

Revision ID: 2bc328d0638a
Revises: 30dfb2892d0b
Create Date: 2022-07-11 14:18:43.132025

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

# revision identifiers, used by Alembic.
revision = '2bc328d0638a'
down_revision = '30dfb2892d0b'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    bind.execute("""
        with subquery as (
            select coalesce(
                min("PRSPurchaseOrder".created_date),
                min("Campaign".start_date),
                ("Project".end_date - INTERVAL '1 DAY')::date
            ) as start_date
            from "Project"
            left outer join "Campaign" on "Project".id = "Campaign".project_id and "Campaign".start_date is not NULL
            left outer join "PRSPurchaseOrder" on "PRSPurchaseOrder".project_id = "Project".id
            group by "Project".id
        )
        update "Project"
        set initial_start_date = subquery.start_date
        from subquery
        where "Project".initial_start_date is null
        and "Project".owner_id is not null;
    """)


def downgrade():
    pass