"""Product Pricing Override Model.

This model represents a Product Pricing Override
"""

import copy
import datetime

from oto import response

from pricing.connectors import mysql
from pricing.constants import error
from pricing.models import product_pricing_override_store
from pricing.models import product_pricing_override_territory

from pricing.models.orchard_pricing_tier import OrchardPricingTier
from pricing.models.product_pricing_override_store import ProductPricingOverrideStore
from pricing.models.product_pricing_override_territory import ProductPricingOverrideTerritory
import sqlalchemy


class ProductPricingOverride(mysql.BaseModel):
    """Product Pricing Override model."""

    __tablename__ = 'product_pricing_override'

    product_pricing_override_id = sqlalchemy.Column(
        sqlalchemy.BIGINT, primary_key=True)
    orchard_pricing_tier_id = sqlalchemy.Column(sqlalchemy.BIGINT)
    store_id = sqlalchemy.Column(sqlalchemy.Integer)
    product_id = sqlalchemy.Column(sqlalchemy.Integer)
    pricing_family_id = sqlalchemy.Column(sqlalchemy.BIGINT)
    custom_price = sqlalchemy.Column(sqlalchemy.VARCHAR(30))
    custom_currency_code = sqlalchemy.Column(sqlalchemy.VARCHAR(10))
    start_date = sqlalchemy.Column(sqlalchemy.DateTime)
    end_date = sqlalchemy.Column(sqlalchemy.DateTime)
    applies_worldwide = sqlalchemy.Column(sqlalchemy.Boolean, default=False)
    territory_list_include = sqlalchemy.Column(
        sqlalchemy.Boolean, default=False)
    resolution = sqlalchemy.Column(sqlalchemy.VARCHAR(45))
    price_code = sqlalchemy.Column(sqlalchemy.VARCHAR(45))
    sort_order = sqlalchemy.Column(sqlalchemy.Integer)
    activated = sqlalchemy.Column(sqlalchemy.Boolean, default=True)
    created_date = sqlalchemy.Column(sqlalchemy.DateTime)
    created_by = sqlalchemy.Column(sqlalchemy.VARCHAR(45))
    updated_date = sqlalchemy.Column(sqlalchemy.DateTime)
    updated_by = sqlalchemy.Column(sqlalchemy.VARCHAR(45))

    def to_dict(self):
        """Convert Product Pricing Override to dict."""
        start_date = None
        if isinstance(self.start_date, datetime.datetime):
            start_date = self.start_date.timestamp()

        end_date = None
        if isinstance(self.end_date, datetime.datetime):
            end_date = self.end_date.timestamp()

        created_date = None
        if isinstance(self.created_date, datetime.datetime):
            created_date = self.created_date.timestamp()

        updated_date = None
        if isinstance(self.updated_date, datetime.datetime):
            updated_date = self.updated_date.timestamp()

        return dict(
            product_pricing_override_id=self.product_pricing_override_id,
            orchard_pricing_tier_id=self.orchard_pricing_tier_id,
            store_id=self.store_id,
            product_id=self.product_id,
            pricing_family_id=self.pricing_family_id,
            custom_price=self.custom_price,
            custom_currency_code=self.custom_currency_code,
            start_date=start_date,
            end_date=end_date,
            applies_worldwide=self.applies_worldwide,
            territory_list_include=self.territory_list_include,
            resolution=self.resolution,
            price_code=self.price_code,
            sort_order=self.sort_order,
            activated=self.activated,
            created_date=created_date,
            updated_date=updated_date
        )


def _get_pricing_family_for_product(data, session):
    """Get pricing family for a product.

    Args:
        data (dict): containing data to query the orchard pricing tier.
        session (Session): the mysql session.

    Returns:
        OrchardPricingTier: containing the orchard pricing tier result.

    """
    query = session.query(
        OrchardPricingTier.pricing_family_id,
        OrchardPricingTier.orchard_pricing_tier_id
    ).where(
        OrchardPricingTier.pricing_family_id == data.get('pricing_family_id', 0),
        OrchardPricingTier.orchard_pricing_tier_id == data['orchard_pricing_tier_id']
    )
    result = session.execute(query).fetchone()
    return result


@mysql.autosession()
def create_product_pricing_override(product_id, data, session):
    """Create a new product pricing override.

    Args:
        product_id (int): the product id
        data (dict): the data from which to create the pricing override.
        session (Session): the mysql session.

    Returns:
        response.Response: containing the created pricing override dict.
    """
    if not data:
        return response.create_error_response(
            400, error.ERROR_MESSAGE_EMPTY_BODY)

    if 'orchard_pricing_tier_id' in data:
        pricing_family_for_product = _get_pricing_family_for_product(data, session)

        if not pricing_family_for_product:
            return response.create_error_response(
                400, error.ERROR_MESSAGE_WRONG_PRICING_FAMILY)

    data['created_date'] = datetime.datetime.now()
    data['product_id'] = product_id

    product_pricing_override = ProductPricingOverride(**data)

    session.add(product_pricing_override)
    session.commit()

    return response.Response(product_pricing_override.to_dict())


def _get_by_product_id_and_product_pricing_override_id(
        product_id, product_pricing_override_id, session):
    """Get a product pricing override by its product and override ID.

    Args:
        product_id (int): product id
        product_pricing_override_id (int): product pricing override id
        session (Session): the mysql session
    Returns:
        ProductPricingOverride: the found product pricing override
    """
    found_product_pricing_override = session.query(
        ProductPricingOverride).filter_by(
        product_id=product_id,
        product_pricing_override_id=product_pricing_override_id).one_or_none()

    return found_product_pricing_override


def _get_territories_by_product_id(product_id, session):
    """Get territories by product ID.

    Args:
        product_id (int): the ID of a product
        session (Session): the mysql session
    Returns:
        array: containing product pricing override territories
    """
    territory_query = session.query(ProductPricingOverrideTerritory).join(
        ProductPricingOverride,
        ProductPricingOverrideTerritory.product_pricing_override_id ==
        ProductPricingOverride.product_pricing_override_id
    ).where(
        ProductPricingOverride.product_id == product_id
    ).all()
    result = [entry.to_dict() for entry in territory_query]
    return result


def _get_stores_by_product_id(product_id, session):
    """Get stores by product ID.

    Args:
        product_id (int): the ID of a product
        session (Session): the mysql session
    Returns:
        array: containing product pricing override stores
    """
    query = session.query(ProductPricingOverrideStore).join(
        ProductPricingOverride,
        ProductPricingOverrideStore.product_pricing_override_id ==
        ProductPricingOverride.product_pricing_override_id
    ).where(
        ProductPricingOverride.product_id == product_id
    ).all()
    result = [entry.to_dict() for entry in query]
    return result


@mysql.autosession()
def get_by_product_id(product_id, session):
    """Get product pricing overrides with the product_id.

    Args:
        product_id (int): the ID of a product
        session (Session): the mysql session
    Returns:
        response.Response: The product pricing overrides
    """
    product_pricing_overrides = session.query(ProductPricingOverride)\
        .filter_by(product_id=product_id)\
        .order_by(ProductPricingOverride.sort_order.desc()).all()

    territory_rows = _get_territories_by_product_id(product_id, session)
    store_rows = _get_stores_by_product_id(product_id, session)

    result = [row.to_dict() for row in product_pricing_overrides]

    for override in result:
        override['territories'] = []
        override['stores'] = []
        for row in territory_rows:
            if override['product_pricing_override_id'] == row[
                    'product_pricing_override_id']:
                override['territories'].append(row['territory_code'])
        for row in store_rows:
            if override['product_pricing_override_id'] == row[
                    'product_pricing_override_id']:
                override['stores'].append(row['store_id'])

    return response.Response(
        {
            'items': result
        })


@mysql.autosession()
def update_product_pricing_override(
        product_id, product_pricing_override_id, data, session):
    """Update a product pricing override.

    Args:
        product_id (int): the product id
        product_pricing_override_id (int): the product pricing override id
        data (dict): The data from which to update the product pricing override
        session: the mysql session
    Returns:
        response.Response: containing the updated product pricing override.
    """
    if not data:
        return response.create_error_response(
            400, error.ERROR_MESSAGE_EMPTY_BODY)

    data['updated_date'] = datetime.datetime.now()

    found_product_pricing_override = \
        _get_by_product_id_and_product_pricing_override_id(
            product_id, product_pricing_override_id, session)

    if not found_product_pricing_override:
        return response.create_not_found_response()

    for key, value in data.items():
        setattr(found_product_pricing_override, key, value)

    session.commit()

    return response.Response(found_product_pricing_override.to_dict())


@mysql.autosession()
def find_duplicate(product_id, data, session):
    """Find a duplicate product pricing override.

    Args:
        product_id (int): the ID of the product.
        data (dict): the data to use to find the duplicate.
        session (Session): the mysql session.

    Returns:
        response.Response: containing True if there's a duplicate and False if
            there isn't.
    """
    if not data:
        return response.create_error_response(
            400, error.ERROR_MESSAGE_EMPTY_BODY)

    data_copy = copy.deepcopy(data)
    data_copy['product_id'] = product_id
    data_copy['activated'] = True

    match = session.query(
        ProductPricingOverride).filter_by(**data_copy).first()

    if match is None:
        return response.Response({'found': False})
    else:
        return response.Response({'found': True, 'item': match.to_dict()})


@mysql.autosession()
def delete_by_product_id(product_id, session):
    """Delete all overrides for a product.

    Args:
        product_id (int): the ID of the product.
        session (Session): the mysql session.

    Returns:
        response.Response: containing the deleted
            product orchard pricing tier dict.
    """
    product_pricing_overrides = session.query(ProductPricingOverride)\
        .filter_by(product_id=product_id)

    result = []
    for override in product_pricing_overrides:
        product_pricing_override_territory.\
            delete_by_product_pricing_override_id(
                override.product_pricing_override_id)
        product_pricing_override_store.\
            delete_by_product_pricing_override_id(
                override.product_pricing_override_id)
        session.delete(override)
        result.append(override)

    return response.Response(
        {
            'deleted': len(result)
        })


@mysql.autosession()
def get_all_with_store_id(session):
    """Get all product pricing overrides with a store_id.

    Args:
        session (Session): the mysql session.
    Returns:
        response.Response: The product pricing overrides as dict
    """
    rows = session.query(ProductPricingOverride).filter(
        ProductPricingOverride.store_id.isnot(None)).all()

    if not rows:
        return response.create_not_found_response()

    result = [row.to_dict() for row in rows]

    return response.Response({'items': result})