"""Database level utility class.

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

import sys

from pricing import config
from pricing.connectors import mysql
from pricing.models import default_account_pricing_tier
from pricing.models import orchard_pricing_tier
from pricing.models import pricing_family
from pricing.models import product_migration_status
from pricing.models import product_orchard_pricing_tier
from pricing.models import product_pricing_override
from pricing.models import product_pricing_override_store
from pricing.models import product_pricing_override_territory
from pricing.models import product_store_effective_price_code
from pricing.models import store_pricing_scheme
from pricing.models import store_pricing_tier
from pricing.models import store_pricing_tier_code
from pricing.models import store_pricing_tier_code_territory
from pricing.models import track_pricing_override
from pricing.models import track_pricing_override_store
from pricing.models import track_pricing_override_territory
from pricing.models import orchard_pricing_tier_value

import sqlalchemy


pricing_family_data = {
    'name': 'Film',
    'pricing_family_id': 1
}

orchard_pricing_tier_data = [
    {
        'orchard_pricing_tier_id': 1,
        'name': 'Budget',
        'pricing_family_id': 1,
        'sort_order': 3
    },
    {
        'orchard_pricing_tier_id': 2,
        'name': 'Premium',
        'pricing_family_id': 1,
        'sort_order': 1
    },
    {
        'orchard_pricing_tier_id': 3,
        'name': 'Mid',
        'pricing_family_id': 1,
        'sort_order': 2
    }
]

store_pricing_scheme_data = [
    {
        'store_pricing_scheme_id': 1,
        'name': 'iTunes Film',
        'store_id': 1,
        'pricing_family_id': 1
    }
]

store_pricing_tier_data = [
    {
        'store_pricing_tier_id': 1,
        'name': 'Frontline',
        'store_pricing_scheme_id': 1,
        'orchard_pricing_tier_id': 2
    },
    {
        'store_pricing_tier_id': 2,
        'name': 'Medium',
        'store_pricing_scheme_id': 1,
        'orchard_pricing_tier_id': 3
    },
    {
        'store_pricing_tier_id': 3,
        'name': 'Budget',
        'store_pricing_scheme_id': 1,
        'orchard_pricing_tier_id': 1
    }
]

store_pricing_tier_code_data = [
    {
        'store_pricing_tier_code_id': 1,
        'store_pricing_tier_id': 1,
        'price_code': '100',
        'resolution': '4K',
        'applies_worldwide': False,
        'territory_list_include': True
    },
    {
        'store_pricing_tier_code_id': 2,
        'store_pricing_tier_id': 1,
        'price_code': '100',
        'resolution': '4K',
        'applies_worldwide': False,
        'territory_list_include': False
    },
    {
        'store_pricing_tier_code_id': 3,
        'store_pricing_tier_id': 1,
        'price_code': '101',
        'resolution': 'HD',
        'applies_worldwide': True,
        'territory_list_include': False
    },
    {
        'store_pricing_tier_code_id': 4,
        'store_pricing_tier_id': 1,
        'price_code': '102',
        'resolution': 'SD',
        'applies_worldwide': True,
        'territory_list_include': False
    },
    {
        'store_pricing_tier_code_id': 5,
        'store_pricing_tier_id': 2,
        'price_code': '200',
        'resolution': 'All',
        'applies_worldwide': True,
        'territory_list_include': False
    },
    {
        'store_pricing_tier_code_id': 6,
        'store_pricing_tier_id': 3,
        'price_code': '300',
        'resolution': 'All',
        'applies_worldwide': True,
        'territory_list_include': False
    }
]

store_pricing_tier_code_territory_data = [
    {
        'store_pricing_tier_code_id': 1,
        'territory_code': 'US'
    },
    {
        'store_pricing_tier_code_id': 1,
        'territory_code': 'CA'
    },
    {
        'store_pricing_tier_code_id': 2,
        'territory_code': 'US'
    },
    {
        'store_pricing_tier_code_id': 2,
        'territory_code': 'CA'
    }
]

product_orchard_pricing_tier_data = [
    {
        'product_orchard_pricing_tier_id': 1,
        'orchard_pricing_tier_id': 1,
        'product_id': 1,
        'pricing_family_id': 1
    },
    {
        'product_orchard_pricing_tier_id': 2,
        'orchard_pricing_tier_id': 2,
        'product_id': 2,
        'pricing_family_id': 1
    },
    {
        'product_orchard_pricing_tier_id': 3,
        'orchard_pricing_tier_id': 3,
        'product_id': 3,
        'pricing_family_id': 1
    },
    {
        'product_orchard_pricing_tier_id': 4,
        'orchard_pricing_tier_id': 2,
        'product_id': 4,
        'pricing_family_id': 2
    },
    {
        'product_orchard_pricing_tier_id': 5,
        'orchard_pricing_tier_id': 3,
        'product_id': 4,
        'pricing_family_id': 3
    }
]

product_pricing_override_data = [
    {
        'product_pricing_override_id': 1,
        'product_id': 1,
        'resolution': 'HD',
        'applies_worldwide': True,
        'store_id': 1,
        'orchard_pricing_tier_id': 3
    },
    {
        'product_pricing_override_id': 2,
        'product_id': 2,
        'resolution': 'SD',
        'applies_worldwide': True,
        'store_id': 1,
        'orchard_pricing_tier_id': 2
    },
    {
        'product_pricing_override_id': 3,
        'product_id': 1,
        'resolution': 'HD',
        'applies_worldwide': True,
        'store_id': 2,
        'orchard_pricing_tier_id': 1
    }
]


product_pricing_override_territory_data = [
    {
        'product_pricing_override_id': 1,
        'territory_code': 'US'
    },
    {
        'product_pricing_override_id': 1,
        'territory_code': 'FR'
    },
    {
        'product_pricing_override_id': 2,
        'territory_code': 'GB'
    }
]

product_pricing_override_store_data = [
    {
        'product_pricing_override_id': 1,
        'store_id': 1
    },
    {
        'product_pricing_override_id': 1,
        'store_id': 2
    },
    {
        'product_pricing_override_id': 2,
        'store_id': 1
    },
    {
        'product_pricing_override_id': 2,
        'store_id': 3
    }
]

track_pricing_override_data = [
    {
        'track_pricing_override_id': 1,
        'track_id': 1,
        'applies_worldwide': True,
        'orchard_pricing_tier_id': 3
    },
    {
        'track_pricing_override_id': 2,
        'track_id': 2,
        'applies_worldwide': True,
        'orchard_pricing_tier_id': 2
    },
    {
        'track_pricing_override_id': 3,
        'track_id': 1,
        'applies_worldwide': True,
        'orchard_pricing_tier_id': 1
    }
]

track_pricing_override_territory_data = [
    {
        'track_pricing_override_id': 1,
        'territory_code': 'US'
    },
    {
        'track_pricing_override_id': 1,
        'territory_code': 'FR'
    },
    {
        'track_pricing_override_id': 2,
        'territory_code': 'US'
    },
    {
        'track_pricing_override_id': 2,
        'territory_code': 'CA'
    }
]

track_pricing_override_store_data = [
    {
        'track_pricing_override_id': 1,
        'store_id': 1
    },
    {
        'track_pricing_override_id': 1,
        'store_id': 2
    },
    {
        'track_pricing_override_id': 2,
        'store_id': 1
    },
    {
        'track_pricing_override_id': 2,
        'store_id': 3
    }
]

product_migration_status_data = [
    {
        'product_migration_status_id': 1,
        'product_id': 1,
        'migration_status': 'NeedPhase1Migration'
    },
    {
        'product_migration_status_id': 2,
        'product_id': 2,
        'migration_status': 'Phase1MigrationDone'
    }
]

default_account_pricing_tier_data = [
    {
        'default_pricing_tier_id': 1,
        'account_type': 'vendor',
        'account_id': 1,
        'orchard_pricing_tier_id': 1,
        'pricing_family_id': 1,
        'active': 1
    },
    {
        'default_pricing_tier_id': 2,
        'account_type': 'vendor',
        'account_id': 1,
        'orchard_pricing_tier_id': 2,
        'pricing_family_id': 2,
        'active': 1
    },
    {
        'default_pricing_tier_id': 3,
        'account_type': 'vendor',
        'account_id': 2,
        'orchard_pricing_tier_id': 3,
        'pricing_family_id': 1,
        'active': 1
    }
]

product_store_effective_price_code_data = [
    {
        'product_store_effective_price_code_id': 1,
        'product_id': 100,
        'store_id': 696,
        'price_code': 'CD11',
        'store_pricing_tier_name': 'CD Tier 1'
    }
]

orchard_pricing_tier_value_data = [
    {
        'orchard_pricing_tier_value_id': 1,
        'orchard_pricing_tier_id': 1,
        'value_cents': 1599,
        'country_code': 'US'
    },
    {
        'orchard_pricing_tier_value_id': 2,
        'orchard_pricing_tier_id': 2,
        'value_cents': 1499,
        'country_code': 'US'
    },
    {
        'orchard_pricing_tier_value_id': 3,
        'orchard_pricing_tier_id': 3,
        'value_cents': 99,
        'country_code': 'US'
    }
]


def create_tables():
    """Create tables defined in pricing.models."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

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

    mysql.BaseModel.metadata.drop_all(mysql._db_engine)
    mysql.BaseModel.metadata.create_all(mysql._db_engine)


@mysql.autosession()
def seed_data(session):
    """Insert test data into pricing_family table."""
    if config.ENVIRONMENT != config.TEST_ENVIRONMENT:
        return

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

    try:
        session.execute(pricing_family.PricingFamily.__table__.insert().values(
            pricing_family_data))
        session.execute(orchard_pricing_tier.OrchardPricingTier.
                        __table__.insert().values(orchard_pricing_tier_data))
        session.execute(store_pricing_scheme.StorePricingScheme.
                        __table__.insert().values(store_pricing_scheme_data))
        session.execute(store_pricing_tier.StorePricingTier.
                        __table__.insert().values(store_pricing_tier_data))
        session.execute(store_pricing_tier_code.StorePricingTierCode.
                        __table__.insert().values(
                            store_pricing_tier_code_data))
        session.execute(
            store_pricing_tier_code_territory.StorePricingTierCodeTerritory.
            __table__.insert().values(
                store_pricing_tier_code_territory_data))

        session.execute(product_orchard_pricing_tier.ProductOrchardPricingTier.
                        __table__.insert().values(
                            product_orchard_pricing_tier_data))
        session.execute(
            product_pricing_override.ProductPricingOverride.
            __table__.insert().values(product_pricing_override_data))
        session.execute(product_pricing_override_territory.
                        ProductPricingOverrideTerritory.__table__.insert().
                        values(product_pricing_override_territory_data))
        session.execute(
            product_pricing_override_store.ProductPricingOverrideStore.
            __table__.insert().values(
                product_pricing_override_store_data))

        session.execute(track_pricing_override.TrackPricingOverride.
                        __table__.insert().values(
                            track_pricing_override_data))
        session.execute(
            track_pricing_override_territory.TrackPricingOverrideTerritory.
            __table__.insert().values(
                track_pricing_override_territory_data))

        session.execute(
            track_pricing_override_store.TrackPricingOverrideStore.
            __table__.insert().values(
                track_pricing_override_store_data))

        session.execute(
            product_migration_status.ProductMigrationStatus.
            __table__.insert().values(
                product_migration_status_data))

        session.execute(default_account_pricing_tier.DefaultAccountPricingTier.
                        __table__.insert().values(
                            default_account_pricing_tier_data))

        session.execute(
            product_store_effective_price_code.ProductStoreEffectivePriceCode.
            __table__.insert().values(
                product_store_effective_price_code_data))
        session.execute(
            orchard_pricing_tier_value.OrchardPricingTierValue.
            __table__.insert().values(
                orchard_pricing_tier_value_data))

    except sqlalchemy.exc.CompileError as error:
        print('Seeding error: SQL compile:', error)
    except sqlalchemy.exc.IntegrityError as error:
        print('Seeding error: Integrity error:', error)
    except Exception:
        print('Unexpected error while seeding:', sys.exc_info()[0])

    session.commit()