"""add_start_date_to_campaign_phase

Revision ID: 12c5b9ea245d
Revises: f7067e97ffc1
Create Date: 2020-03-20 15:35:29.858359

"""
from string import Template

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '12c5b9ea245d'
down_revision = 'f7067e97ffc1'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###

    bind = op.get_bind()
    query_result = bind.execute(''' SELECT id FROM "Campaign"''').fetchall()

    op.drop_table('CampaignPhaseActivity')
    op.add_column('CampaignPhase', sa.Column('start_date', sa.Date(), nullable=True))

    for campaign_id_ in query_result:
        campaign_id = campaign_id_[0]
        earliest_activity_date_result = bind.execute('''SELECT min(start_date) FROM "Activity" WHERE campaign_id = {}'''.format(campaign_id)).fetchone()
        campaign_record_type = bind.execute('''
                    SELECT "RecordType".id FROM "Campaign" 
                    JOIN "Record" ON "Record".id = "Campaign".record_id 
                    JOIN "RecordType" ON "RecordType".id = "Record".record_type_id 
                    WHERE "Campaign".id = {}
                '''.format(campaign_id)).fetchone()[0]
        campaign_record_name = bind.execute('''
                           SELECT "Record".name FROM "Campaign" 
                           JOIN "Record" ON "Record".id = "Campaign".record_id
                           WHERE "Campaign".id = {}
                       '''.format(campaign_id)).fetchone()
        campaign_artist = bind.execute('''
                                   SELECT "Artist".name FROM "Campaign" 
                                   JOIN "Artist" ON "Artist".id = "Campaign".artist_id
                                   WHERE "Campaign".id = {}
                               '''.format(campaign_id)).fetchone()

        campaign_artist = campaign_artist[0] if campaign_artist is not None else ""
        campaign_record_name = campaign_record_name[0] if campaign_record_name is not None else ""
        if campaign_record_name is not None:
            campaign_record_name = campaign_record_name.replace('"', '').replace("'", "''")

        phase_name = campaign_record_name if campaign_record_type == 3 else 'Phase 1'
        bind.execute(''' DELETE FROM "CampaignPhase" WHERE campaign_id = {}'''.format(campaign_id))
        if earliest_activity_date_result[0] is not None:
            phase_start_date = earliest_activity_date_result[0].strftime("%Y-%m-%d")
            t = Template(''' INSERT INTO "CampaignPhase" ("name", "start_date", "order", "campaign_id", "asset") VALUES ('$name', '$start_date', 0, '$campaign_id', '{"type": "track", "artist": "$artist", "title": "$record", "external_id": null}')''')
            bind.execute(t.substitute(name=phase_name, campaign_id=campaign_id, start_date=phase_start_date, artist=campaign_artist, record=campaign_record_name))
        else:
            t = Template(''' INSERT INTO "CampaignPhase" ("name", "order", "campaign_id", "asset") VALUES ('$name', 0, '$campaign_id', '{"type": "track", "artist": "$artist", "title": "$record", "external_id": null}')''')
            bind.execute(t.substitute(name=phase_name, campaign_id=campaign_id, artist=campaign_artist, record=campaign_record_name))


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('CampaignPhase', 'start_date')
    op.create_table('CampaignPhaseActivity',
    sa.Column('phase_id', sa.BIGINT(), autoincrement=False, nullable=False),
    sa.Column('activity_id', sa.BIGINT(), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['activity_id'], ['Activity.id'], name='fk_CampaignPhaseActivity_activity_id_Activity', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['phase_id'], ['CampaignPhase.id'], name='fk_CampaignPhaseActivity_phase_id_CampaignPhase', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('phase_id', 'activity_id', name='pk_CampaignPhaseActivity')
    )
    # ### end Alembic commands ###