"""rework_phases

Revision ID: 386ac2df985c
Revises: 35304b84ca0d
Create Date: 2022-05-30 13:31:55.476009

"""
from alembic import op
import sqlalchemy as sa
from migrations.fixtures.delphi_tables.v4.data_refresher import DelphiTablesDataRefresher as DelphiTablesDataRefresherv4
from migrations.fixtures.delphi_tables.v5.data_refresher import DelphiTablesDataRefresher as DelphiTablesDataRefresherv5


# revision identifiers, used by Alembic.
revision = '386ac2df985c'
down_revision = '35304b84ca0d'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    delphi_refresherv4 = DelphiTablesDataRefresherv4()
    delphi_refresherv4.drop_triggers(bind)

    op.add_column('Campaign', sa.Column('project_phase_id', sa.Integer(), nullable=True, index=True))
    op.add_column('Campaign', sa.Column('order_in_phase', sa.SmallInteger(), nullable=True))
    op.create_foreign_key(
        op.f('fk_Campaign_project_phase_id_ProjectPhase'),
        'Campaign',
        'ProjectPhase',
        ['project_phase_id'],
        ['id'],
        ondelete='RESTRICT',
    )

    op.create_check_constraint(
        'check_only_internal_campaigns_have_phase',
        'Campaign',
        """not ((source != 'dec' and source is not null) and project_phase_id is not null)"""
    )

    op.create_unique_constraint(
        'uq_Campaign_project_phase_id_order_in_phase',
        'Campaign',
        ['project_phase_id', 'order_in_phase']
    )

    bind.execute("""
        update "Campaign"
        set project_phase_id = subquery.phase_id
        from (
            with phases_stuff as (
                select "ProjectPhase".id, "ProjectPhase".project_id, "ProjectPhase".order, "ProjectPhase".start_date,
                   cast(
                       coalesce(
                           lead("ProjectPhase".start_date - cast('1 DAY' as interval))
                           over (partition by "ProjectPhase".project_id order by "ProjectPhase".order),
                           greatest("Project".end_date, "ProjectPhase".start_date)
                       )
                       as date
                   ) as end_date
                from "ProjectPhase"
                left outer join "Project" on "Project".id = "ProjectPhase".project_id
            )
            select "Campaign".id as campaign_id, phases_stuff.id as phase_id
            from "Campaign"
            join phases_stuff on phases_stuff.project_id = "Campaign".project_id
                                 and "Campaign".start_date between phases_stuff.start_date and phases_stuff.end_date
            where ("Campaign".source = 'dec' or "Campaign".source is null)
        ) as subquery
        where "Campaign".id = subquery.campaign_id
    """)

    bind.execute("""
        with subquery as (
            select "Project".id as id, phases_subquery.start_date as start_date from "Project"
            left join lateral (
                select "ProjectPhase".start_date as start_date from "ProjectPhase"
                where "ProjectPhase".project_id = "Project".id
                order by "ProjectPhase".start_date ASC
                limit 1
            ) as phases_subquery on true
            where "Project".owner_id is not null
        )
        update "Project"
        set initial_start_date = subquery.start_date
        from subquery
        where "Project".id = subquery.id
    """)

    bind.execute("""
        update "ProjectPhase"
        set name = subquery.name
        from (select concat(
            "ProjectPhase".name,
            ' - ',
            case
                when "Project".currency = 'USD' then concat(
                    to_char("ProjectPhase".start_date, 'MM/DD/YYYY'),
                    '-',
                    to_char(
                        coalesce(
                            lead("ProjectPhase".start_date - cast('1 DAY' as interval))
                                over (partition by "ProjectPhase".project_id order by "ProjectPhase".order),
                            greatest("Project".end_date, "ProjectPhase".start_date)
                        ),
                        'MM/DD/YYYY'
                    )
                )
                else concat(
                    to_char("ProjectPhase".start_date, 'DD/MM/YYYY'),
                    '-',
                    to_char(
                        coalesce(
                            lead("ProjectPhase".start_date - cast('1 DAY' as interval))
                                over (partition by "ProjectPhase".project_id order by "ProjectPhase".order),
                            greatest("Project".end_date, "ProjectPhase".start_date)
                        ),
                        'DD/MM/YYYY'
                    )
                )
            end,
            case
                when "ProjectPhase".objective_text is not null and "ProjectPhase".objective_text != ''
                    then concat(' - ', "ProjectPhase".objective_text)
                else ''
            end
        ) as name,
        "ProjectPhase".id as id
        from "ProjectPhase"
        join "Project" on "Project".id = "ProjectPhase".project_id) as subquery
        where "ProjectPhase".id = subquery.id
    """)

    delphi_refresherv5 = DelphiTablesDataRefresherv5()
    delphi_refresherv5.refresh(bind)

    op.drop_column('phases', 'start_date')
    op.drop_column('phases', 'end_date')
    op.drop_column('ProjectPhase', 'start_date')


def downgrade():
    pass