"""Utility functions to support database interactions in tests."""

from contextlib import contextmanager
from functools import wraps
import sys
from unittest import mock

from product_digital import config
from product_digital.connectors import mysql
from product_digital.models import mkt_priority

CREATE_LANGUAGE_TABLE = """
CREATE TABLE `language`
(
    `language_code` TEXT NOT NULL PRIMARY KEY,
    `language` TEXT NOT NULL,
    `iso_639_1_code` varchar(2) DEFAULT NULL,
    `iso_code_639_3_code` varchar(3) DEFAULT NULL,
    `apple_compatible` TINYINT(1) DEFAULT '1'
)
"""

DROP_LANGUAGE_TABLE = 'DROP TABLE IF EXISTS `language`'


CREATE_TRACK_TABLE = """
CREATE TABLE `track`
(
    `id` INTEGER NOT NULL PRIMARY KEY,
    `release_id` INTEGER NOT NULL,
    `meta_language` TEXT DEFAULT NULL
)
"""

DROP_TRACK_TABLE = 'DROP TABLE IF EXISTS `track`'


CREATE_RELEASE_ARTIST_TABLE = """
CREATE TABLE `release_artist`
(
    `release_artist_id` INTEGER NOT NULL PRIMARY KEY,
    `release_id` INTEGER NOT NULL,
    `upc` INTEGER NOT NULL,
    `role` TEXT NOT NULL,
    `artist_name` TEXT NOT NULL,
    `url` TEXT DEFAULT NULL,
    `artist_info_id` INTEGER
)
"""


DROP_RELEASE_ARTIST_TABLE = 'DROP TABLE IF EXISTS `release_artist`'


CREATE_RELEASES_TABLE = """
CREATE TABLE `releases`
(
    `release_id` INTEGER NOT NULL PRIMARY KEY,
    `upc` INTEGER NOT NULL,
    `project_id` INTEGER DEFAULT NULL,
    `product_code` INTEGER DEFAULT NULL,
    `delivered_version` TEXT DEFAULT NULL,
    `deletions` TEXT NOT NULL DEFAULT 'N',
    `distribution_format_id` INTEGER DEFAULT NULL,
    `artist_id` INTEGER DEFAULT NULL,
    `subaccount_id` INTEGER DEFAULT NULL,
    `release_name` TEXT DEFAULT NULL,
    `release_status` TEXT NOT NULL DEFAULT 'orchard_processsing',
    `label` TEXT DEFAULT NULL,
    `version` TEXT DEFAULT NULL,
    `genre_id` INTEGER DEFAULT NULL,
    `ingestion_completed` DATETIME DEFAULT NULL,
    `release_date` DATE DEFAULT NULL,
    `sale_start_date` DATE DEFAULT NULL,
    `preorder_date` DATE DEFAULT NULL,
    `format` TEXT DEFAULT NULL,
    `meta_language` TEXT DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `special_instructions` TEXT DEFAULT NULL,
    `manufacturer_upc` TEXT DEFAULT NULL,
    `itunes_previewable` TEXT DEFAULT NULL,
    `vendor_catalog_number` TEXT DEFAULT NULL,
    `product_type_id` INTEGER DEFAULT 1,
    `c_line` TEXT DEFAULT NULL,
    `display_upc` TEXT DEFAULT NULL,
    `not_for_distribution` TEXT DEFAULT NULL,
    `vendor_release_identifier` TEXT DEFAULT NULL,
    `p_line` TEXT DEFAULT NULL
)
"""

DROP_RELEASES_TABLE = 'DROP TABLE IF EXISTS `releases`'


CREATE_RELEASE_SUBGENRE_TABLE = """
CREATE TABLE `release_subgenre`
(
    `id` INTEGER NOT NULL PRIMARY KEY,
    `upc` INTEGER NOT NULL,
    `subgenre_id` INTEGER NOT NULL,
    `release_id` INTEGER NOT NULL
)
"""

DROP_RELEASE_SUBGENRE_TABLE = 'DROP TABLE IF EXISTS `release_subgenre`'


CREATE_RELEASE_STATUS_TABLE = """
CREATE TABLE `release_status`
(
    `release_status_id` INTEGER NOT NULL PRIMARY KEY,
    `release_id` INTEGER NOT NULL,
    `status` TEXT NOT NULL DEFAULT 'orchard processing',
    `date` DATE NOT NULL,
    `changed_by` INTEGER NOT NULL,
    `changed_by_type` TEXT NOT NULL
)
"""

DROP_RELEASE_STATUS_TABLE = 'DROP TABLE IF EXISTS `release_status`'


CREATE_RELEASE_APPROVAL_QUEUE_TABLE = """
CREATE TABLE `release_approval_queue`
(
    `release_approval_id` INTEGER NOT NULL PRIMARY KEY,
    `release_id` INTEGER NOT NULL,
    `status` TEXT NOT NULL,
    `admin_approval` VARCHAR(1),
    `last_updated` DATETIME NOT NULL,
    `date_submitted` DATETIME NOT NULL
)
"""


DROP_RELEASE_APPROVAL_QUEUE_TABLE = """
DROP TABLE IF EXISTS `release_approval_queue`
"""


CREATE_MKT_PRIORITY_TABLE = """
CREATE TABLE `mkt_priority`
(
    `id` INTEGER NOT NULL PRIMARY KEY,
    `upc` INTEGER NOT NULL,
    `priority` TEXT DEFAULT 'b',
    `admin_approval` VARCHAR(1),
    `country_id` INTEGER NOT NULL,
    `release_id` INTEGER NOT NULL,
    `created_by` INT(10) DEFAULT NULL,
    `updated_by` INT(10) DEFAULT NULL,
    `created_on` datetime DEFAULT NULL,
    `updated_on` datetime DEFAULT NULL
)
"""


DROP_MKT_PRIORITY_TABLE = """
DROP TABLE IF EXISTS `mkt_priority`
"""

CREATE_PRODUCT_PROVIDED_STORE_ARTISTS_TABLE = """
CREATE TABLE `product_provided_store_artists`
(
    `product_provided_store_artists_id` INTEGER NOT NULL PRIMARY KEY,
    `spotify` TEXT DEFAULT 'N',
    `release_id` INTEGER NOT NULL
)
"""


DROP_PRODUCT_PROVIDED_STORE_ARTISTS_TABLE = """
DROP TABLE IF EXISTS `product_provided_store_artists`
"""


CREATE_RELEASE_LOGGING_TABLE = """
CREATE TABLE `release_logging`
(
    `id` INTEGER NOT NULL PRIMARY KEY,
    `release_id` INTEGER NOT NULL,
    `changed_by_type` TEXT DEFAULT 'system',
    `changed_by` INTEGER NOT NULL DEFAULT 0,
    `updated_timestamp` DATETIME NOT NULL
)
"""


DROP_RELEASE_LOGGING_TABLE = """
DROP TABLE IF EXISTS `release_logging`
"""


CREATE_RELEASE_MANUAL_ADJUSTMENT_TABLE = """
CREATE TABLE `release_manual_adjustment`
(
    `id` INTEGER NOT NULL PRIMARY KEY,
    `date_created` DATETIME NOT NULL,
    `created_by` INTEGER NOT NULL,
    `category_id` INTEGER NOT NULL,
    `release_id` INTEGER NOT NULL,
    `amount` INTEGER NOT NULL,
    `currencies_id` INTEGER NOT NULL,
    `description` TEXT DEFAULT NULL,
    `vendor_manual_adjustment_id` INTEGER NOT NULL,
    `last_updated` DATETIME NOT NULL,
    `last_modified_by` INTEGER DEFAULT 179,
    `user_type` TEXT DEFAULT 'system'
)
"""


DROP_RELEASE_MANUAL_ADJUSTMENT_TABLE = """
DROP TABLE IF EXISTS `release_manual_adjustment`
"""


CREATE_RELEASE_SUBTITLES_TABLE = """
CREATE TABLE `release_subtitles`
(
    `id` INTEGER NOT NULL PRIMARY KEY,
    `release_id` INTEGER NOT NULL,
    `import_asset_id` INTEGER NOT NULL,
    `forced_subtitles` TEXT DEFAULT 'N',
    `file_type` TEXT NOT NULL,
    `language_tag` TEXT NOT NULL,
    `track_id` INTEGER NOT NULL,
    `status` TEXT NOT NULL,
    `last_updated` DATETIME NULL,
    `subtitle_type` TEXT DEFAULT 'feature'
)
"""


DROP_RELEASE_SUBTITLES_TABLE = """
DROP TABLE IF EXISTS `release_subtitles`
"""


CREATE_RELEASE_GRID_TABLE = """
CREATE TABLE `release_grid`
(
    `release_id` INTEGER NOT NULL PRIMARY KEY,
    `upc` INTEGER NULL,
    `grid` TEXT NULL,
    `product_no` TEXT NOT NULL,
    `create_time` DATETIME NOT NULL,
    `gras_status` TEXT DEFAULT 'unknown'
)
"""


DROP_RELEASE_GRID_TABLE = """
DROP TABLE IF EXISTS `release_grid`
"""


CREATE_REJECTION_NOTES_TABLE = """
CREATE TABLE `rejection_notes`
(
    `rejection_id` INTEGER NOT NULL PRIMARY KEY,
    `release_approval_id` INTEGER NOT NULL,
    `table_name` TEXT NOT NULL,
    `field_name` TEXT NULL,
    `comments` TEXT NOT NULL,
    `corrected` TEXT NOT NULL,
    `date_added` DATETIME NOT NULL,
    `key_id` INTEGER NULL,
    `changed_by` INTEGER NULL,
    `changed_by_type` TEXT NULL
)
"""


DROP_REJECTION_NOTES_TABLE = """
DROP TABLE IF EXISTS `rejection_notes`
"""


CREATE_RELEASE_SPATIAL_TABLE = """
CREATE TABLE `release_spatial`
(
    `release_id` INTEGER NOT NULL PRIMARY KEY,
    `upc` BIGINT NOT NULL UNIQUE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP
)
"""

DROP_RELEASE_SPATIAL_TABLE = """
DROP TABLE IF EXISTS `release_spatial`
"""


CREATE_CATALOG_TRANSFERS_TABLE = """
CREATE TABLE `catalog_transfers`
(
    `release_id` INTEGER NOT NULL PRIMARY KEY,
    `original_distributor` TEXT NOT NULL,
    `same_isrc_upc` TEXT NOT NULL
)
"""

DROP_CATALOG_TRANSFERS_TABLE = """
DROP TABLE IF EXISTS `catalog_transfers`
"""


CREATE_PRODUCT_ADDITIONAL_UPC_TABLE = """
CREATE TABLE `product_additional_upc`
(
    `id` INTEGER NOT NULL PRIMARY KEY,
    `product_id` INTEGER NOT NULL,
    `type` TEXT NOT NULL,
    `upc` BIGINT NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL,
    UNIQUE(`upc`),
    UNIQUE(`product_id`, `type`)
)
"""

DROP_PRODUCT_ADDITIONAL_UPC_TABLE = """
DROP TABLE IF EXISTS `product_additional_upc`
"""

CREATE_DELIVERY_RESTRICTIONS_REQUESTS_TABLE = """
CREATE TABLE `delivery_restrictions_requests`
(
    `release_id` INTEGER NOT NULL PRIMARY KEY,
    `delivery_requests` TEXT NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP
)
"""

DROP_DELIVERY_RESTRICTIONS_REQUESTS_TABLE = """
DROP TABLE IF EXISTS `delivery_restrictions_requests`
"""

def execute_sql(*args):
    """Run a series of queries safely within the test environment."""
    with mysql.db_session() as session:
        _exit_if_not_test_environment(session)

        for query in args:
            session.execute(query)


def seed_models(models):
    """Save the given model(s) to the DB.

    Args:
        models (list): list of model instances to save.
    """
    if not hasattr(models, '__iter__'):
        models = [models]

    with mysql.db_session() as session:
        _exit_if_not_test_environment(session)
        for model in models:
            session.add(model)
        session.flush()

        # detach the objects from this session so tests can interrogate them
        for model in models:
            session.expunge(model)


def test_schema(function):
    """Test schema.

    Decorator that creates the test DB schema before a function call and
    tears the schema down after the function call has finished.

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

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

    Returns:
        Function: The decorated function.
    """
    @wraps(function)
    def call_function_within_db_context(*args, **kwargs):
        execute_sql(
            CREATE_LANGUAGE_TABLE,
            CREATE_RELEASE_ARTIST_TABLE,
            CREATE_TRACK_TABLE,
            CREATE_RELEASES_TABLE,
            CREATE_RELEASE_SUBGENRE_TABLE,
            CREATE_RELEASE_STATUS_TABLE,
            CREATE_RELEASE_APPROVAL_QUEUE_TABLE,
            CREATE_MKT_PRIORITY_TABLE,
            CREATE_PRODUCT_PROVIDED_STORE_ARTISTS_TABLE,
            CREATE_RELEASE_LOGGING_TABLE,
            CREATE_RELEASE_MANUAL_ADJUSTMENT_TABLE,
            CREATE_RELEASE_SUBTITLES_TABLE,
            CREATE_RELEASE_GRID_TABLE,
            CREATE_REJECTION_NOTES_TABLE,
            CREATE_RELEASE_SPATIAL_TABLE,
            CREATE_CATALOG_TRANSFERS_TABLE,
            CREATE_PRODUCT_ADDITIONAL_UPC_TABLE,
            CREATE_DELIVERY_RESTRICTIONS_REQUESTS_TABLE
        )

        try:
            function_return = function(*args, **kwargs)
        finally:
            execute_sql(
                DROP_LANGUAGE_TABLE,
                DROP_RELEASE_ARTIST_TABLE,
                DROP_RELEASES_TABLE,
                DROP_RELEASE_SUBGENRE_TABLE,
                DROP_RELEASE_STATUS_TABLE,
                DROP_RELEASE_APPROVAL_QUEUE_TABLE,
                DROP_TRACK_TABLE,
                DROP_MKT_PRIORITY_TABLE,
                DROP_PRODUCT_PROVIDED_STORE_ARTISTS_TABLE,
                DROP_RELEASE_LOGGING_TABLE,
                DROP_RELEASE_MANUAL_ADJUSTMENT_TABLE,
                DROP_RELEASE_SUBTITLES_TABLE,
                DROP_RELEASE_GRID_TABLE,
                DROP_REJECTION_NOTES_TABLE,
                DROP_RELEASE_SPATIAL_TABLE,
                DROP_CATALOG_TRANSFERS_TABLE,
                DROP_PRODUCT_ADDITIONAL_UPC_TABLE,
                DROP_DELIVERY_RESTRICTIONS_REQUESTS_TABLE
            )

        return function_return
    return call_function_within_db_context


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 mock_db_session(mocker):
    """Create a mock database sesssion.

    Also mock the db_session context manager to use the mock session.
    """
    mock_session = mock.Mock(query=mock.Mock())

    @contextmanager
    def fake_session_manager():
        yield mock_session

    mocker.patch.object(mysql, 'db_session', fake_session_manager)

    return mock_session


INSERT_DATA = [
    {
        'id': 1155,
        'upc': 829410910552,
        'priority': 'a',
        'country_id': 0,
        'release_id': 92949
    },
    {
        'id': 1122,
        'upc': 829410750950,
        'priority': 'a',
        'country_id': 0,
        'release_id': 92949
    },
    {
        'id': 1166,
        'upc': 829410910553,
        'priority': 'b',
        'country_id': 1,
        'release_id': 92950
    },
    {
        'id': 1177,
        'upc': 829410750954,
        'priority': 'a',
        'country_id': 2,
        'release_id': 92951
    }
]


def insert_mkt_priority_data():
    """Insert data to mkt_priority table."""
    with mysql.db_session() as session:
        session.bulk_insert_mappings(
            mkt_priority.MktPriority, INSERT_DATA)