"""db.py.

Setup database testing structures for SQLlite which is used in testing env.

"""

import sys

from sqlalchemy import text

from ows_product_physical import config
from ows_product_physical.connector.mysql import db_session
from ows_product_physical.connector.mysql import delivery_db_session
from ows_product_physical.connector.mysql import DeliveryBaseModel
from ows_product_physical.connector.mysql import get_delivery_db_engine


def drop_product_physical_packaging_table():
    """DROP `product_physical_packaging`."""
    DROP_TABLE_PRODUCT_PHYSICAL_PACKAGING = text("""
    DROP TABLE IF EXISTS product_physical_packaging
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_TABLE_PRODUCT_PHYSICAL_PACKAGING)


def create_product_physical_packaging_table():
    """CREATE `product_physical_packaging`."""
    CREATE_TABLE_PRODUCT_PHYSICAL_PACKAGING = text("""
    CREATE TABLE `product_physical_packaging` (
      `id` INTEGER PRIMARY KEY,
      `name` VARCHAR(128) NOT NULL,
      `display_flag` TEXT DEFAULT 'Y');
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_product_physical_packaging_table()
        session.execute(CREATE_TABLE_PRODUCT_PHYSICAL_PACKAGING)


def drop_releases_table():
    """DROP `releases`."""
    DROP_TABLE_RELEASES = text("""
    DROP TABLE IF EXISTS releases
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_TABLE_RELEASES)


def create_releases_table():
    """CREATE `releases`."""
    CREATE_TABLE_RELEASES = text("""
    CREATE TABLE `releases` (
      `release_id` INTEGER PRIMARY KEY,
      `upc` INTEGER NOT NULL,
      `display_upc` TEXT DEFAULT NULL,
      `manufacturer_upc` TEXT DEFAULT NULL,
      `release_name` TEXT NOT NULL,
      `artist_id` INTEGER DEFAULT NULL,
      `subaccount_id` INTEGER DEFAULT NULL,
      `label` TEXT DEFAULT NULL,
      `release_date` TEXT DEFAULT NULL,
      `genre_id` INTEGER NOT NULL,
      `format` TEXT DEFAULT NULL,
      `new_release` TEXT DEFAULT NULL,
      `distribution_format_id` INTEGER DEFAULT NULL,
      `c_line` TEXT DEFAULT NULL,
      `sale_start_date` TEXT DEFAULT NULL,
      `product_code` TEXT DEFAULT NULL,
      `country_of_origin` INTEGER DEFAULT NULL,
      `version` TEXT DEFAULT NULL,
      `release_status` TEXT DEFAULT 'orchard_processing',
      `project_id` INTEGER DEFAULT NULL,
      `special_instructions` TEXT DEFAULT NULL,
      `description` TEXT DEFAULT NULL,
      `not_for_distribution` TEXT DEFAULT 'N',
      `vendor_catalog_number` TEXT DEFAULT NULL);
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_releases_table()
        session.execute(CREATE_TABLE_RELEASES)


def drop_release_status_table():
    """DROP `release_status`."""
    DROP_TABLE_RELEASE_STATUS = text("""
    DROP TABLE IF EXISTS release_status
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_TABLE_RELEASE_STATUS)


def create_release_status_table():
    """CREATE `release_status`."""
    CREATE_TABLE_RELEASES_STATUS = text("""
    CREATE TABLE `release_status` (
      `release_status_id` INTEGER PRIMARY KEY,
      `release_id` INTEGER NOT NULL,
      `status` TEXT NOT NULL,
      `date` TEXT NOT NULL,
      `changed_by` INTEGER NOT NULL,
      `changed_by_type` TEXT DEFAULT NULL,
      `upc_REMOVED` TEXT DEFAULT NULL);
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_release_status_table()
        session.execute(CREATE_TABLE_RELEASES_STATUS)


def drop_product_physical_table():
    """DROP `product_physical`."""
    DROP_TABLE_PRODUCT_PHYSICAL = text("""
    DROP TABLE IF EXISTS product_physical
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_TABLE_PRODUCT_PHYSICAL)


def create_product_physical_table():
    """CREATE `product_physical`."""
    CREATE_TABLE_PRODUCT_PHYSICAL = text("""
    CREATE TABLE `product_physical` (
      `id` INTEGER PRIMARY KEY,
      `release_id` INTEGER NOT NULL,
      `packaging_id` INTEGER DEFAULT NULL,
      `exclusive_for` TEXT DEFAULT NULL,
      `initial_stock` INTEGER DEFAULT NULL,
      `units_per_set` TINYINT(3) DEFAULT NULL,
      `box_lot` INTEGER DEFAULT NULL,
      `pricing` DECIMAL(6,2) DEFAULT NULL,
      `end_date` DATE DEFAULT NULL,
      `discount` TEXT DEFAULT NULL,
      `individual` TEXT DEFAULT NULL,
      `wholesale_price` DECIMAL(6,2) DEFAULT NULL,
      `explicit` TEXT DEFAULT NULL,
      `manufacturing_obligation` TEXT DEFAULT NULL,
      `pline` TEXT DEFAULT NULL,
      `production_notes` TEXT DEFAULT NULL,
      `display_configuration` TEXT DEFAULT NULL,
      `japan_distribution` TEXT DEFAULT 'no',
      `edition` TEXT DEFAULT NULL,
      FOREIGN KEY(release_id) REFERENCES releases(release_id)
    );
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_product_physical_table()
        session.execute(CREATE_TABLE_PRODUCT_PHYSICAL)


def drop_release_subgenre_table():
    """DROP `release_subgenre`."""
    DROP_TABLE_RELEASE_SUBGENRE = text("""
    DROP TABLE IF EXISTS release_subgenre
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_TABLE_RELEASE_SUBGENRE)


def create_release_subgenre_table():
    """CREATE `release_subgenre`."""
    CREATE_TABLE_RELEASE_SUBGENRE = text("""
    CREATE TABLE `release_subgenre` (
      `id` INTEGER PRIMARY KEY,
      `upc` INTEGER DEFAULT NULL,
      `subgenre_id` INTEGER NOT NULL,
      `release_id` INTEGER NOT NULL,
      FOREIGN KEY(release_id) REFERENCES releases(release_id));
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_release_subgenre_table()
        session.execute(CREATE_TABLE_RELEASE_SUBGENRE)


def drop_project_table():
    """DROP `project`."""
    DROP_TABLE_PROJECT = text("""
    DROP TABLE IF EXISTS project
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_TABLE_PROJECT)


def create_project_table():
    """CREATE `project`."""
    CREATE_TABLE_PROJECT = text("""
    CREATE TABLE `project` (
      `project_id` INTEGER PRIMARY KEY,
      `project_code` TEXT NOT NULL,
      `vendor_id` INTEGER NOT NULL,
      `subaccount_id` INTEGER NOT NULL,
      `project_name` TEXT NOT NULL,
      `created_date_utc` INTEGER NOT NULL,
      `updated_date_utc` INTEGER NOT NULL,
      `correlation_id` TEXT DEFAULT NULL);
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_project_table()
        session.execute(CREATE_TABLE_PROJECT)


def drop_delivery_history_table():
    """DROP `delivery_history`."""
    DROP_TABLE_DELIVERY_HISTORY = text("""
    DROP TABLE IF EXISTS delivery_history
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_TABLE_DELIVERY_HISTORY)


def create_delivery_history_table():
    """CREATE `delivery_history`."""
    CREATE_TABLE_DELIVERY_HISTORY = text("""
    CREATE TABLE `delivery_history` (
        `delivery_id` INTEGER PRIMARY KEY,
        `encoder_id` INTEGER NOT NULL,
        `upc` INTEGER NOT NULL,
        `customer_master_master_id` INTEGER NOT NULL,
        `date_delivered` DATE NOT NULL,
        `package_size` INTEGER NOT NULL);
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_delivery_history_table()
        session.execute(CREATE_TABLE_DELIVERY_HISTORY)


def create_all_delivery_tables():
    """CREATE all tables attached to delivery db engine."""
    with delivery_db_session() as session:
        _exit_if_not_test_environment(session)
        engine = get_delivery_db_engine()
        DeliveryBaseModel.metadata.drop_all(engine)
        DeliveryBaseModel.metadata.create_all(engine)


def populate_delivery_table(model, rows):
    """Populate tables under delivery db engine."""
    with delivery_db_session() as session:
        _exit_if_not_test_environment(session)
        for row in rows:
            new_order = model(**row)
            session.add(new_order)
        session.flush()


def populate_table(table_name, rows):
    """POPULATE `project`."""
    with db_session() as session:
        _exit_if_not_test_environment(session)
        for row in rows:
            columns = ', '.join(row.keys())
            values = ', '.join([':%s' % key for key in row.keys()])
            statement = 'INSERT INTO {} ( {} ) VALUES ( {} );'.format(
                table_name, columns, values)
            session.execute(text(statement), row)


def drop_release_artist_table():
    """DROP `release_artist`."""
    DROP_RELEASE_ARTIST_TABLE = text("""
    DROP TABLE IF EXISTS release_artist
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_RELEASE_ARTIST_TABLE)


def create_release_artist_table():
    """CREATE `release_artist`."""
    CREATE_RELEASE_ARTIST_TABLE = text("""
    CREATE TABLE release_artist (
        release_artist_id INTEGER PRIMARY KEY,
        upc INTEGER,
        release_id INTEGER,
        artist_name VARCHAR(255)
    )
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_release_artist_table()
        session.execute(CREATE_RELEASE_ARTIST_TABLE)


def create_track_table():
    """CREATE `track`."""
    CREATE_TRACK_TABLE = text("""
    CREATE TABLE track (
      id INTEGER PRIMARY KEY,
      release_id INTEGER DEFAULT NULL,
      track_name varchar(255) DEFAULT NULL,
      track_id INTEGER DEFAULT NULL,
      upc INTEGER DEFAULT NULL,
      isrc varchar(16) DEFAULT NULL,
      cd INTEGER DEFAULT NULL,
      length_minute INTEGER DEFAULT NULL,
      length_seconds INTEGER DEFAULT NULL,
      us_publishing_obligation varchar(30) DEFAULT NULL,
      third_party_publisher varchar(1) DEFAULT 'N'
    )
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_track_table()
        session.execute(CREATE_TRACK_TABLE)


def create_track_physical_table():
    """CREATE `track`."""
    CREATE_TRACK_PHYSICAL_TABLE = text("""
    CREATE TABLE track_physical (
      track_physical_id INTEGER PRIMARY KEY,
      track_id INTEGER DEFAULT NULL,
      side varchar(64) DEFAULT NULL,
      last_updated DATE
    )
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_track_physical_table()
        session.execute(CREATE_TRACK_PHYSICAL_TABLE)


def create_track_writer_table():
    """CREATE `track_writer`."""
    CREATE_TRACK_WRITER_TABLE = text("""
    CREATE TABLE track_writer (
      track_writer_id INTEGER PRIMARY KEY,
      writer_name VARCHAR(255) DEFAULT NULL,
      upc INTEGER DEFAULT NULL,
      cd INTEGER DEFAULT NULL,
      track_id INTEGER DEFAULT NULL,
      unique_track_id INTEGER DEFAULT NULL,
      FOREIGN KEY (unique_track_id) REFERENCES track(id)
    )
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_track_writer_table()
        session.execute(CREATE_TRACK_WRITER_TABLE)


def create_track_artist_table():
    """CREATE `track_artist`."""
    CREATE_TRACK_ARTIST_TABLE = text("""
    CREATE TABLE track_artist (
      id INTEGER PRIMARY KEY,
      track_id INTEGER DEFAULT NULL,
      name text DEFAULT NULL,
      type text DEFAULT NULL,
      FOREIGN KEY (track_id) REFERENCES track(id)
    )
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_track_artist_table()
        session.execute(CREATE_TRACK_ARTIST_TABLE)


def drop_track_table():
    """DROP `track_table`."""
    DROP_TRACK_TABLE = text("""
    DROP TABLE IF EXISTS track
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_TRACK_TABLE)


def drop_track_physical_table():
    """DROP `track_table`."""
    DROP_TRACK_PHYSICAL_TABLE = text("""
    DROP TABLE IF EXISTS track_physical
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_TRACK_PHYSICAL_TABLE)


def drop_track_writer_table():
    """DROP `track_writer table`."""
    DROP_TRACK_WRITER_TABLE = text("""
    DROP TABLE IF EXISTS track_writer
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_TRACK_WRITER_TABLE)


def drop_track_artist_table():
    """DROP `track_artist`."""
    DROP_TRACK_ARTIST_TABLE = text("""
    DROP TABLE IF EXISTS track_artist
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_TRACK_ARTIST_TABLE)


def drop_distribution_format_table():
    """Drop `distribution_format` table."""
    DROP_DISTRIBUTION_FORMAT_TABLE = text("""
    DROP TABLE IF EXISTS distribution_format;
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_DISTRIBUTION_FORMAT_TABLE)


def create_distribution_format_table():
    """Create `distribution_format` table."""
    CREATE_DISTRIBUTION_FORMAT_TABLE = text("""
    CREATE TABLE distribution_format (
        distribution_format_id INTEGER PRIMARY KEY,
        context_type VARCHAR(128)
    );
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_distribution_format_table()
        session.execute(CREATE_DISTRIBUTION_FORMAT_TABLE)


def drop_release_approval_queue_table():
    """Drop `release_approval_queue` table."""
    DROP_RELEASE_APPROVAL_QUEUE_TABLE = text("""
    DROP TABLE IF EXISTS release_approval_queue;
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_RELEASE_APPROVAL_QUEUE_TABLE)


def create_release_approval_queue_table():
    """Create `release_approval_queue` table."""
    CREATE_RELEASE_APPROVAL_QUEUE_TABLE = text("""
    CREATE TABLE release_approval_queue (
        release_approval_id INTEGER PRIMARY KEY,
        release_id INTEGER,
        admin_approval VARCHAR(1),
        status VARCHAR(16),
        last_updated VARCHAR(24),
        date_submitted VARCHAR(24),
        submitted_by INTEGER
    );
    """)
    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(CREATE_RELEASE_APPROVAL_QUEUE_TABLE)


def drop_track_publisher_table():
    """DROP `track_publisher`."""
    DROP_TRACK_PUBLISHER_TABLE = text("""
    DROP TABLE IF EXISTS track_publisher
    """)
    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_TRACK_PUBLISHER_TABLE)


def create_track_publisher_table():
    """CREATE `track_publisher`."""
    CREATE_TRACK_PUBLISHER_TABLE = text("""
    CREATE TABLE track_publisher (
        track_publisher_id INTEGER PRIMARY KEY,
        unique_track_id INTEGER DEFAULT NULL,
        upc INTEGER,
        publisher_name VARCHAR(255),
        FOREIGN KEY (unique_track_id) REFERENCES track(id)
    )
    """)
    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_track_publisher_table()
        session.execute(CREATE_TRACK_PUBLISHER_TABLE)


def _exit_if_not_test_environment(session):
    """For safety, only run tests in test environment pointed to sqlite.

    Exit immediately if not in test environment or not pointed to sqlite.
    """
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        sys.exit('Environment must be set to {}.'.format(
            config.TEST_ENVIRONMENT))
    if 'sqlite' not in session.bind.url.drivername:
        sys.exit('Tests must point to sqlite database.')


def create_product_physical_change_history():
    """CREATE `product_physical_change_history`."""
    CREATE_PRODUCT_HISTORY_CHANGE_TABLE = text("""
    CREATE TABLE product_physical_change_history (
       id INTEGER PRIMARY KEY,
       product_id INTEGER NOT NULL,
       field_name varchar(100) NOT NULL,
       store_id INTEGER DEFAULT NULL,
       new_price varchar(50) DEFAULT NULL,
       old_sale_start_date DATE DEFAULT NULL,
       new_sale_start_date DATE DEFAULT NULL,
       old_release_date DATE DEFAULT NULL,
       new_release_date DATE DEFAULT NULL,
       old_embargo_date DATE DEFAULT NULL,
       new_embargo_date DATE DEFAULT NULL,
       old_deletion_status varchar(100) DEFAULT NULL,
       new_deletion_status varchar(100) DEFAULT NULL,
       date_changed DATE NOT NULL,
       delivered TEXT DEFAULT NULL,
       artworkpath varchar(255) DEFAULT NULL
       )
    """)
    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_product_physical_change_history()
        session.execute(CREATE_PRODUCT_HISTORY_CHANGE_TABLE)


def drop_product_physical_change_history():
    """Drop `product_physical_change_history` table."""
    DROP_PRODUCT_HISTORY_CHANGE_TABLE = text("""
    DROP TABLE IF EXISTS product_physical_change_history;
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_PRODUCT_HISTORY_CHANGE_TABLE)


def create_product_physical_supply_chain_info():
    """CREATE `product_physical_change_info`."""
    CREATE_PRODUCT_PHYSICAL_SUPPLY_CHAIN_INFO_TABLE = text("""
    CREATE TABLE product_physical_supply_chain_info (
       id INTEGER PRIMARY KEY,
       product_id INTEGER NOT NULL,
       returnability varchar(100) NULL,
       return_disposition varchar(100) NULL,
       store_id INTEGER NOT NULL,
       updated_date DATE DEFAULT NULL
       )
    """)
    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_product_physical_supply_chain_info()
        session.execute(CREATE_PRODUCT_PHYSICAL_SUPPLY_CHAIN_INFO_TABLE)


INSERT_PRODUCT_PHYSICAL_SUPPLY_CHAIN_INFO = text("""
INSERT INTO product_physical_supply_chain_info (
    id,
    product_id,
    returnability,
    return_disposition,
    store_id,
    updated_date
    ) VALUES
    (
        1,
        123,
        'Y',
        'Keep',
        738,
        '2018-04-12 23:49:35'
    )
""")


def insert_product_physcial_supply_chain_info():
    """Insert values for `product_physical_supply_chain_info` table."""
    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(INSERT_PRODUCT_PHYSICAL_SUPPLY_CHAIN_INFO)


def drop_product_physical_supply_chain_info():
    """Drop `product_physical_change_info` table."""
    DROP_PRODUCT_PHYSICAL_SUPPLY_CHAIN_INFO_TABLE = text("""
    DROP TABLE IF EXISTS product_physical_supply_chain_info;
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_PRODUCT_PHYSICAL_SUPPLY_CHAIN_INFO_TABLE)


def create_product_physical_supply_chain_metadata():
    """CREATE `product_physical_change_metadata`."""
    CREATE_PRODUCT_PHYSICAL_SUPPLY_CHAIN_METADATA_TABLE = text("""
    CREATE TABLE product_physical_supply_chain_metadata (
        id INTEGER PRIMARY KEY,
        product_id INTEGER NOT NULL,
        store_id INTEGER NOT NULL,
        embargo_date varchar(100) NULL,
        release_date varchar(100) NULL,
        sale_start_date varchar(100) NULL,
        initial_stock INTEGER NULL,
        is_deleted varchar(100) NULL,
        date_added Date NOT NULL,
        date_updated Date NOT NULL
       );
    """)
    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_product_physical_supply_chain_metadata()
        session.execute(CREATE_PRODUCT_PHYSICAL_SUPPLY_CHAIN_METADATA_TABLE)


INSERT_PRODUCT_PHYSICAL_SUPPLY_CHAIN_METADATA = text("""
INSERT INTO product_physical_supply_chain_metadata (
    id,
    product_id,
    store_id,
    embargo_date,
    release_date,
    sale_start_date,
    is_deleted,
    date_added,
    date_updated
    ) VALUES
    (
        1,
        1900609,
        6,
        NULL,
        NULL,
        NULL,
        '0',
        '2018-09-02 00:00:00',
        '2018-09-02 00:00:00'
    )
""")


INSERT_SUPPLY_CHAIN_METADATA_WITH_SUPPLY_CHAIN_INFO = text("""
INSERT INTO product_physical_supply_chain_metadata (
    id,
    product_id,
    store_id,
    embargo_date,
    release_date,
    sale_start_date,
    initial_stock,
    is_deleted,
    date_added,
    date_updated
    ) VALUES
    (
        1,
        1900609,
        738,
        NULL,
        NULL,
        NULL,
        NULL,
        '0',
        '2018-09-02 00:00:00',
        '2018-09-02 00:00:00'
    )
""")

INSERT_SUPPLY_CHAIN_METADATA_WITH_INITIAL_STOCK = text("""
INSERT INTO product_physical_supply_chain_metadata (
    id,
    product_id,
    store_id,
    embargo_date,
    release_date,
    sale_start_date,
    initial_stock,
    is_deleted,
    date_added,
    date_updated
    ) VALUES
    (
        1,
        1900609,
        6,
        NULL,
        NULL,
        NULL,
        50,
        '0',
        '2018-09-02 00:00:00',
        '2018-09-02 00:00:00'
    )
""")

INSERT_DELETED_PRODUCT_PHYSICAL_SUPPLY_CHAIN_METADATA = text("""
INSERT INTO product_physical_supply_chain_metadata (
    id,
    product_id,
    store_id,
    embargo_date,
    release_date,
    sale_start_date,
    is_deleted,
    date_added,
    date_updated
    ) VALUES
    (
        1,
        1900609,
        6,
        NULL,
        NULL,
        NULL,
        1,
        '2018-09-02 00:00:00',
        '2018-09-02 00:00:00'
    )
""")


def insert_product_physcial_supply_chain_metadata():
    """Insert values for `product_physical_supply_chain_metadata` table."""
    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(INSERT_PRODUCT_PHYSICAL_SUPPLY_CHAIN_METADATA)


def insert_product_physcial_supply_chain_metadata_with_initial_stock():
    """Insert values for `product_physical_supply_chain_metadata` table."""
    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(INSERT_SUPPLY_CHAIN_METADATA_WITH_INITIAL_STOCK)


def insert_supply_chain_metadata_with_supply_chain_info():
    """Insert values for `product_physical_supply_chain_metadata` table."""
    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(INSERT_SUPPLY_CHAIN_METADATA_WITH_SUPPLY_CHAIN_INFO)


def insert_deleted_product_physcial_supply_chain_metadata():
    """Insert values for `product_physical_supply_chain_metadata` table."""
    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(INSERT_DELETED_PRODUCT_PHYSICAL_SUPPLY_CHAIN_METADATA)


def drop_product_physical_supply_chain_metadata():
    """Drop `product_physical_change_metadata` table."""
    DROP_PRODUCT_PHYSICAL_SUPPLY_CHAIN_METADATA = text("""
    DROP TABLE IF EXISTS product_physical_supply_chain_metadata;
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_PRODUCT_PHYSICAL_SUPPLY_CHAIN_METADATA)


def create_product_distribution():
    """CREATE `product_distribution`."""
    CREATE_PRODUCT_DISTRIBUTION_TABLE = text("""
    CREATE TABLE product_distribution (
        product_distribution_id INTEGER PRIMARY KEY,
        product_id INTEGER NOT NULL,
        distribute_to varchar(100) NOT NULL,
        last_updated datetime,
        updated_by INTEGER,
        user_type varchar(20)
       )
    """)
    with db_session() as session:
        _exit_if_not_test_environment(session)
        drop_product_distribution()
        session.execute(CREATE_PRODUCT_DISTRIBUTION_TABLE)


def drop_product_distribution():
    """Drop `product_distribution` table."""
    DROP_PRODUCT_DISTRIBUTION = text("""
    DROP TABLE IF EXISTS product_distribution;
    """)

    with db_session() as session:
        _exit_if_not_test_environment(session)
        session.execute(DROP_PRODUCT_DISTRIBUTION)