"""add_send_items_candidate_items

Revision ID: 5fba58ed286f
Revises: 059619ce78d0
Create Date: 2020-08-12 09:28:24.238000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '5fba58ed286f'
down_revision = '059619ce78d0'
branch_labels = None
depends_on = None

# TODO: Set the first_agent_version correctly before merging this revision
first_agent_version = '20.8.12'


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

        connection.executescript(
            """
            savepoint up;
            CREATE INDEX "idx-send_items_candidates-id" ON send_items_candidates (id);
            
            CREATE TABLE send_items_candidate_items (
                "key" INTEGER NOT NULL, 
                send_items_candidate_id INTEGER NOT NULL, 
                ticket_item_id VARCHAR(64) NOT NULL, 
                item_order_mode_id VARCHAR(64), 
                PRIMARY KEY ("key"), 
                CONSTRAINT "fk-send_items_candidate_items-send_items_candidates" FOREIGN KEY(send_items_candidate_id)
                    REFERENCES send_items_candidates (id) ON DELETE cascade ON UPDATE cascade
            );
            CREATE INDEX "idx-send_items_candidate_items-ticket_item_id" ON send_items_candidate_items (ticket_item_id);
            
            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;
            
            DROP TABLE send_items_candidate_items;
            DROP INDEX "idx-send_items_candidates-id";
            
            release down;
            """
        )
    finally:
        connection.execute('PRAGMA foreign_keys = "1";')
