"""Orchard Pricing Tier Value Model.

This model represents an Orchard Pricing Tier Value
"""

from oto import response

from pricing.constants import pricing_family
from pricing.connectors import mysql
from pricing.models.product_orchard_pricing_tier import ProductOrchardPricingTier
import sqlalchemy


class OrchardPricingTierValue(mysql.BaseModel):
    """Orchard Pricing Tier Value model."""

    __tablename__ = 'orchard_pricing_tier_value'

    orchard_pricing_tier_value_id = sqlalchemy.Column(
        sqlalchemy.BIGINT, primary_key=True)
    orchard_pricing_tier_id = sqlalchemy.Column(sqlalchemy.BIGINT)
    value_cents = sqlalchemy.Column(sqlalchemy.BIGINT)
    country_code = sqlalchemy.Column(sqlalchemy.VARCHAR(30))

    def to_value(self):
        return self.value_cents

    def to_dict(self):
        return dict(
            orchard_pricing_tier_id=self.orchard_pricing_tier_id,
            value_cents=self.value_cents,
            country_code=self.country_code)


@mysql.autosession()
def get_price_value_by_tier_id(orchard_pricing_tier_id, session):
    """Get the price value in cents for an orchard pricing tier.

    Args:
        orchard_pricing_tier_id (int): the ID of the orchard pricing tier.
        session (Session): the mysql session.
    Returns:
        int: the price value in cents
    """
    result = session.query(OrchardPricingTierValue).filter_by(
        orchard_pricing_tier_id=orchard_pricing_tier_id
    ).first()

    return result.to_value() if result else None


@mysql.autosession()
def get_price_values_by_tier_id(orchard_pricing_tier_ids, session):
    """Get the price value in cents for an orchard pricing tier.

    Args:
        orchard_pricing_tier_ids (list): the IDs of the orchard pricing tiers.
        session (Session): the mysql session.
    Returns:
        list: the price values in cents
    """
    results = session.query(OrchardPricingTierValue).where(
        OrchardPricingTierValue.orchard_pricing_tier_id.in_(orchard_pricing_tier_ids)
    ).all()

    return [result.to_value() for result in results]


@mysql.autosession()
def get_album_price_cents_by_product_id(product_id, session):
    """Get get a product's album price in cents by the product id.

    Args:
        product_id (int): the ID of a product
        session (Session): the mysql session
    Returns:
        int: the album price in cents
    """
    result = _get_tier_price_value_cents_by_product_id(
        product_id, pricing_family.AUDIO_ALBUM_PRICING_FAMILY_ID, session)

    return result.to_value() if result else None


@mysql.autosession()
def get_track_price_cents_by_product_id(product_id, session):
    """Get get a product's track price in cents by the product id.

    Args:
        product_id (int): the ID of a product
        session (Session): the mysql session
    Returns:
        int: the track price in cents
    """
    result = _get_tier_price_value_cents_by_product_id(
        product_id, pricing_family.AUDIO_TRACK_PRICING_FAMILY_ID, session)

    return result.to_value() if result else None


@mysql.autosession()
def get_all_pricing_tier_values(session):
    """Return all the tier pricing tier values.

    Args:
        session (Session): the mysql session.
    """
    try:
        results = session.query(OrchardPricingTierValue).all()
        if results:
            return response.Response({'items': [row.to_dict() for row in results]})
        return response.create_not_found_response()
    except Exception as e:
        return response.create_error_response(
            500, f"Database error: {str(e)}")


def _get_tier_price_value_cents_by_product_id(
        product_id, price_family_id, session, country_code='US'):
    """Get get a product's tier price in cents by the product id.

    Args:
        product_id (int): the ID of a product
        price_family_id (int): the ID of the pricing family
        session (Session): the mysql session
        country_code (str): the country code
    Returns:
        int: the tier price in cents"""

    return session.query(OrchardPricingTierValue).join(
        ProductOrchardPricingTier,
        OrchardPricingTierValue.orchard_pricing_tier_id ==
        ProductOrchardPricingTier.orchard_pricing_tier_id
    ).where(
        ProductOrchardPricingTier.product_id == product_id,
        ProductOrchardPricingTier.pricing_family_id == price_family_id,
        OrchardPricingTierValue.country_code == country_code
    ).first()