"""db.py.

Database level utility class.
"""

from functools import wraps
import sys

from sqlalchemy import text

from product_configuration import config
from product_configuration.connectors.mysql import db_session


DROP_TABLE_DISTRIBUTION_FORMAT_MEDIA = """
DROP TABLE IF EXISTS distribution_format_media;
"""

CREATE_TABLE_DISTRIBUTION_FORMAT_MEDIA = """
CREATE TABLE `distribution_format_media` (
    `distribution_format_media_id` INTEGER PRIMARY KEY,
    `name` TEXT NOT NULL);
"""

INSERT_DISTRIBUTION_FORMAT_MEDIA_TYPES = """
INSERT INTO distribution_format_media (
    distribution_format_media_id,
    name
    ) VALUES
    (
        1,
        'Music Video'
    ),
    (
        2,
        '7" Vinyl'
    ),
    (
        3,
        '12" Vinyl'
    ),
    (
        4,
        'Cassette'
    )
"""

INSERT_DISTRIBUTION_FORMAT_MEDIA_FOR_STORE_DEFAULTS = """
INSERT INTO distribution_format_media (
    distribution_format_media_id,
    name
    ) VALUES
    (
        1,
        'Music Video'
    ),
    (
        2,
        '7" Vinyl'
    ),
    (
        3,
        '12" Vinyl'
    ),
    (
        4,
        'Cassette'
    ),
    (
        5,
        'Physical'
    )
"""

DROP_TABLE_DISTRIBUTION_FORMAT_MEDIA_FORMAT = """
DROP TABLE IF EXISTS distribution_format_media_format;
"""

CREATE_TABLE_DISTRIBUTION_FORMAT_MEDIA_FORMAT = """
CREATE TABLE `distribution_format_media_format` (
    `distribution_format_media_format_id` INTEGER PRIMARY KEY,
    `name` TEXT NOT NULL);
"""

INSERT_DISTRIBUTION_FORMAT_MEDIA_FORMATS = """
INSERT INTO distribution_format_media_format (
    distribution_format_media_format_id,
    name
    ) VALUES
    (
        1,
        'Single'
    ),
    (
        2,
        'EP'
    ),
    (
        3,
        'LP'
    )
"""

DROP_TABLE_DISTRIBUTION_FORMAT = """
DROP TABLE IF EXISTS distribution_format;
"""

CREATE_TABLE_DISTRIBUTION_FORMAT = """
CREATE TABLE `distribution_format` (
    `distribution_format_id` INTEGER PRIMARY KEY,
    `distribution_format_media_id` INTEGER DEFAULT NULL,
    `distribution_format_media_format_id` INTEGER DEFAULT NULL,
    `name` TEXT,
    `display_flag` TEXT NOT NULL,
    `context_type` TEXT NOT NULL);
"""

INSERT_DISTRIBUTION_FORMATS = """
INSERT INTO `distribution_format` (
    `distribution_format_id`,
    `distribution_format_media_id`,
    `distribution_format_media_format_id`,
    `name`,
    `display_flag`,
    `context_type`
    ) VALUES
    (1, 2, 1, 'a format', 'Y', 'physical'),
    (2, 2, 2, 'another format', 'Y', 'physical'),
    (3, 1, NULL, 'yet another format', 'Y', 'digital')
"""

DROP_TABLE_SUPPLY_CHAIN_DEFAULTS = """
DROP TABLE IF EXISTS supply_chain_defaults;
"""

CREATE_TABLE_SUPPLY_CHAIN_DEFAULTS = """
CREATE TABLE supply_chain_defaults (
    supply_chain_default_id INTEGER NOT NULL PRIMARY KEY,
    supply_chain_id INTEGER NOT NULL,
    distribution_format_media_id INTEGER NOT NULL,
    is_returnable TINYINT,
    return_disposition TEXT,
    date_added TIMESTAMP NULL DEFAULT TIMESTAMP,
    date_updated TIMESTAMP NULL DEFAULT TIMESTAMP
);
"""

INSERT_SUPPLY_CHAIN_DEFAULT_DATA = """
INSERT INTO supply_chain_defaults (
    supply_chain_default_id,
    supply_chain_id,
    distribution_format_media_id,
    is_returnable,
    return_disposition,
    date_added,
    date_updated
    )VALUES
    (
        1,
        738,
        5,
        1,
        'Keep',
        '2017-12-29 03:52:00',
        '2017-12-29 03:52:00'
    ),
    (
        2,
        738,
        1,
        1,
        'Scrap',
        '2017-12-29 03:52:00',
        '2017-12-29 03:52:00'
    ),
    (
        3,
        738,
        2,
        1,
        'Scrap',
        '2017-12-29 03:52:00',
        '2017-12-29 03:52:00'
    ),
    (
        4,
        738,
        3,
        1,
        'Scrap',
        '2017-12-29 03:52:00',
        '2017-12-29 03:52:00'
    ),
    (
        5,
        739,
        5,
        1,
        'Keep',
        '2017-12-29 03:52:00',
        '2017-12-29 03:52:00'
    )
"""

DROP_TABLE_CUSTOMER_MASTER_MASTER = """
DROP TABLE IF EXISTS customer_master_master;
"""

CREATE_TABLE_CUSTOMER_MASTER_MASTER = """
CREATE TABLE customer_master_master (
    customer_master_master_id INTEGER NOT NULL PRIMARY KEY,
    customer_name TEXT,
    sony_dms_code TEXT,
    ci_dms_code TEXT,
    delivery_option TEXT,
    show_delivery_info TEXT,
    product_type_id TEXT,
    delivery_cap INTEGER,
    weekly_limit INTEGER,
    eo_limit INTEGER,
    encoding_order_status TEXT,
    mv_delivery_option TEXT,
    ddex_party_id TEXT,
    optin_date TIMESTAMP,
    required_genre_code TEXT,
    required_subgenre_code TEXT,
    required_subgenre TEXT,
    instant_grat TEXT,
    hd_only TEXT,
    status TEXT,
    label_store TEXT
);
"""

INSERT_CUSTOMER_MASTER_MASTER_DATA = """
INSERT INTO customer_master_master (
    customer_master_master_id,
    customer_name,
    sony_dms_code,
    ci_dms_code,
    delivery_option,
    show_delivery_info,
    product_type_id,
    delivery_cap,
    weekly_limit,
    eo_limit,
    encoding_order_status,
    mv_delivery_option,
    ddex_party_id,
    optin_date,
    required_genre_code,
    required_subgenre_code,
    required_subgenre,
    instant_grat,
    hd_only,
    status,
    label_store

    )VALUES
    (
        738,
        'u.s',
        'sonycode',
        'dmscode',
        'cod',
        'sdi',
        'abs',
        1,
        5,
        4,
       'delievered',
       'cod',
       '123',
       '2017-12-29 03:52:00',
       'cod',
       'cod',
       'cod',
       'cod',
       'cod',
       'cod',
       'cod'
    ),
    (
        739,
        'u.s',
        'sonycode',
        'dmscode',
        'cod',
        'sdi',
        'abs',
        1,
        5,
        4,
       'delievered',
       'cod',
       '123',
       '2017-12-29 03:52:00',
       'cod',
       'cod',
       'cod',
       'cod',
       'cod',
       'cod',
       'cod'
    )
"""


def create_table_distribution_format_media():
    """Create `distribution_format_media` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(CREATE_TABLE_DISTRIBUTION_FORMAT_MEDIA))


def drop_table_distribution_format_media():
    """Drop `distribution_format_media` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(DROP_TABLE_DISTRIBUTION_FORMAT_MEDIA))


def insert_distribution_format_media_types():
    """Insert values for `distribution_format_media` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(INSERT_DISTRIBUTION_FORMAT_MEDIA_TYPES))


def create_table_distribution_format_media_format():
    """Create `distribution_format_media` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(CREATE_TABLE_DISTRIBUTION_FORMAT_MEDIA_FORMAT))


def drop_table_distribution_format_media_format():
    """Drop `distribution_format_media` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(DROP_TABLE_DISTRIBUTION_FORMAT_MEDIA_FORMAT))


def insert_distribution_format_media_formats():
    """Insert values for `distribution_format_media` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(INSERT_DISTRIBUTION_FORMAT_MEDIA_FORMATS))


def drop_table_distribution_format():
    """Drop `distribution_format` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(DROP_TABLE_DISTRIBUTION_FORMAT))


def create_table_distribution_format():
    """Create `distribution_format` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(CREATE_TABLE_DISTRIBUTION_FORMAT))


def insert_distribution_formats():
    """Insert values for `distribution_format` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(INSERT_DISTRIBUTION_FORMATS))


def drop_table_supply_chain_defaults():
    """Drop `supply_chain_defaults` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(DROP_TABLE_SUPPLY_CHAIN_DEFAULTS))


def create_table_supply_chain_defaults():
    """Create `supply_chain_defaults` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(CREATE_TABLE_SUPPLY_CHAIN_DEFAULTS))


def insert_distribution_format_media_for_defaults():
    """Insert values for `distribution_format_media` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(INSERT_DISTRIBUTION_FORMAT_MEDIA_FOR_STORE_DEFAULTS))


def insert_supply_chain_defaults_data():
    """Insert values for `supply_chain_defaults` table."""
    insert_customer_master_master_data()
    insert_distribution_format_media_for_defaults()
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(INSERT_SUPPLY_CHAIN_DEFAULT_DATA))


def drop_table_customer_master_master():
    """Drop `customer_master_master` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(DROP_TABLE_CUSTOMER_MASTER_MASTER))


def create_table_customer_master_master():
    """Create `customer_master_master` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(CREATE_TABLE_CUSTOMER_MASTER_MASTER))


def insert_customer_master_master_data():
    """Insert values for `customer_master_master` table."""
    with db_session() as session:
        _exit_if_not_test_environment()
        session.execute(text(INSERT_CUSTOMER_MASTER_MASTER_DATA))


def insert_distribution_format_data():
    """Util to insert necessary data for testing."""
    insert_distribution_format_media_types()
    insert_distribution_format_media_formats()
    insert_distribution_formats()


def test_schema(function):
    """Create and tear down the test DB schema around a function call.

    This just creates the schema and does not seed data. Indvidual test cases
    can use factories to seed data as needed.

    Args:
        Function (func): the function to be called after creating the test
        schema.

    Returns:
        Function: The decorated function.
    """
    @wraps(function)
    def call_function_within_db_context(*args, **kwargs):
        create_table_customer_master_master()
        create_table_supply_chain_defaults()
        create_table_distribution_format_media()
        create_table_distribution_format_media_format()
        create_table_distribution_format()

        try:
            function_return = function(*args, **kwargs)
        finally:
            drop_table_customer_master_master()
            drop_table_supply_chain_defaults()
            drop_table_distribution_format_media()
            drop_table_distribution_format_media_format()
            drop_table_distribution_format()

        return function_return
    return call_function_within_db_context


def _exit_if_not_test_environment():
    """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 config.DB_CONNECTION_URL:
        sys.exit('Tests must point to sqlite database.')