"""add_open_name_to_menu_items

Revision ID: c6febc7d4882
Revises: d7d924a6a31f
Create Date: 2020-10-01 13:59:29.725000

"""
from alembic import op
import sqlalchemy as sa


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

first_agent_version = '20.11.2'


def upgrade():
    connection = op.get_bind().engine.raw_connection()
    try:
        connection.executescript('PRAGMA foreign_keys = "0";')
        connection.executescript(
            """
            savepoint up;

            CREATE TABLE temp_menu_items (
                id VARCHAR(64) NOT NULL,
                pos_id VARCHAR(64),
                name VARCHAR(128),
                price_per_unit INTEGER,
                open BOOLEAN,
                open_name BOOLEAN,
                in_stock BOOLEAN,
                _stub BOOLEAN,
                _registered_property_values BLOB,
                PRIMARY KEY (id),
                CHECK (open IN (0, 1)),
                CHECK (open_name IN (0, 1)),
                CHECK (in_stock IN (0, 1)),
                CHECK (_stub IN (0, 1))
            );
            
            INSERT INTO temp_menu_items (id, pos_id, name, price_per_unit, open, in_stock, _stub, _registered_property_values)
                SELECT id, pos_id, name, price_per_unit, open, in_stock, _stub, _registered_property_values
                FROM menu_items;
            UPDATE temp_menu_items SET open_name=0;
            DROP TABLE menu_items;
            ALTER TABLE temp_menu_items RENAME to menu_items;

            release up;
            """
        )

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


def downgrade():
    connection = op.get_bind().engine.raw_connection()
    try:
        connection.executescript('PRAGMA foreign_keys = "0";')
        connection.executescript(
            """
            savepoint down;
            CREATE TABLE temp_menu_items (
                id VARCHAR(64) NOT NULL,
                pos_id VARCHAR(64),
                name VARCHAR(128),
                price_per_unit INTEGER,
                open BOOLEAN,
                in_stock BOOLEAN,
                _stub BOOLEAN,
                _registered_property_values BLOB,
                PRIMARY KEY (id),
                CHECK (open IN (0, 1)),
                CHECK (in_stock IN (0, 1)),
                CHECK (_stub IN (0, 1))
            );
            
            INSERT INTO temp_menu_items (id, pos_id, name, price_per_unit, open, in_stock, _stub, _registered_property_values)
                SELECT id, pos_id, name, price_per_unit, open, in_stock, _stub, _registered_property_values
                FROM menu_items;
            DROP TABLE menu_items;
            ALTER TABLE temp_menu_items RENAME TO menu_items;

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