"""alter table estado

Revision ID: 6972787fd9d7
Revises: 31a3eca86283
Create Date: 2020-11-16 21:05:47.172743

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql
from sqlalchemy.orm import sessionmaker

Session = sessionmaker()

# revision identifiers, used by Alembic.
revision = '6972787fd9d7'
down_revision = '31a3eca86283'
branch_labels = None
depends_on = None

def preencher(filename):
    bind = op.get_bind()
    session = Session(bind=bind)
    # Open the .sql file
    sql_file = open(filename,'r')
    # Create an empty command string
    sql_command = ''

    # Iterate over all lines in the sql file
    for line in sql_file:
        # Ignore commented lines
        if not line.startswith('--') and line.strip('\n'):
            # Append line to the command string
            sql_command += line.strip('\n')

            # If the command string ends with ';', it is a full statement
            if sql_command.endswith(';'):
                # Try to execute statement and commit it
                try:
                    session.execute(sa.text(sql_command))
                    session.commit()

                # Assert in case of error
                except:
                    print('Ops')

                # Finally, clear command string
                finally:
                    sql_command = ''


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('estado', sa.Column('nome', sa.String(length=25), nullable=False))
    op.add_column('estado', sa.Column('uf', sa.String(length=2), nullable=False))
    op.drop_index('descricao', table_name='estado')
    op.create_unique_constraint(None, 'estado', ['nome'])
    op.create_unique_constraint(None, 'estado', ['uf'])
    op.drop_column('estado', 'descricao')
    preencher('sqls/estados.sql')
    preencher('sqls/cidades.sql')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('estado', sa.Column('descricao', mysql.VARCHAR(collation='utf8mb4_unicode_ci', length=2), nullable=False))
    op.drop_constraint(None, 'estado', type_='unique')
    op.drop_constraint(None, 'estado', type_='unique')
    op.create_index('descricao', 'estado', ['descricao'], unique=True)
    op.drop_column('estado', 'uf')
    op.drop_column('estado', 'nome')
    # ### end Alembic commands ###
