"""Allow non-globally unique combo groups and items.

Revision ID: ce7912a13af1
Revises: 3433a9867b15
Create Date: 2020-05-21 10:33:27.595000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
from sqlalchemy import Column, UniqueConstraint, Index, ForeignKeyConstraint, String

from positronics_agent.v1_0.models.mapper.sqlite_safe_big_integer import SqliteSafeBigInteger

revision = 'ce7912a13af1'
down_revision = '3433a9867b15'
branch_labels = None
depends_on = None

first_agent_version = '20.6.3'


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;
            
            -- menu_combo_item_groups
            CREATE TABLE temp_menu_combo_item_groups (
                "key" INTEGER NOT NULL, 
                id VARCHAR(64) NOT NULL, 
                menu_combo_id VARCHAR(64) NOT NULL, 
                pos_id VARCHAR(64), 
                name VARCHAR(128), 
                _stub BOOLEAN, 
                _registered_property_values BLOB, 
                PRIMARY KEY ("key"), 
                CONSTRAINT "fk-menu_combo_item_groups-menu_combos" FOREIGN KEY(menu_combo_id) REFERENCES menu_combos (id) ON DELETE cascade ON UPDATE cascade, 
                CHECK (_stub IN (0, 1))
            );            
            
            insert into temp_menu_combo_item_groups (id, menu_combo_id, pos_id, name, _stub, _registered_property_values)
            select id, menu_combo_id, pos_id, name, _stub, _registered_property_values
              from menu_combo_item_groups;
              
            drop table menu_combo_item_groups;
            alter table temp_menu_combo_item_groups rename to menu_combo_item_groups;
            
            CREATE UNIQUE INDEX "idx-menu_combo_item_groups-menu_combo_id-id" ON menu_combo_item_groups (menu_combo_id, id);
            
            -- menu_combo_items
            CREATE TABLE temp_menu_combo_items (
                "key" INTEGER NOT NULL, 
                id VARCHAR(64) NOT NULL, 
                menu_combo_item_group_key INTEGER NOT NULL, 
                menu_item_id VARCHAR(64), 
                pos_id VARCHAR(64), 
                "default" BOOLEAN, 
                price_per_unit INTEGER, 
                name VARCHAR(128), 
                _stub BOOLEAN, 
                _registered_property_values BLOB, 
                PRIMARY KEY ("key"), 
                CONSTRAINT "fk-menu_combo_items-menu_items" FOREIGN KEY(menu_item_id) REFERENCES menu_items (id) ON DELETE cascade ON UPDATE cascade, 
                CONSTRAINT "fk-menu_combo_items-menu_combo_item_groups" FOREIGN KEY(menu_combo_item_group_key) REFERENCES menu_combo_item_groups ("key") ON DELETE cascade ON UPDATE cascade, 
                CHECK ("default" IN (0, 1)), 
                CHECK (_stub IN (0, 1))
            );
            
            insert into temp_menu_combo_items (id, menu_combo_item_group_key, menu_item_id, pos_id, [default], price_per_unit, name, _stub, _registered_property_values)
            select i.id
                  ,g.key menu_combo_item_group_key
                  ,i.menu_item_id 
                  ,i.pos_id
                  ,i.[default]
                  ,i.price_per_unit
                  ,i.name
                  ,i._stub
                  ,i._registered_property_values
              from menu_combos c
              join menu_combo_item_groups g
                on c.id=g.menu_combo_id
              join menu_combo_item_groups_menu_combo_items a
                on g.id=a.menu_combo_item_group_id
              join menu_combo_items i
                on a.menu_combo_item_id=i.id;            
                
            drop table menu_combo_items;
            alter table temp_menu_combo_items rename to menu_combo_items;
            
            CREATE INDEX "idx-menu_combo_items-menu_item_id" ON menu_combo_items (menu_item_id);
            CREATE UNIQUE INDEX "idx-menu_combo_items-menu_combo_item_group_key-id" ON menu_combo_items (menu_combo_item_group_key, id);
            
            -- menu_combo_item_groups_menu_combo_items
            drop table menu_combo_item_groups_menu_combo_items;
            
            -- 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 and populate menu_combo_item_groups_menu_combo_items. FKs added after other tables updated + data copied.
            CREATE TABLE menu_combo_item_groups_menu_combo_items (
                menu_combo_item_group_id VARCHAR(64) NOT NULL, 
                menu_combo_item_id VARCHAR(64) NOT NULL,
                UNIQUE (menu_combo_item_group_id, menu_combo_item_id)
            );
            
            CREATE INDEX idx_menu_combo_groups_to_menu_combo_items ON menu_combo_item_groups_menu_combo_items (menu_combo_item_id);
            CREATE INDEX idx_menu_combo_items_to_menu_combo_groups ON menu_combo_item_groups_menu_combo_items (menu_combo_item_group_id);
            
            insert into menu_combo_item_groups_menu_combo_items (menu_combo_item_group_id, menu_combo_item_id)
            select g.id menu_combo_item_group_id, i.id menu_combo_item_id
              from menu_combos c
              join menu_combo_item_groups g
                on c.id=g.menu_combo_id
              join menu_combo_items i
                on g.key=i.menu_combo_item_group_key;              
                
            -- menu_combo_item_groups
            CREATE TABLE temp_menu_combo_item_groups (
                id VARCHAR(64) NOT NULL, 
                menu_combo_id VARCHAR(64), 
                pos_id VARCHAR(64), 
                name VARCHAR(128), 
                _stub BOOLEAN, 
                _registered_property_values BLOB, 
                PRIMARY KEY (id), 
                CONSTRAINT fk_menu_combo_item_groups_menu_combos FOREIGN KEY(menu_combo_id) REFERENCES menu_combos (id) ON DELETE cascade ON UPDATE cascade, 
                CHECK (_stub IN (0, 1))
            );
            
            insert into temp_menu_combo_item_groups (id, menu_combo_id, pos_id, name, _stub, _registered_property_values)
            select id, menu_combo_id, pos_id, name, _stub, _registered_property_values
              from menu_combo_item_groups;
              
            drop table menu_combo_item_groups;
            alter table temp_menu_combo_item_groups rename to menu_combo_item_groups;
            
            CREATE INDEX idx_menu_combo_item_group_menu_combo_id ON menu_combo_item_groups (menu_combo_id);         
            
            -- menu_combo_items 
            CREATE TABLE temp_menu_combo_items (
                id VARCHAR(64) NOT NULL, 
                menu_item_id VARCHAR(64), 
                pos_id VARCHAR(64), 
                "default" BOOLEAN, 
                price_per_unit INTEGER, 
                name VARCHAR(128), 
                _stub BOOLEAN, 
                _registered_property_values BLOB, 
                PRIMARY KEY (id), 
                CONSTRAINT fk_menu_combo_items_menu_items FOREIGN KEY(menu_item_id) REFERENCES menu_items (id) ON DELETE cascade ON UPDATE cascade, 
                CHECK ("default" IN (0, 1)), 
                CHECK (_stub IN (0, 1))
            );
            insert into temp_menu_combo_items 
            select distinct id, menu_item_id, pos_id, [default], price_per_unit, name, _stub, _registered_property_values
              from menu_combo_items;              
              
            drop table menu_combo_items;
            alter table temp_menu_combo_items rename to menu_combo_items;
            
            CREATE INDEX idx_menu_combo_item_menu_item_id ON menu_combo_items (menu_item_id);
            
            -- Re-create menu_combo_item_groups_menu_combo_items with FKs.
            CREATE TABLE temp_menu_combo_item_groups_menu_combo_items (
                menu_combo_item_group_id VARCHAR(64) NOT NULL, 
                menu_combo_item_id VARCHAR(64) NOT NULL,
                UNIQUE (menu_combo_item_group_id, menu_combo_item_id),
                CONSTRAINT fk_menu_combo_items_to_menu_combo_groups FOREIGN KEY(menu_combo_item_group_id) REFERENCES menu_combo_item_groups (id) ON DELETE cascade ON UPDATE cascade, 
                CONSTRAINT fk_menu_combo_groups_to_menu_combo_items FOREIGN KEY(menu_combo_item_id) REFERENCES menu_combo_items (id) ON DELETE cascade ON UPDATE cascade
            );
            
            insert into temp_menu_combo_item_groups_menu_combo_items (menu_combo_item_group_id, menu_combo_item_id)
            select menu_combo_item_group_id, menu_combo_item_id 
              from menu_combo_item_groups_menu_combo_items;
              
            drop table menu_combo_item_groups_menu_combo_items;
            alter table temp_menu_combo_item_groups_menu_combo_items rename to menu_combo_item_groups_menu_combo_items;
            
            CREATE INDEX idx_menu_combo_groups_to_menu_combo_items ON menu_combo_item_groups_menu_combo_items (menu_combo_item_id);
            CREATE INDEX idx_menu_combo_items_to_menu_combo_groups ON menu_combo_item_groups_menu_combo_items (menu_combo_item_group_id);
            
            -- Commit DDL changes.
            release down;
            """
        )
    finally:
        connection.execute('PRAGMA foreign_keys = "1";')

