"""update_delphi_tables

Revision ID: 542328df669a
Revises: 8032a165f55f
Create Date: 2021-04-21 10:28:00.785844

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '542328df669a'
down_revision = '8032a165f55f'
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    bind.execute(
        """
            TRUNCATE projects,
                     project_artists,
                     phases,
                     phase_campaigns,
                     user_projects,
                     project_campaigns,
                     decibel_labels
                     CASCADE;
        """
    )
    bind.execute(
        """
            ALTER TABLE projects
            ADD COLUMN IF NOT EXISTS gras_id varchar;

            ALTER TABLE decibel_labels
            ADD COLUMN IF NOT EXISTS rep_owner_key varchar;

            CREATE INDEX IF NOT EXISTS ix_projects_gras_id ON projects using btree(gras_id);
            CREATE INDEX IF NOT EXISTS ix_decibel_labels_rep_owner_key ON decibel_labels using btree(rep_owner_key);
        """
    )

    bind.execute(
        """
            -- projects
            insert into projects(id, gras_id, name, budget, decibel_label_id, end_date, start_date)
            select id, gras_id, name, budget, decibel_label_id, end_date, start_date from projects_view;
            -- project_artists
            insert into project_artists(project_id, artist_id)
            select project_id, artist_id from project_artists_view;
            -- phases
            insert into phases(id, name, project_id, start_date, end_date)
            select id, name, project_id, start_date, end_date from phases_view;
            -- phase_campaigns
            insert into phase_campaigns(campaign_id, phase_id)
            select campaign_id, phase_id from phase_campaigns_view;
            -- user_projects
            insert into user_projects(project_id, user_id)
            select project_id, user_id from user_projects_view;
            -- project_campaigns
            insert into project_campaigns(project_id, campaign_id)
            select project_id, campaign_id from project_campaigns_view;
            -- decibel_labels
            insert into decibel_labels(id, rep_owner_key, name)
            select id, rep_owner_key, name from decibel_labels_view;
        """
    )


def downgrade():
    bind = op.get_bind()
    bind.execute(
        """
            DROP INDEX IF EXISTS ix_projects_gras_id;
            DROP INDEX IF EXISTS ix_decibel_labels_rep_owner_key;
            ALTER TABLE projects
            DROP COLUMN IF EXISTS gras_id;

            ALTER TABLE decibel_labels
            DROP COLUMN IF EXISTS rep_owner_key;
        """
    )