"""Database level utility class.

Used for constructing and seeding the test Legacy SQLite database.
"""

from pricing import config
from pricing.connectors import legacy_mysql

from tests.utils.constants import TERRITORIES_TO_SEED

import sqlalchemy


CREATE_COUNTRY_TABLE = \
    'CREATE TABLE country ' \
    '(id int,' \
    'abbrivation varchar(50))'

CREATE_DMS_MASTER_PRICING_SCHEME_TABLE = 'CREATE TABLE ' \
                                         'dms_master_pricing_scheme ' \
                                         '(pricing_scheme_id int,' \
                                         'pricing_scheme varchar(50),' \
                                         'scheme_level varchar(50),' \
                                         'customer_master_master_id int,' \
                                         'product_type_id int,' \
                                         'default_pricing_tier int)'

CREATE_DMS_MASTER_PRICING_SCHEME_TERRITORY_TABLE = \
    'CREATE TABLE dms_master_pricing_scheme_territory ' \
    '(pricing_scheme_id int,' \
    'country_id int)'

CREATE_DMS_PRICING_TIER_TABLE = 'CREATE TABLE dms_pricing_tier ' \
                                '(pricing_tier_id int,' \
                                'pricing_scheme_id int,' \
                                'pricing_tier varchar(50),' \
                                'price_code varchar(50))'

CREATE_DMS_TERRITORY_CURRENCY_TABLE =\
    'CREATE TABLE dms_territory_currency ' \
    '(country_id int,' \
    'customer_master_master_id int,' \
    'currency_code varchar(50))'

CREATE_RELEASES_TABLE = 'CREATE TABLE releases ' \
                        '(release_id int, ' \
                        'product_type_id int, ' \
                        'product_subtype_id int)'

CREATE_RELEASE_PRICING_TIER_TABLE = 'CREATE TABLE release_pricing_tier ' \
                                    '(id int,' \
                                    'release_id int, ' \
                                    'pricing_tier_id int,' \
                                    'active varchar(1))'

CREATE_RELEASE_PRICING_TIER_COUNTRY_TABLE = \
    'CREATE TABLE release_pricing_tier_country ' \
    '(release_pricing_tier_id int,' \
    'country_id int)'

CREATE_RELEASE_CUSTOM_PRICING_TABLE = \
    'CREATE TABLE release_custom_pricing ' \
    '(release_pricing_tier_id int,' \
    'custom_price varchar(50),' \
    'currency_code varchar(50))'

CREATE_TRACK_TABLE = 'CREATE TABLE track ' \
                     '(id int, ' \
                     'release_id int, isrc varchar(30))'

CREATE_TRACK_PRICING_TIER_TABLE = 'CREATE TABLE track_pricing_tier ' \
                                  '(id int,' \
                                  'unique_track_id int, ' \
                                  'pricing_tier_id int,' \
                                  'active bool)'

CREATE_TRACK_PRICING_TIER_COUNTRY_TABLE = \
    'CREATE TABLE track_pricing_tier_country ' \
    '(track_pricing_tier_id int,' \
    'country_id int)'

CREATE_TRACK_CUSTOM_PRICING_TABLE = \
    'CREATE TABLE track_custom_pricing ' \
    '(track_pricing_tier_id int,' \
    'custom_price varchar(50),' \
    'currency_code varchar(50))'

DROP_COUNTRY_TABLE = 'DROP TABLE IF EXISTS country'

DROP_DMS_MASTER_PRICING_SCHEME_TABLE = \
    'DROP TABLE IF EXISTS dms_master_pricing_scheme'

DROP_DMS_MASTER_PRICING_SCHEME_TERRITORY_TABLE = \
    'DROP TABLE IF EXISTS dms_master_pricing_scheme_territory'

DROP_DMS_PRICING_TIER_TABLE = 'DROP TABLE IF EXISTS dms_pricing_tier'

DROP_DMS_TERRITORY_CURRENCY_TABLE = \
    'DROP TABLE IF EXISTS dms_territory_currency'

DROP_RELEASES_TABLE = 'DROP TABLE IF EXISTS releases'

DROP_RELEASE_PRICING_TIER_TABLE = 'DROP TABLE IF EXISTS release_pricing_tier'

DROP_RELEASE_PRICING_TIER_COUNTRY_TABLE = \
    'DROP TABLE IF EXISTS release_pricing_tier_country'

DROP_RELEASE_CUSTOM_PRICING_TABLE = \
    'DROP TABLE IF EXISTS release_custom_pricing'

DROP_TRACK_TABLE = 'DROP TABLE IF EXISTS track'

DROP_TRACK_PRICING_TIER_TABLE = 'DROP TABLE IF EXISTS track_pricing_tier'

DROP_TRACK_PRICING_TIER_COUNTRY_TABLE = \
    'DROP TABLE IF EXISTS track_pricing_tier_country'

DROP_TRACK_CUSTOM_PRICING_TABLE = \
    'DROP TABLE IF EXISTS track_custom_pricing'


def create_legacy_tables():
    """Create legacy tables."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

    if config.AR_DB_URL != 'sqlite://':
        return

    session = legacy_mysql.get_session()
    session.execute(sqlalchemy.text(DROP_COUNTRY_TABLE))
    session.execute(sqlalchemy.text(DROP_DMS_MASTER_PRICING_SCHEME_TABLE))
    session.execute(sqlalchemy.text(DROP_DMS_MASTER_PRICING_SCHEME_TERRITORY_TABLE))
    session.execute(sqlalchemy.text(DROP_DMS_PRICING_TIER_TABLE))
    session.execute(sqlalchemy.text(DROP_DMS_TERRITORY_CURRENCY_TABLE))
    session.execute(sqlalchemy.text(DROP_RELEASES_TABLE))
    session.execute(sqlalchemy.text(DROP_RELEASE_PRICING_TIER_TABLE))
    session.execute(sqlalchemy.text(DROP_RELEASE_PRICING_TIER_COUNTRY_TABLE))
    session.execute(sqlalchemy.text(DROP_RELEASE_CUSTOM_PRICING_TABLE))
    session.execute(sqlalchemy.text(DROP_TRACK_TABLE))
    session.execute(sqlalchemy.text(DROP_TRACK_PRICING_TIER_TABLE))
    session.execute(sqlalchemy.text(DROP_TRACK_PRICING_TIER_COUNTRY_TABLE))
    session.execute(sqlalchemy.text(DROP_TRACK_CUSTOM_PRICING_TABLE))

    session.execute(sqlalchemy.text(CREATE_COUNTRY_TABLE))
    session.execute(sqlalchemy.text(CREATE_DMS_MASTER_PRICING_SCHEME_TABLE))
    session.execute(sqlalchemy.text(CREATE_DMS_MASTER_PRICING_SCHEME_TERRITORY_TABLE))
    session.execute(sqlalchemy.text(CREATE_DMS_PRICING_TIER_TABLE))
    session.execute(sqlalchemy.text(CREATE_DMS_TERRITORY_CURRENCY_TABLE))
    session.execute(sqlalchemy.text(CREATE_RELEASES_TABLE))
    session.execute(sqlalchemy.text(CREATE_RELEASE_PRICING_TIER_TABLE))
    session.execute(sqlalchemy.text(CREATE_RELEASE_PRICING_TIER_COUNTRY_TABLE))
    session.execute(sqlalchemy.text(CREATE_RELEASE_CUSTOM_PRICING_TABLE))
    session.execute(sqlalchemy.text(CREATE_TRACK_TABLE))
    session.execute(sqlalchemy.text(CREATE_TRACK_PRICING_TIER_TABLE))
    session.execute(sqlalchemy.text(CREATE_TRACK_PRICING_TIER_COUNTRY_TABLE))
    session.execute(sqlalchemy.text(CREATE_TRACK_CUSTOM_PRICING_TABLE))

    territory_id = 1
    for territory in TERRITORIES_TO_SEED:
        seed_country(territory_id, territory)
        territory_id += 1


def seed_pricing_schemes(seed_input):
    """Seed pricing schemes from seed_input."""
    if config.AR_DB_URL != 'sqlite://':
        return

    for pricing_scheme in input['legacy_pricing_schemes']:
        seed_dms_master_pricing_scheme(pricing_scheme)


def seed_country(country_id, country_code):
    """Seed a row into the legacy table country."""
    if config.AR_DB_URL != 'sqlite://':
        return

    sql = 'INSERT INTO country (id, abbrivation) VALUES (:id, :abbrivation)'
    session = legacy_mysql.get_session()
    session.execute(sqlalchemy.text(sql), {
        'id': country_id,
        'abbrivation': country_code
    })
    session.commit()


def seed_dms_master_pricing_scheme(item):
    """Seed a row into the legacy table dms_master_pricing_scheme."""
    if config.AR_DB_URL != 'sqlite://':
        return

    sql = 'INSERT INTO dms_master_pricing_scheme ' \
          '(pricing_scheme_id, pricing_scheme, scheme_level, ' \
          'customer_master_master_id, product_type_id, ' \
          'default_pricing_tier) ' \
          'VALUES (:pricing_scheme_id, :pricing_scheme, ' \
          ':scheme_level, :store_id, :product_type_id, :default_pricing_tier)'

    session = legacy_mysql.get_session()

    session.execute(sqlalchemy.text(sql), {
        'pricing_scheme_id': item['pricing_scheme_id'],
        'pricing_scheme': item['pricing_scheme'],
        'scheme_level': item['scheme_level'],
        'store_id': item['store_id'],
        'product_type_id': item['product_type_id'],
        'default_pricing_tier': item['default_pricing_tier']
    })
    session.commit()

    for country_code in item['territories']:
        seed_dms_master_pricing_scheme_territory(
            item['pricing_scheme_id'], country_code)
    for pricing_tier in item['pricing_tiers']:
        seed_dms_pricing_tier(item['pricing_scheme_id'], pricing_tier)


def seed_dms_master_pricing_scheme_territory(pricing_scheme_id, country_code):
    """Seed a row into the legacy table dms_master_pricing_scheme_territory."""
    if config.AR_DB_URL != 'sqlite://':
        return

    sql = 'INSERT INTO dms_master_pricing_scheme_territory ' \
          '(pricing_scheme_id, country_id) ' \
          'SELECT :pricing_scheme_id, id FROM country ' \
          'WHERE abbrivation = :country_code'

    session = legacy_mysql.get_session()

    session.execute(sqlalchemy.text(sql), {
        'pricing_scheme_id': pricing_scheme_id,
        'country_code': country_code
    })
    session.commit()


def seed_dms_pricing_tier(
        pricing_scheme_id, pricing_tier):
    """Seed a row into the legacy table dms_pricing_tier."""
    sql = 'INSERT INTO dms_pricing_tier ' \
          '(pricing_scheme_id, pricing_tier_id, pricing_tier, price_code) ' \
          'VALUES (:pricing_scheme_id, :pricing_tier_id, :pricing_tier, :price_code)'
    session = legacy_mysql.get_session()
    session.execute(sqlalchemy.text(sql), {
        'pricing_scheme_id': pricing_scheme_id,
        'pricing_tier_id': pricing_tier['pricing_tier_id'],
        'pricing_tier': pricing_tier['pricing_tier'],
        'price_code': pricing_tier['price_code']
    })
    session.commit()


def seed_release(item):
    """Seed a row into the legacy table releases."""
    if config.AR_DB_URL != 'sqlite://':
        return

    sql = 'INSERT INTO releases (release_id, product_type_id) VALUES (:release_id, :product_type_id)'
    session = legacy_mysql.get_session()
    session.execute(sqlalchemy.text(sql), {
        'release_id': item['release_id'],
        'product_type_id': item['product_type_id']
    })
    session.commit()

    if 'release_pricing_tiers' in item:
        for release_pricing_tier in item['release_pricing_tiers']:
            seed_release_pricing_tier(
                item['release_id'], release_pricing_tier)

    if 'tracks' in item:
        for track in item['tracks']:
            seed_track(item['release_id'], track)


def seed_release_pricing_tier(release_id, item):
    """Seed a row into the legacy table release_pricing_tier."""
    if config.AR_DB_URL != 'sqlite://':
        return

    sql = 'INSERT INTO release_pricing_tier ' \
          '(release_id, id, pricing_tier_id, active) ' \
          'VALUES (:release_id, :id, :pricing_tier_id, :active)'
    session = legacy_mysql.get_session()
    session.execute(sqlalchemy.text(sql), {
        'release_id': release_id,
        'id': item['id'],
        'pricing_tier_id': item['pricing_tier_id'],
        'active': 'Y'
    })
    session.commit()

    for territory in item['territories']:
        seed_release_pricing_tier_country(
            item['id'], territory)

    if 'custom_price' in item:
        seed_release_custom_pricing(
            item['id'], item['custom_price'], item['currency_code'])


def seed_release_pricing_tier_country(release_pricing_tier_id, country_code):
    """Seed a row into the legacy table release_pricing_tier_country."""
    if config.AR_DB_URL != 'sqlite://':
        return

    sql = 'INSERT INTO release_pricing_tier_country ' \
          '(release_pricing_tier_id, country_id) ' \
          'SELECT :release_pricing_tier_id, id FROM ' \
          'country WHERE abbrivation = :country_code'
    session = legacy_mysql.get_session()
    session.execute(sqlalchemy.text(sql), {
        'release_pricing_tier_id': release_pricing_tier_id,
        'country_code': country_code
    })
    session.commit()


def seed_release_custom_pricing(
        release_pricing_tier_id, custom_price, currency_code):
    """Seed a row into the legacy table release_custom_pricing."""
    if config.AR_DB_URL != 'sqlite://':
        return

    sql = 'INSERT INTO release_custom_pricing ' \
          '(release_pricing_tier_id, custom_price, currency_code) ' \
          'VALUES (:release_pricing_tier_id, :custom_price, :currency_code)'
    session = legacy_mysql.get_session()
    session.execute(sqlalchemy.text(sql), {
        'release_pricing_tier_id': release_pricing_tier_id,
        'custom_price': custom_price,
        'currency_code': currency_code
    })
    session.commit()


def seed_track(release_id, track):
    """Seed a row into the legacy table track."""
    if config.AR_DB_URL != 'sqlite://':
        return

    sql = 'INSERT INTO track (release_id, id, isrc) VALUES (:release_id, :id, :isrc)'
    session = legacy_mysql.get_session()
    session.execute(sqlalchemy.text(sql), {
        'release_id': release_id,
        'id': track['id'],
        'isrc': track['isrc']
    })
    session.commit()

    if 'track_pricing_tiers' in track:
        for track_pricing_tier in track['track_pricing_tiers']:
            seed_track_pricing_tier(track['id'], track_pricing_tier)


def seed_track_pricing_tier(unique_track_id, item):
    """Seed a row into the legacy table track_pricing_tier."""
    if config.AR_DB_URL != 'sqlite://':
        return

    sql = 'INSERT INTO track_pricing_tier ' \
          '(id, unique_track_id, pricing_tier_id, active) ' \
          'VALUES (:id, :unique_track_id, :pricing_tier_id, :active)'
    session = legacy_mysql.get_session()
    session.execute(sqlalchemy.text(sql), {
        'id': item['id'],
        'unique_track_id': unique_track_id,
        'pricing_tier_id': item['pricing_tier_id'],
        'active': 'Y'
    })
    session.commit()

    for territory in item['territories']:
        seed_track_pricing_tier_country(
            item['id'], territory)

    if 'custom_price' in item:
        seed_track_custom_pricing(
            item['id'], item['custom_price'], item['currency_code'])


def seed_track_pricing_tier_country(track_pricing_tier_id, country_code):
    """Seed a row into the legacy table track_pricing_tier_country."""
    if config.AR_DB_URL != 'sqlite://':
        return

    sql = 'INSERT INTO track_pricing_tier_country ' \
          '(track_pricing_tier_id, country_id) ' \
          'SELECT :track_pricing_tier_id, id FROM country ' \
          'WHERE abbrivation = :country_code'
    session = legacy_mysql.get_session()
    session.execute(sqlalchemy.text(sql), {
        'track_pricing_tier_id': track_pricing_tier_id,
        'country_code': country_code
    })
    session.commit()


def seed_track_custom_pricing(
        track_pricing_tier_id, custom_price, currency_code):
    """Seed a row into the legacy table track_custom_pricing."""
    if config.AR_DB_URL != 'sqlite://':
        return

    sql = 'INSERT INTO track_custom_pricing ' \
          '(track_pricing_tier_id, custom_price, currency_code) ' \
          'VALUES (:track_pricing_tier_id, :custom_price, :currency_code)'
    session = legacy_mysql.get_session()
    session.execute(sqlalchemy.text(sql), {
        'track_pricing_tier_id': track_pricing_tier_id,
        'custom_price': custom_price,
        'currency_code': currency_code
    })
    session.commit()