""" "Model Store."""

from collections import defaultdict
from typing import Any

from sqlalchemy import Column, Enum, ForeignKey, Integer, String, func
from sqlalchemy.sql import text

from store.connectors.mysql import BaseModel, db_session
from store.constants.constants import STORE_STATUS, TERRITORY_STANDARD_ORCH_1_2016


class Store(BaseModel):
    """Store model."""

    __tablename__ = "customer_master_master"

    customer_master_master_id = Column(Integer, primary_key=True, nullable=False)
    customer_name = Column(String, nullable=False)
    supports_timed_release = Column(String)
    status = Column(Enum(*STORE_STATUS), nullable=False)

    def to_dict(self) -> dict[str, Any]:
        """Return dictionary of store's properties."""
        return {
            "id": self.customer_master_master_id,
            "name": self.customer_name,
            "supports_timed_release": self.supports_timed_release == "Y",
            "status": self.status,
        }


class StoreClassificationDetail(BaseModel):
    """Store classification model."""

    __tablename__ = "store_classification_detail"

    classification_detail_id = Column(Integer, primary_key=True, nullable=False)
    store_id = Column(
        Integer, ForeignKey(Store.customer_master_master_id), nullable=False
    )


class ClassificationDetail(BaseModel):
    """Classification detail model."""

    __tablename__ = "classification_detail"

    id = Column(Integer, primary_key=True, nullable=False)
    classification = Column(String, nullable=False)


class DistributionType(BaseModel):
    """Distribution type model."""

    __tablename__ = "distribution_type"

    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String, nullable=False)


class StoreDistributionType(BaseModel):
    """Store distribution type model."""

    __tablename__ = "customer_master_master_distribution_type"

    distribution_type_id = Column(Integer, primary_key=True, nullable=False)
    customer_master_master_id = Column(
        Integer, ForeignKey(Store.customer_master_master_id), nullable=False
    )


class Substore(BaseModel):
    """Substore model."""

    __tablename__ = "customer_master"

    customer_id = Column(Integer, primary_key=True, nullable=False)
    customer_master_master_id = Column(
        Integer, ForeignKey(Store.customer_master_master_id), nullable=False
    )
    territory = Column(Integer, primary_key=False, nullable=True)


class Territory(BaseModel):
    """Territory model."""

    __tablename__ = "country"

    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String, nullable=False)
    country_code = Column(String, nullable=False)


def get_stores(
    supports_timed_release: bool | None = None,
    classification_id: int | None = None,
    statuses: list[str] | None = None,
    order_by: str | None = None,
    order_dir: str | None = None,
) -> list[dict[str, Any]]:
    """Get stores.

    Args:
        supports_timed_release(bool or None): if present, filter result
            set by whether supports_timed_release is 'Y' or 'N'. If None,
            do not filter.
        classification_id(Int or None): if present, filter result set by
            whether the store has a coresponding store_classification_detail
            matching the id.
        statuses(list(string) or None): if present, filter by provided statues
        order_by(string or None): order_by database column
        order_dir(string or None): order_dir for database sorting (either 'asc' or 'desc')

    Returns:
        list: the stores
    """
    with db_session() as session:
        query = session.query(Store)

        # filter by supports_timed_release
        if supports_timed_release is not None:
            query = query.filter(
                Store.supports_timed_release == ("Y" if supports_timed_release else "N")
            )

        # filter by statues
        if statuses is not None:
            query = query.filter(Store.status.in_(statuses))

        # filter store classification
        if classification_id is not None:
            query = (
                query.join(
                    StoreClassificationDetail,
                    Store.customer_master_master_id
                    == StoreClassificationDetail.store_id,
                )
                .join(
                    ClassificationDetail,
                    StoreClassificationDetail.classification_detail_id
                    == ClassificationDetail.id,
                )
                .filter(ClassificationDetail.id == classification_id)
            )

        stores = []

        if order_by and order_dir:
            query = query.order_by(text(f"{order_by} {order_dir}"))

        # serialize result set
        for result in query:
            stores.append(result.to_dict())

        return stores


def get_stores_by_ids(store_ids: list[int]) -> list[dict[str, Any]]:
    """Get stores.

    Args:
        store_ids(list(int)): list of store IDs

    Returns:
        list: the stores
    """
    with db_session() as session:
        query = session.query(Store).filter(
            Store.customer_master_master_id.in_(store_ids)
        )

        items = []

        # serialize result set
        for result in query:
            items.append(result.to_dict())

        return items


def get_store_classifications_by_store_ids(
    store_ids: list[int],
) -> list[dict[str, Any]]:
    """Get store classifications by store ids.

    Args:
        store_ids(list(int)): list of store IDs

    Returns:
        list: classifications for each store id
    """
    with db_session() as session:
        query = (
            session.query(
                Store.customer_master_master_id,
                func.group_concat(ClassificationDetail.id.distinct()),
            )
            .outerjoin(
                StoreClassificationDetail,
                Store.customer_master_master_id == StoreClassificationDetail.store_id,
            )
            .outerjoin(
                ClassificationDetail,
                StoreClassificationDetail.classification_detail_id
                == ClassificationDetail.id,
            )
            .group_by(Store.customer_master_master_id)
            .filter(Store.customer_master_master_id.in_(store_ids))
        )

        payload = {}
        for store_id, classification in query:
            if store_id not in payload:
                payload[store_id] = {"id": store_id, "classifications": []}
            if classification:
                payload[store_id]["classifications"] = list(
                    map(int, classification.split(","))
                )

        return list(payload.values())


def get_store_distribution_types_by_store_ids(
    store_ids: list[int],
) -> list[dict[str, Any]]:
    """Get store distribution types store ids.

    Args:
        store_ids(list(int)): list of store IDs

    Returns:
        list: distribution_types for each store id
    """
    with db_session() as session:
        query = (
            session.query(
                Store.customer_master_master_id,
                func.group_concat(DistributionType.id.distinct()),
            )
            .outerjoin(
                StoreDistributionType,
                Store.customer_master_master_id
                == StoreDistributionType.customer_master_master_id,
            )
            .outerjoin(
                DistributionType,
                StoreDistributionType.distribution_type_id == DistributionType.id,
            )
            .group_by(Store.customer_master_master_id)
            .filter(Store.customer_master_master_id.in_(store_ids))
        )
        payload = {}
        for store_id, distribution_type in query:
            if store_id not in payload:
                payload[store_id] = {"id": store_id, "distribution_types": []}
            if distribution_type:
                payload[store_id]["distribution_types"] = list(
                    map(int, distribution_type.split(","))
                )

        return list(payload.values())


def get_substores_by_ids(store_ids: list[int]) -> list[dict[str, Any]]:
    """Get substores.

    Args:
        store_ids(list(int)): list of store IDs

    Returns:
        list: the substores
    """
    with db_session() as session:
        query = (
            session.query(Substore, Territory)
            .filter(Substore.customer_master_master_id.in_(store_ids))
            .join(
                Territory,
                Substore.territory == Territory.id,
            )
        )

        # serialize result set
        substores = defaultdict(list)
        for result in query:
            substore, territory = result

            substores[substore.customer_master_master_id].append(
                {
                    "orchard_id": territory.id,
                    "country_code": territory.country_code,
                    "standard": TERRITORY_STANDARD_ORCH_1_2016,
                }
            )

        substores_array = []
        for key, value in substores.items():
            substores_array.append({"store_id": key, "territories": value})

        return substores_array