"""OEM130 Alter Ids from Int to UUID

Revision ID: 1d1fe669d84b
Revises: 95bfc564a7e7
Create Date: 2020-03-02 12:02:21.760454

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = '1d1fe669d84b'
down_revision = '95bfc564a7e7'
branch_labels = None
depends_on = None


# yapf: disable
def upgrade():
    # ### commands generated by Alembic ###
    op.execute(sa.text('CREATE EXTENSION IF NOT EXISTS "uuid-ossp";'))  # For uuid_generate_v4

    op.execute(sa.text('''
        CREATE TABLE process_status_detail_temp AS
            SELECT uuid_generate_v4() as status_id, fwan_process_id, file_id, component_id, status, status_date, status_id as status_id_bigint
            FROM process_status_detail
            ORDER BY status_date ASC;
        '''))

    op.execute(sa.text('''
        CREATE TABLE error_detail_temp AS
            SELECT p.status_id as status_id, e.details as details
            FROM process_status_detail_temp p JOIN error_detail e ON p.status_id_bigint = e.status_id;
        '''))

    op.execute(sa.text('''
        ALTER TABLE process_status_detail_temp DROP COLUMN status_id_bigint;
        '''))

    op.drop_table('error_detail')
    op.drop_table('process_status_detail')

    op.execute(sa.text('''
        DROP SEQUENCE IF EXISTS process_status_detail_status_id_seq;
        '''))

    op.execute(sa.text('''
        ALTER TABLE process_status_detail_temp RENAME TO process_status_detail;
        '''))

    op.execute(sa.text('''
        ALTER TABLE process_status_detail ALTER COLUMN status_id SET DEFAULT uuid_generate_v4();
        '''))

    op.execute(sa.text('''
        ALTER TABLE process_status_detail ADD PRIMARY KEY (status_id);
        '''))

    op.execute(sa.text('''
        CREATE INDEX process_status_detail_idx ON process_status_detail (fwan_process_id, file_id, component_id, status);
        '''))

    op.execute(sa.text('''
        ALTER TABLE error_detail_temp RENAME TO error_detail;
        '''))

    op.execute(sa.text('''
        ALTER TABLE error_detail ADD PRIMARY KEY (status_id);
        '''))

    op.execute(sa.text('''
        ALTER TABLE error_detail ADD CONSTRAINT error_detail_status_id_fkey FOREIGN KEY (status_id) REFERENCES process_status_detail (status_id);
        '''))

    # op.create_table('process_status_detail_temp',
    #                 sa.Column('status_id', postgresql.UUID(), server_default=sa.text("uuid_generate_v4()"), nullable=False),
    #                 sa.Column('fwan_process_id', postgresql.UUID(), nullable=True),
    #                 sa.Column('file_id', sa.Text(), nullable=True),
    #                 sa.Column('component_id', sa.Text(), nullable=True),
    #                 sa.Column('status', sa.Text(), nullable=True),
    #                 sa.Column('status_date', sa.DateTime(timezone=True), nullable=True),
    #                 sa.Column('status_id_bigint', sa.BigInteger(), nullable=False),
    #                 sa.PrimaryKeyConstraint('status_id')
    #                 )
    # op.drop_constraint('error_detail_status_id_fkey', 'error_detail', type_='foreignkey')
    # op.drop_constraint('error_detail_pkey', 'error_detail', type_='primarykey')
    # op.drop_constraint('process_status_detail_pkey', 'process_status_detail', type_='primarykey')
    # ### end Alembic commands ###


def downgrade():
    # ### commands generated by Alembic ###
    op.execute(sa.text('''
        CREATE SEQUENCE process_status_detail_status_id_seq;
        '''))

    op.execute(sa.text('''
        CREATE TABLE process_status_detail_temp AS
            SELECT nextval('process_status_detail_status_id_seq'::regclass) as status_id, fwan_process_id, file_id, component_id, status, status_date, status_id as status_id_uuid
            FROM process_status_detail
            ORDER BY status_date ASC;
        '''))

    op.execute(sa.text('''
        CREATE TABLE error_detail_temp AS
            SELECT p.status_id as status_id, e.details as details
            FROM process_status_detail_temp p JOIN error_detail e ON p.status_id_uuid = e.status_id;
        '''))

    op.execute(sa.text('''
        ALTER TABLE process_status_detail_temp DROP COLUMN status_id_uuid;
        '''))

    op.drop_table('error_detail')
    op.drop_table('process_status_detail')

    op.execute(sa.text('''
        ALTER TABLE process_status_detail_temp RENAME TO process_status_detail;
        '''))

    op.execute(sa.text('''
        ALTER TABLE process_status_detail ALTER COLUMN status_id SET DEFAULT nextval('process_status_detail_status_id_seq'::regclass);
        '''))

    op.execute(sa.text('''
        ALTER TABLE process_status_detail ADD PRIMARY KEY (status_id);
        '''))

    op.execute(sa.text('''
        CREATE INDEX process_status_detail_idx ON process_status_detail (fwan_process_id, file_id, component_id, status);
        '''))

    op.execute(sa.text('''
        ALTER TABLE error_detail_temp RENAME TO error_detail;
        '''))

    op.execute(sa.text('''
        ALTER TABLE error_detail ADD PRIMARY KEY (status_id);
        '''))

    op.execute(sa.text('''
        ALTER TABLE error_detail ADD CONSTRAINT error_detail_status_id_fkey FOREIGN KEY (status_id) REFERENCES process_status_detail (status_id);
        '''))

    # ### end Alembic commands ###

# yapf: enable
