"""vehicle_info

Revision ID: d7d924a6a31f
Revises: db8faa0c64cf
Create Date: 2020-07-29 16:19:56.917000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'd7d924a6a31f'
down_revision = 'db8faa0c64cf'
branch_labels = None
depends_on = None

first_agent_version = '20.8.5'


def upgrade():
    """
    Use batch-operations to support sqlite. If you are modifying any tables with foreign keys, be sure to disable
    foreign keys while upgrading.

    > conn = op.get_bind()
    > try:
    >     conn.execute('PRAGMA foreign_keys = "0";')
    >     with op.batch_alter_table('table_name') as batch_op:
    >         pass
    > finally:
    >     conn.execute('PRAGMA foreign_keys = "0";')
    """
    connection = op.get_bind().engine.raw_connection()
    try:
        connection.executescript('PRAGMA foreign_keys = "0";')

        connection.executescript(
            """
            -- Explicit savepoint allows for proper rollback of DDL. Explicit rollback not needed.
            savepoint up;
                    
            -- user_data
            ALTER TABLE user_data ADD COLUMN vehicle_make VARCHAR(32);
            ALTER TABLE user_data ADD COLUMN vehicle_model VARCHAR(32);
            ALTER TABLE user_data ADD COLUMN vehicle_year VARCHAR(4);
            ALTER TABLE user_data ADD COLUMN vehicle_color VARCHAR(32);
            
            -- Commit DDL changes.
            release up;
            """
        )
    finally:
        connection.execute('PRAGMA foreign_keys = "1";')


def downgrade():
    """
    Use batch-operations to support sqlite. If you are modifying any tables with foreign keys, be sure to disable
    foreign keys while upgrading.

    > conn = op.get_bind()
    > try:
    >     conn.execute('PRAGMA foreign_keys = "0";')
    >     with op.batch_alter_table('table_name') as batch_op:
    >         pass
    > finally:
    >     conn.execute('PRAGMA foreign_keys = "0";')
    """
    connection = op.get_bind().engine.raw_connection()
    try:
        connection.executescript('PRAGMA foreign_keys = "0";')

        connection.executescript(
            """
            -- Explicit savepoint allows for proper rollback of DDL. Explicit rollback not needed.
            savepoint up;
                    
            -- user_data
            CREATE TABLE temp_user_data (
                id VARCHAR(40) NOT NULL, 
                account_number VARCHAR(32), 
                first_name VARCHAR(32), 
                last_name VARCHAR(32), 
                address_street1 VARCHAR(32), 
                address_street2 VARCHAR(32), 
                address_street3 VARCHAR(32), 
                address_zip VARCHAR(32), 
                address_city VARCHAR(32), 
                address_state VARCHAR(32), 
                phone VARCHAR(32), 
                phone_ext VARCHAR(10), 
                email VARCHAR(128), 
                PRIMARY KEY (id)
            );
            insert into temp_user_data (id, account_number, first_name, last_name, address_street1, address_street2, address_street3,
                        address_zip, address_city, address_state, phone, phone_ext, email)
                        select id, account_number, first_name, last_name, address_street1, address_street2, address_street3,
                        address_zip, address_city, address_state, phone, phone_ext, email
                        from user_data;

            drop table user_data;
            alter table temp_user_data rename to user_data;


            -- Commit DDL changes.
            release up;
            """
        )
    finally:
        connection.execute('PRAGMA foreign_keys = "1";')
    pass
