"""make_held_ticket_item_discounts_nullable

Revision ID: 54290c977da3
Revises: ce7912a13af1
Create Date: 2020-06-05 01:54:35.937000

"""
from alembic import op
import sqlalchemy as sa


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

first_agent_version = '20.6.17'


def upgrade():
    """
    Use batch-operations to support sqlite

    > with op.batch_alter_table('table_name') as batch_op:
    >     pass
    """
    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;

            -- ticket_item_discounts
            CREATE TABLE temp_ticket_item_discounts (
                ticket_item_key INTEGER NOT NULL, 
                id VARCHAR(64) NOT NULL, 
                comment TEXT, 
                name VARCHAR(128), 
                value INTEGER, 
                discount_id VARCHAR(64), 
                PRIMARY KEY (ticket_item_key, id), 
                CONSTRAINT "fk-ticket_item_discounts-ticket_items" FOREIGN KEY(ticket_item_key) REFERENCES ticket_items ("key") ON DELETE cascade ON UPDATE cascade
            );
            
            INSERT INTO temp_ticket_item_discounts (ticket_item_key, id, comment, name, value, discount_id)
                SELECT ticket_item_key, id, comment, name, value, discount_id
                FROM ticket_item_discounts;
            
            DROP TABLE ticket_item_discounts;
            ALTER TABLE temp_ticket_item_discounts RENAME TO ticket_item_discounts;
            
            -- held_ticket_item_discounts
            CREATE TABLE temp_held_ticket_item_discounts (
                ticket_item_key INTEGER NOT NULL, 
                id VARCHAR(64) NOT NULL, 
                comment TEXT, 
                name VARCHAR(128), 
                value INTEGER, 
                discount_id VARCHAR(64), 
                PRIMARY KEY (ticket_item_key, id), 
                CONSTRAINT "fk-held_ticket_item_discounts-held_ticket_items" FOREIGN KEY(ticket_item_key) REFERENCES held_ticket_items ("key") ON DELETE cascade ON UPDATE cascade
            );
            
            INSERT INTO temp_held_ticket_item_discounts (ticket_item_key, id, comment, name, value, discount_id)
                SELECT ticket_item_key, id, comment, name, value, discount_id
                FROM held_ticket_item_discounts;
                
            DROP TABLE held_ticket_item_discounts;
            ALTER TABLE temp_held_ticket_item_discounts RENAME TO held_ticket_item_discounts;

            -- Commit DDL changes.
            release up;
            """
        )

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


def downgrade():
    """
    Use batch-operations to support sqlite

    > with op.batch_alter_table('table_name') as batch_op:
    >     pass
    """

    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;
            
            -- ticket_item_discounts
            UPDATE ticket_item_discounts SET value=0 WHERE value IS NULL;
            
            CREATE TABLE temp_ticket_item_discounts (
                ticket_item_key INTEGER NOT NULL, 
                id VARCHAR(64) NOT NULL, 
                comment TEXT, 
                name VARCHAR(128), 
                value INTEGER NOT NULL, 
                discount_id VARCHAR(64), 
                PRIMARY KEY (ticket_item_key, id), 
                CONSTRAINT "fk_ticket_item_discounts_ticket_items" FOREIGN KEY(ticket_item_key) REFERENCES ticket_items ("key") ON DELETE cascade ON UPDATE cascade
            );
    
            INSERT INTO temp_ticket_item_discounts (ticket_item_key, id, comment, name, value, discount_id)
                SELECT ticket_item_key, id, comment, name, value, discount_id
                FROM ticket_item_discounts;
                
            DROP TABLE ticket_item_discounts;
            ALTER TABLE temp_ticket_item_discounts RENAME TO ticket_item_discounts;
            
            
            -- held_ticket_item_discounts
            UPDATE held_ticket_item_discounts SET value=0 WHERE value IS NULL;
            
            CREATE TABLE temp_held_ticket_item_discounts (
                ticket_item_key INTEGER NOT NULL, 
                id VARCHAR(64) NOT NULL, 
                comment TEXT, 
                name VARCHAR(128), 
                value INTEGER NOT NULL, 
                discount_id VARCHAR(64), 
                PRIMARY KEY (ticket_item_key, id), 
                CONSTRAINT "fk_held_ticket_item_discounts_ticket_items" FOREIGN KEY(ticket_item_key) REFERENCES held_ticket_items ("key") ON DELETE cascade ON UPDATE cascade
            );
    
            INSERT INTO temp_held_ticket_item_discounts (ticket_item_key, id, comment, name, value, discount_id)
                SELECT ticket_item_key, id, comment, name, value, discount_id
                FROM held_ticket_item_discounts;
                
            DROP TABLE held_ticket_item_discounts;
            ALTER TABLE temp_held_ticket_item_discounts RENAME TO held_ticket_item_discounts;
            
            -- Commit DDL changes.
            release down;
            """
        )

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