"""add_email_to_user_data

Revision ID: e198d2d26fee
Revises: 54290c977da3
Create Date: 2020-06-10 10:43:39.194000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'e198d2d26fee'
down_revision = '54290c977da3'
branch_labels = None
depends_on = None

first_agent_version = '20.6.17'


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;
            
            ALTER TABLE user_data
                ADD COLUMN email VARCHAR(128);
                
            -- 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 down;
            
            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),
                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
            )
            SELECT
                id,
                account_number,
                first_name,
                last_name,
                address_street1,
                address_street2,
                address_street3,
                address_zip,
                address_city,
                address_state,
                phone,
                phone_ext
            FROM user_data;
            
            DROP TABLE user_data;
            ALTER TABLE temp_user_data RENAME TO user_data;
            
            -- Commit DDL changes.
            release down;
            """
        )

    finally:
        connection.execute('PRAGMA foreign_keys = "1";')
