"""db.py.

Database level utility class for testing against the tool_log schema.
"""

from datetime import datetime
from functools import wraps
import sys

from sqlalchemy.dialects.mysql import MEDIUMINT
from sqlalchemy.dialects.mysql import TINYINT
from sqlalchemy.ext.compiler import compiles

from prs import config
from prs.connectors.mysql import ar_model
from prs.connectors.mysql import ar_db_engine
from prs.connectors.mysql import ar_db_session
from prs.connectors.mysql import ppb_model
from prs.connectors.mysql import ppb_db_engine
from prs.connectors.mysql import ppb_db_session
from prs.connectors.mysql import ar_database_session
from prs.connectors.mysql import ppb_database_session
from prs.models import country
from prs.models import label_blacklist
from prs.models import label_delivery_history
from prs.models import label_territory_exception
from prs.models import rome_convention
from prs.models import tool_log

INSERT_COLLECTION_SOCIETY = """
    INSERT INTO collection_society (id, country_id, is_society)
    VALUES(1, 1, 1),(2, 2, 1),(3, 3, 0);
"""

DROP_TABLE_CURRENCIES = """
DROP TABLE currencies;
"""

CREATE_TABLE_CURRENCIES = """
CREATE TABLE currencies(id int(10), ISO_4217_code varchar(10))
"""

INSERT_INTO_TABLE_CURRENCIES = """
INSERT INTO currencies(id,ISO_4217_code)
VALUES(1,'USD'),(2,'GBR')
"""

DROP_TABLE_VW_LABEL_DETAIL = """
DROP TABLE vw_label_details;
"""

CREATE_TABLE_VW_LABEL_DETAIL = """
CREATE TABLE vw_label_details(vendor_id int(10),country varchar(255),
api_vendor_id int(10),vendor_name varchar(100),owner varchar(10),
label_country varchar(45),is_signed varchar(1),is_contract_active varchar(1),
is_owned_and_operated varchar(1),assigned_to varchar(71), email varchar(30),
last_delivery_date varchar(10),contact_email varchar(255),
is_distributor varchar(1), label_country_id int(5))
"""

INSERT_INTO_TABLE_VW_LABEL_DETAIL = """
INSERT INTO vw_label_details(
vendor_id,country,api_vendor_id,vendor_name,owner,label_country,is_signed,
is_contract_active,is_owned_and_operated,assigned_to,email,last_delivery_date,
contact_email,is_distributor, label_country_id
)
VALUES(547,10,NULL,'RIQUELME','DMGI','Argentina','Y','Y','N',
 '','email1@theorchard.com','NONE','','Y',1),
(548,8,NULL,'MICHAEL GEORGE','DMGI','Netherlands','Y','Y','N',
    'Artist/Label Relations','email2@theorchard.com','NONE','','Y',1),
(549,1,NULL,'PETE GEORGE','DMGI','USA','Y','Y','N','Artist/Label Relations',
 'email1@theorchard.com','NONE','','Y',1),
(551,1,NULL,'TARZAN','DMGI','USA','Y','Y','N','Artist/Label Relations',
 'email@theorchard.com','NONE','','Y',1),
(552,1,NULL,'DIANE RUSSEL','DMGI','USA','Y','Y','N','Artist/Label Relations',
    'email@theorchard.com','NONE','','Y',1),
(553,3,NULL,'ADADADADA','HMV','Canada','Y','Y','N','Artist/Label Relations',
    'email3@theorchard.com','NONE','','Y',3)
"""

DROP_TABLE_VW_ALL_VENDOR_CONTRACTS = """
DROP TABLE vw_all_vendor_contracts;
"""

CREATE_TABLE_VW_ALL_VENDOR_CONTRACTS = """
CREATE TABLE vw_all_vendor_contracts(id int(11),vendor_id int(10),
cont_start date,cont_end date,vcc2id int(11),
digital_split float,currency_id int(5),royalty_collection_territory text,
territory_carve_out text,royalty_collection_commission float,
sx_royalty_collection_commission float);
"""

INSERT_INTO_TABLE_VW_ALL_VENDOR_CONTRACTS = """
INSERT INTO vw_all_vendor_contracts(
id,vendor_id,cont_start,cont_end,vcc2id,digital_split,currency_id,
royalty_collection_territory,territory_carve_out,royalty_collection_commission,
sx_royalty_collection_commission)
VALUES(1,547,'2004-04-27','9999-12-31',NULL,0.5,1,'1,2,3,4,5','',0.0,'0.0'),
(2,548,'2004-08-10','9999-12-31',NULL,0.5,1,'1,2,6,7,8','',0.0,0.0),
(3,549,'2004-08-31','9999-12-31',NULL,0.5,1,'12,10,24,1,2','',0.0,0.0),
(4,551,'2004-06-25','9999-12-31',NULL,0.5,1,'45,12,7,8,1','',0.0,0.0);
"""

DROP_TABLE_VW_ACTIVE_VENDOR_CONTRACT = """
DROP TABLE vw_active_vendor_contract;
"""

CREATE_TABLE_VW_ACTIVE_VENDOR_CONTRACT = """
CREATE TABLE vw_active_vendor_contract(vendor_id int(10),vendor_contract_id
int(10))
"""

INSERT_INTO_TABLE_VW_ACTIVE_VENDOR_CONTRACT = """
INSERT INTO vw_active_vendor_contract(
vendor_id,vendor_contract_id) VALUES(547,1),(548,2),(549,3),(551,4),(552,5)"""

country_data = [
    {
        'country_id': 1,
        'name': 'USA'
    },
    {
        'country_id': 3,
        'name': 'UK'
    }
]

label_territory_exception_data = [
    {
        'label_id': 101,
        'country_id': 1
    },
    {
        'label_id': 102,
        'country_id': 1
    },
    {
        'label_id': 103,
        'country_id': 1
    },
]

label_blacklist_data = [
    {
        'bad_label_id': 1,
        'vendor_id': 101,
        'label_blacklist_type_id': 2
    },
    {
        'bad_label_id': 2,
        'vendor_id': 102,
        'label_blacklist_type_id': 2
    },
    {
        'bad_label_id': 3,
        'vendor_id': 103,
        'label_blacklist_type_id': 2
    },
    {
        'bad_label_id': 4,
        'vendor_id': 104,
        'label_blacklist_type_id': 8
    },
    {
        'bad_label_id': 5,
        'vendor_id': 105,
        'label_blacklist_type_id': 8
    },
    {
        'bad_label_id': 6,
        'vendor_id': 106,
        'label_blacklist_type_id': 8
    },
]


rome_convention_country_data = rome_convention_unsigned_data = [
    {
        'country_id': 1,
        'active': 1
    },
    {
        'country_id': 2,
        'active': 1
    },
    {
        'country_id': 3,
        'active': 0
    },
]

log_data = [
    {
        'tool_log_id': 1,
        'tool_name': 'Label Details',
        'society_id': 41,
        'time_start': datetime(2017, 7, 25, 14, 8, 17),
        'status': -1,
        'progress': 'Fetching eligible labels',
        'user_ip': '127.0.0.1'
    }
]

label_delivery_history_data = [
    {
        'label_id': 101,
        'society_id': 1,
        'last_delivery': datetime(2017, 9, 5, 5, 10, 10),
    },
    {
        'label_id': 101,
        'society_id': 2,
        'last_delivery': datetime(2017, 9, 5, 10, 20, 20),
    },
    {
        'label_id': 102,
        'society_id': 1,
        'last_delivery': datetime(2017, 9, 5, 10, 30, 30),
    },
    {
        'label_id': 102,
        'society_id': 1,
        'last_delivery': datetime(2017, 9, 10, 10, 40, 30),
    },
    {
        'label_id': 110,
        'society_id': 1,
        'last_delivery': datetime(2017, 9, 10, 10, 10, 10),
    },
]

label_delivery_history_response = [
    ('101', '2017-09-05'), ('102', '2017-09-05'),
    ('102', '2017-09-10'), ('110', '2017-09-10')]


'''
bulk_insert_list is a list of format:
[
    {
        'session': <db session>,
        'models': [
            {'model': <model class>, 'row_list_dicts': <model data>}, ..
        ]
    }, ..
]
'''
bulk_insert_list = [
    {
        'session': ar_db_session,
        'models': [
            {'model': country.Country, 'row_list_dicts': country_data},
        ]
    },
    {
        'session': ppb_db_session,
        'models': [
            {
                'model': label_territory_exception.LabelTerritoryException,
                'row_list_dicts': label_territory_exception_data
            },
            {
                'model': label_blacklist.LabelBlacklist,
                'row_list_dicts': label_blacklist_data
            },
            {
                'model': tool_log.ToolLog, 'row_list_dicts': log_data
            },
            {
                'model': rome_convention.RomeConventionCountry,
                'row_list_dicts': rome_convention_country_data
            },
            {
                'model': rome_convention.RomeConventionUnsigned,
                'row_list_dicts': rome_convention_unsigned_data
            },
            {
                'model': label_delivery_history.LabelDeliveryHistory,
                'row_list_dicts': label_delivery_history_data
            }
        ]
    }
]


def ppb_create_tables():
    """Create all the table for ppb_collection from metadata."""
    _exit_if_not_test_environment(ppb_database_session())
    ppb_model.metadata.create_all(ppb_db_engine)


def ppb_drop_tables():
    """Drop all the table for ppb_collection from metadata."""
    _exit_if_not_test_environment(ppb_database_session())
    ppb_model.metadata.drop_all(ppb_db_engine)


def ar_create_tables():
    """Create all the table for art_relations from metadata."""
    _exit_if_not_test_environment(ar_database_session())
    ar_model.metadata.create_all(ar_db_engine)


def ar_drop_tables():
    """Drop all the table for art_relations from metadata."""
    _exit_if_not_test_environment(ar_database_session())
    ar_model.metadata.drop_all(ar_db_engine)


def drop_table_raw(db_session, drop_table_query):
    """DROP command for table."""
    with db_session as session:
        _exit_if_not_test_environment(session)
        session.execute(drop_table_query)


def create_table_raw(db_session, create_table_query):
    """Create table."""
    with db_session as session:
        _exit_if_not_test_environment(session)
        session.execute(create_table_query)


def insert_to_table_raw(db_session, seed_table_query):
    """Insert to table."""
    with db_session as session:
        _exit_if_not_test_environment(session)
        session.execute(seed_table_query)


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 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. Individual 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):
        ppb_create_tables()
        ar_create_tables()
        create_table_raw(ar_db_session(), CREATE_TABLE_CURRENCIES)
        create_table_raw(ar_db_session(), CREATE_TABLE_VW_LABEL_DETAIL)
        create_table_raw(ar_db_session(), CREATE_TABLE_VW_ALL_VENDOR_CONTRACTS)
        create_table_raw(
            ar_db_session(), CREATE_TABLE_VW_ACTIVE_VENDOR_CONTRACT)

        bulk_insert_data()

        try:
            function_return = function(*args, **kwargs)
        finally:
            ppb_drop_tables()
            ar_drop_tables()
            drop_table_raw(ar_db_session(), DROP_TABLE_CURRENCIES)
            drop_table_raw(ar_db_session(), DROP_TABLE_VW_LABEL_DETAIL)
            drop_table_raw(ar_db_session(), DROP_TABLE_VW_ALL_VENDOR_CONTRACTS)
            drop_table_raw(
                ar_db_session(), DROP_TABLE_VW_ACTIVE_VENDOR_CONTRACT)

        return function_return
    return call_function_within_db_context


def bulk_insert_data():
    """Insert data in table(s) for given models in db session(s)."""
    for db in bulk_insert_list:
        db_session = db['session']
        model_list = db['models']
        with db_session() as session:
            for model in model_list:
                session.bulk_insert_mappings(
                    model['model'], model['row_list_dicts']
                )


@compiles(MEDIUMINT, 'sqlite')
def compile_mediumint(element, compiler, **kw):
    """Handle mysql MEDIUMINT datatype as Integer in sqlite."""
    return compiler.visit_integer(element, **kw)


@compiles(TINYINT, 'sqlite')
def compile_tinyint(element, compiler, **kw):
    """Handle mysql TINYINT datatype as Integer in sqlite."""
    return compiler.visit_integer(element, **kw)