"""combo_price_levevls

Revision ID: db8faa0c64cf
Revises: e198d2d26fee
Create Date: 2020-06-26 17:03:09.524000

"""
from alembic import op
import sqlalchemy as sa


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

first_agent_version = '20.7.14'


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;
                    
            -- price_levels
            CREATE TABLE temp_price_levels (
                    "key" INTEGER,
                    menu_item_id VARCHAR(64),
                    menu_modifier_id VARCHAR(64),
                    menu_combo_id VARCHAR(64),
                    menu_combo_item_key INTEGER,
                    id VARCHAR(64),
                    name VARCHAR(128),
                    price_per_unit INTEGER,
                    PRIMARY KEY ("key"),
                    CONSTRAINT fk_price_levels_menu_item FOREIGN KEY(menu_item_id) REFERENCES menu_items (id) ON DELETE cascade ON UPDATE cascade,
                    CONSTRAINT fk_price_levels_menu_modifier FOREIGN KEY(menu_modifier_id) REFERENCES modifiers (id) ON DELETE cascade ON UPDATE cascade,
                    CONSTRAINT "fk-price_levels-menu_combos" FOREIGN KEY(menu_combo_id) REFERENCES menu_combos (id) ON DELETE cascade ON UPDATE cascade,
                    CONSTRAINT "fk-price_levels-menu_combo_items" FOREIGN KEY(menu_combo_item_key) REFERENCES menu_combo_items ("key") ON DELETE cascade ON UPDATE cascade
            );

            insert into temp_price_levels (menu_item_id, menu_modifier_id, id, name, price_per_unit)
                        select menu_item_id, menu_modifier_id, id, name,  price_per_unit
                        from price_levels;
                        
            drop table price_levels;
            alter table temp_price_levels rename to price_levels;

            CREATE INDEX "idx-price_levels-menu_combo_id-id" ON price_levels (menu_combo_id, id);
            CREATE INDEX "idx-price_levels-menu_combo_item_key-id" ON price_levels (menu_combo_item_key, id);
            CREATE INDEX idx_price_levels_menu_item_id_id ON price_levels (menu_item_id, id);
            CREATE INDEX idx_price_levels_menu_modifier_id_id ON price_levels (menu_modifier_id, id);


            -- held_ticket_combo_items
            ALTER TABLE held_ticket_combo_items ADD COLUMN price_per_unit INTEGER;
            ALTER TABLE held_ticket_combo_items ADD COLUMN price_level_id VARCHAR(64);


            -- held_ticket_combos
            CREATE TABLE temp_held_ticket_combos (
                "key" INTEGER, 
                ticket_id VARCHAR(64), 
                id VARCHAR(64), 
                name VARCHAR(128), 
                quantity INTEGER NOT NULL, 
                split INTEGER, 
                price INTEGER NOT NULL, 
                included_tax INTEGER, 
                comment TEXT, 
                sent BOOLEAN, 
                sent_at DATETIME, 
                void BOOLEAN NOT NULL, 
                void_type_id INTEGER, 
                approving_employee_id INTEGER, 
                menu_combo_id VARCHAR(64), 
                price_per_unit INTEGER, 
                price_level_id VARCHAR(64), 
                PRIMARY KEY ("key"), 
                CONSTRAINT fk_held_ticket_combos_held_tickets FOREIGN KEY(ticket_id) REFERENCES held_tickets (id) ON DELETE cascade ON UPDATE cascade, 
                CHECK (sent IN (0, 1)), 
                CHECK (void IN (0, 1))
            );

            insert into temp_held_ticket_combos (ticket_id, id, name, quantity, split, included_tax, 
                        comment, sent, sent_at, void, void_type_id, approving_employee_id, menu_combo_id, price_per_unit, price_level_id)
                        select ticket_id, id, name, quantity, split, included_tax, 
                        comment, sent, sent_at, void, void_type_id, approving_employee_id, menu_combo_id, price_per_unit, price_level_id
                        from held_ticket_combos;
                        
            drop table held_ticket_combos;
            alter table temp_held_ticket_combos rename to held_ticket_combos;

            CREATE UNIQUE INDEX idx_held_ticket_combo_id_ticket_id ON held_ticket_combos (ticket_id, id);

            -- held_ticket_item_modifiers
            CREATE TABLE temp_held_ticket_item_modifiers (
                "key" INTEGER, 
                id VARCHAR(64), 
                name VARCHAR(128), 
                quantity INTEGER NOT NULL, 
                split INTEGER, 
                price INTEGER NOT NULL, 
                comment TEXT, 
                sent BOOLEAN, 
                sent_at DATETIME, 
                menu_modifier_id VARCHAR(64), 
                price_per_unit INTEGER, 
                price_level_id VARCHAR(64), 
                PRIMARY KEY ("key"), 
                CHECK (sent IN (0, 1))
            );

            insert into temp_held_ticket_item_modifiers (id, name, quantity, split, price, 
                        comment, sent, sent_at, menu_modifier_id, price_per_unit, price_level_id)
                        select id, name, quantity, split, price, 
                        comment, sent, sent_at, menu_modifier_id, price_per_unit, price_level_id
                        from held_ticket_item_modifiers;

            drop table held_ticket_item_modifiers;
            alter table temp_held_ticket_item_modifiers rename to held_ticket_item_modifiers;

            -- held_ticket_items

            CREATE TABLE temp_held_ticket_items (
                "key" INTEGER, 
                ticket_id VARCHAR(64), 
                id VARCHAR(64), 
                name VARCHAR(128), 
                quantity INTEGER NOT NULL, 
                split INTEGER, 
                price INTEGER NOT NULL, 
                included_tax INTEGER, 
                comment TEXT, 
                seat INTEGER, 
                sent BOOLEAN, 
                sent_at DATETIME, 
                void BOOLEAN NOT NULL, 
                void_type_id INTEGER, 
                approving_employee_id INTEGER, 
                menu_item_id VARCHAR(64), 
                item_order_mode_id VARCHAR(64), 
                price_per_unit INTEGER, 
                price_level_id VARCHAR(64), 
                PRIMARY KEY ("key"), 
                CONSTRAINT fk_held_ticket_items_tickets FOREIGN KEY(ticket_id) REFERENCES held_tickets (id) ON DELETE cascade ON UPDATE cascade, 
                CHECK (sent IN (0, 1)), 
                CHECK (void IN (0, 1))
            );

            insert into temp_held_ticket_items (ticket_id, id, name, quantity, split, price, included_tax, comment, seat,
                        sent, sent_at, void, void_type_id, approving_employee_id, menu_item_id, item_order_mode_id, price_per_unit, price_level_id)
                        select ticket_id, id, name, quantity, split, price, included_tax, comment, seat,
                        sent, sent_at, void, void_type_id, approving_employee_id, menu_item_id, item_order_mode_id, price_per_unit, price_level_id
                        from held_ticket_items;

            drop table held_ticket_items;
            alter table temp_held_ticket_items rename to held_ticket_items;

            CREATE UNIQUE INDEX idx_held_ticket_id_id ON held_ticket_items (ticket_id, id);

            -- 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;

            -- price_levels
            CREATE TABLE temp_price_levels (
                    "key" INTEGER,
                    menu_item_id VARCHAR(64),
                    menu_modifier_id VARCHAR(64),
                    id VARCHAR(64),
                    name VARCHAR(128),
                    price_per_unit INTEGER,
                    PRIMARY KEY ("key"),
                    CONSTRAINT fk_price_levels_menu_item FOREIGN KEY(menu_item_id) REFERENCES menu_items (id) ON DELETE cascade ON UPDATE cascade,
                    CONSTRAINT fk_price_levels_menu_modifier FOREIGN KEY(menu_modifier_id) REFERENCES modifiers (id) ON DELETE cascade ON UPDATE cascade
            );

            insert into temp_price_levels (menu_item_id, menu_modifier_id, id, name, price_per_unit)
                        select menu_item_id, menu_modifier_id, id, name,  price_per_unit
                        from price_levels where menu_combo_id is null and menu_combo_item_key is null;
                        
            drop table price_levels;
            alter table temp_price_levels rename to price_levels;

            CREATE INDEX idx_price_levels_menu_item_id_id ON price_levels (menu_item_id, id);
            CREATE INDEX idx_price_levels_menu_modifier_id_id ON price_levels (menu_modifier_id, id);


            -- held_ticket_combo_items
            CREATE TABLE temp_held_ticket_combo_items (
                "key" INTEGER, 
                ticket_id VARCHAR(64), 
                ticket_combo_id VARCHAR(64), 
                id VARCHAR(64), 
                name VARCHAR(128), 
                quantity INTEGER NOT NULL, 
                split INTEGER, 
                price INTEGER NOT NULL, 
                comment TEXT, 
                sent BOOLEAN, 
                sent_at DATETIME, 
                menu_combo_item_group_id VARCHAR(64), 
                menu_combo_item_id VARCHAR(64), 
                menu_item_id VARCHAR(64), 
                PRIMARY KEY ("key"), 
                CONSTRAINT fk_held_ticket_combo_items_held_tickets FOREIGN KEY(ticket_id) REFERENCES held_tickets (id) ON DELETE cascade ON UPDATE cascade, 
                CONSTRAINT fk_held_ticket_combo_items_held_ticket_combos FOREIGN KEY(ticket_id, ticket_combo_id) REFERENCES held_ticket_combos (ticket_id, id) ON DELETE cascade ON UPDATE cascade, 
                CHECK (sent IN (0, 1))
            );

            insert into temp_held_ticket_combo_items (ticket_id, ticket_combo_id, id, name, quantity, split, price,
                        comment, sent, sent_at, menu_combo_item_group_id, menu_combo_item_id, menu_item_id)
                        select ticket_id, ticket_combo_id, id, name, quantity, split, price,
                        comment, sent, sent_at, menu_combo_item_group_id, menu_combo_item_id, menu_item_id
                        from held_ticket_combo_items;
                        
            drop table held_ticket_combo_items;
            alter table temp_held_ticket_combo_items rename to held_ticket_combo_items;

            CREATE UNIQUE INDEX idx_held_ticket_combo_items_ticket_id_id ON held_ticket_combo_items (ticket_id, id);

            -- held_ticket_combos
            CREATE TABLE temp_held_ticket_combos (
                "key" INTEGER, 
                ticket_id VARCHAR(64), 
                id VARCHAR(64), 
                name VARCHAR(128), 
                quantity INTEGER NOT NULL, 
                split INTEGER, 
                price INTEGER NOT NULL, 
                included_tax INTEGER, 
                comment TEXT, 
                sent BOOLEAN, 
                sent_at DATETIME, 
                void BOOLEAN NOT NULL, 
                void_type_id INTEGER, 
                approving_employee_id INTEGER, 
                menu_combo_id VARCHAR(64), 
                price_per_unit INTEGER, 
                price_level_id VARCHAR(32), 
                PRIMARY KEY ("key"), 
                CONSTRAINT fk_held_ticket_combos_held_tickets FOREIGN KEY(ticket_id) REFERENCES held_tickets (id) ON DELETE cascade ON UPDATE cascade, 
                CHECK (sent IN (0, 1)), 
                CHECK (void IN (0, 1))
            );

            insert into temp_held_ticket_combos (ticket_id, id, name, quantity, split, included_tax, 
                        comment, sent, sent_at, void, void_type_id, approving_employee_id, menu_combo_id, price_per_unit, price_level_id)
                        select ticket_id, id, name, quantity, split, included_tax, 
                        comment, sent, sent_at, void, void_type_id, approving_employee_id, menu_combo_id, price_per_unit, price_level_id
                        from held_ticket_combos;
                        
            drop table held_ticket_combos;
            alter table temp_held_ticket_combos rename to held_ticket_combos;

            CREATE UNIQUE INDEX idx_held_ticket_combo_id_ticket_id ON held_ticket_combos (ticket_id, id);

            -- held_ticket_item_modifiers
            CREATE TABLE temp_held_ticket_item_modifiers (
                "key" INTEGER, 
                id VARCHAR(64), 
                name VARCHAR(128), 
                quantity INTEGER NOT NULL, 
                split INTEGER, 
                price INTEGER NOT NULL, 
                comment TEXT, 
                sent BOOLEAN, 
                sent_at DATETIME, 
                menu_modifier_id VARCHAR(64), 
                price_per_unit INTEGER, 
                price_level_id VARCHAR(32), 
                PRIMARY KEY ("key"), 
                CHECK (sent IN (0, 1))
            );

            insert into temp_held_ticket_item_modifiers (id, name, quantity, split, price, 
                        comment, sent, sent_at, menu_modifier_id, price_per_unit, price_level_id)
                        select id, name, quantity, split, price, 
                        comment, sent, sent_at, menu_modifier_id, price_per_unit, price_level_id
                        from held_ticket_item_modifiers;

            drop table held_ticket_item_modifiers;
            alter table temp_held_ticket_item_modifiers rename to held_ticket_item_modifiers;

            -- held_ticket_items

            CREATE TABLE temp_held_ticket_items (
                "key" INTEGER, 
                ticket_id VARCHAR(64), 
                id VARCHAR(64), 
                name VARCHAR(128), 
                quantity INTEGER NOT NULL, 
                split INTEGER, 
                price INTEGER NOT NULL, 
                included_tax INTEGER, 
                comment TEXT, 
                seat INTEGER, 
                sent BOOLEAN, 
                sent_at DATETIME, 
                void BOOLEAN NOT NULL, 
                void_type_id INTEGER, 
                approving_employee_id INTEGER, 
                menu_item_id VARCHAR(64), 
                item_order_mode_id VARCHAR(64), 
                price_per_unit INTEGER, 
                price_level_id VARCHAR(32), 
                PRIMARY KEY ("key"), 
                CONSTRAINT fk_held_ticket_items_tickets FOREIGN KEY(ticket_id) REFERENCES held_tickets (id) ON DELETE cascade ON UPDATE cascade, 
                CHECK (sent IN (0, 1)), 
                CHECK (void IN (0, 1))
            );

            insert into temp_held_ticket_items (ticket_id, id, name, quantity, split, price, included_tax, comment, seat,
                        sent, sent_at, void, void_type_id, approving_employee_id, menu_item_id, item_order_mode_id, price_per_unit, price_level_id)
                        select ticket_id, id, name, quantity, split, price, included_tax, comment, seat,
                        sent, sent_at, void, void_type_id, approving_employee_id, menu_item_id, item_order_mode_id, price_per_unit, price_level_id
                        from held_ticket_items;

            drop table held_ticket_items;
            alter table temp_held_ticket_items rename to held_ticket_items;

            CREATE UNIQUE INDEX idx_held_ticket_id_id ON held_ticket_items (ticket_id, id);

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