import logging
from typing import Dict, Any
from datetime import datetime

from median.models import (
    Service,
    ListeModel,
    ListeItemModel,
    Gpao,
    Seuil,
    ListeValide,
    ItemValide,
)
from median.constant import TypeMouvementGpao, TypeServiListe, TypeEtatGpao, MEDIANWEB_POSTE, TypeListe, EtatListe
from peewee import fn, JOIN, Value
from common.status import HTTP_200_OK, HTTP_500_INTERNAL_SERVER_ERROR, HTTP_400_BAD_REQUEST

logger = logging.getLogger("median.completion")


class WardService:
    """Service for handling ward-related operations"""

    def fetch_global_wards(self) -> Dict[str, Any]:
        """Fetch list of global wards"""
        try:
            self._create_D_gpao_lines()

            # Query from ListeModel
            query_liste = (
                ListeModel.select(
                    ListeModel.pk,
                    Service,
                    fn.DATE(ListeModel.ddeb).alias("creation_date"),
                    ListeModel.id_chargement,
                    Value("liste").alias("type_liste"),
                )
                .join(ListeItemModel, JOIN.INNER, on=(ListeModel.liste == ListeItemModel.liste))
                .switch(ListeModel)
                .join(Service, JOIN.INNER, on=(Service.code == ListeModel.service))
                .where(
                    (ListeModel.liste.contains("GLOBAL"))
                    & (ListeModel.selectionne == 0)
                    & (
                        ListeModel.type_servi
                        << [TypeServiListe.GlobalePilulier.value, TypeServiListe.GlobaleBoite.value]
                    )
                )
                .group_by(ListeModel.service, fn.DATE(ListeModel.ddeb), ListeModel.id_chargement)
            )

            # Query from ListeValide
            query_valide = (
                ListeValide.select(
                    ListeValide.pk,
                    Service,
                    fn.DATE(ListeValide.ddeb).alias("creation_date"),
                    ListeValide.id_chargement,
                    Value("liste_valide").alias("type_liste"),
                )
                .join(ItemValide, JOIN.INNER, on=(ItemValide.liste_pk == ListeValide.pk))
                .switch(ListeValide)
                .join(Service, JOIN.INNER, on=(Service.code == ListeValide.service))
                .where(
                    (ListeValide.liste.contains("GLOBAL"))
                    & (
                        ListeValide.type_servi
                        << [TypeServiListe.GlobalePilulier.value, TypeServiListe.GlobaleBoite.value]
                    )
                )
                .group_by(ListeValide.service, fn.DATE(ListeValide.ddeb), ListeValide.id_chargement)
            )

            # Union both queries
            union_query = query_liste.union(query_valide)

            # Final query with grouping and ordering
            final_query = union_query.group_by(union_query.c.x_dest, union_query.c.creation_date).order_by(
                union_query.c.creation_date.desc()
            )

            wards = []
            for row in final_query:
                creation_date = row.creation_date

                if row.type_liste == "liste_valide":
                    items_to_complete = ItemValide.select().where(
                        (ItemValide.liste_pk == row.pk) & (ItemValide.quantite_serv < ItemValide.quantite_dem)
                    )
                    if len(items_to_complete) == 0:
                        continue

                wards.append(
                    {
                        "code": row.service.code,
                        "label": row.service.libelle,
                        "chrono": creation_date.strftime("%Y-%m-%d") if creation_date else None,
                        "id_chargement": row.id_chargement,
                    }
                )

            return {"wards": wards}, HTTP_200_OK

        except Exception as e:
            logger.error(f"Error fetching wards: {str(e)}")
            return {"error": f"Failed to fetch wards: {str(e)}"}, HTTP_500_INTERNAL_SERVER_ERROR

    def _create_D_gpao_lines(self):
        # Search all global items without thresholds to create Gpao entries

        query_global = (
            ListeItemModel.select(
                fn.SUM(ListeItemModel.qte_dem - ListeItemModel.qte_serv).alias("qte"),
                ListeItemModel,
                ListeModel,
                Gpao,
            )
            .join(ListeModel, JOIN.INNER, on=(ListeModel.liste == ListeItemModel.liste))
            .join(
                Gpao,
                JOIN.LEFT_OUTER,
                on=(
                    (Gpao.ref == ListeItemModel.reference)
                    & (Gpao.liste == ListeModel.liste)
                    & (Gpao.fraction == ListeItemModel.fraction)
                    & (Gpao.etat << [TypeEtatGpao.DRAFT.value, TypeEtatGpao.DRAFT2.value])
                    & (Gpao.type_mvt == TypeMouvementGpao.COMPLEMENT.value)
                    & (Gpao.ipp == ListeModel.num_ipp)
                ),
            )
            .join(Seuil, JOIN.LEFT_OUTER, on=(Seuil.reference == ListeItemModel.reference))
            .where(
                (ListeModel.num_ipp.contains("GLOBAL"))
                & (ListeModel.type_servi << [TypeServiListe.GlobalePilulier.value, TypeServiListe.GlobaleBoite.value])
                & (Seuil.pk.is_null())
                & (Gpao.pk.is_null())
                & (ListeItemModel.etat == EtatListe.Vierge.value)
                & (ListeItemModel.mode == TypeListe.Output.value)
                & (ListeItemModel.info != "")
            )
            .group_by(ListeModel.service)
        )

        if len(query_global) > 0:
            # If there are global items, we need to create Gpao entries for them
            for item in query_global:
                if not hasattr(item, "gpao") or item.gpao.pk is None:
                    logger.info(f"Creating Gpao D/A entry for item {item.pk} in global mode")
                    self._create_gpao_entry(item)

    def fetch_wards(self, gpao_type: str) -> Dict[str, Any]:
        """Fetch list of wards for filtering with GPAO type validation"""
        try:
            if gpao_type not in [TypeMouvementGpao.COMPLEMENT.value, TypeMouvementGpao.OUTPUT.value]:
                logger.error(f"Invalid gpaoType: {gpao_type}")
                return {"error": "Invalid gpaoType"}, HTTP_400_BAD_REQUEST

            # TODO: Intriged by the "_2" ? look the todo note in gpao_service
            query = (
                ListeValide.select(
                    ListeValide,
                    ItemValide,
                    Gpao,
                    Service,
                    Seuil,
                )
                .join(
                    Gpao,
                    JOIN.INNER,
                    on=((Gpao.liste == ListeValide.liste) | (Gpao.liste == ListeValide.liste + "_2"))
                    | (Gpao.liste == ListeValide.liste + "-C"),
                )
                .join_from(
                    ListeValide,
                    ItemValide,
                    JOIN.INNER,
                    on=(ItemValide.liste_pk == ListeValide.pk) & (ItemValide.item_wms == Gpao.item_wms),
                )
                .join_from(ListeValide, Service, JOIN.LEFT_OUTER, on=(Gpao.dest == Service.code).alias("servicemodel"))
                .join_from(
                    Gpao,
                    Seuil,
                    JOIN.LEFT_OUTER,
                    on=((Gpao.ref == Seuil.reference) & (Seuil.fraction == Gpao.fraction)).alias("seuilmodel"),
                )
            )

            # Fetch wards that have items in the current GPAO type
            where_conditions = [
                (Gpao.item == ItemValide.item),
                (Gpao.ref == ItemValide.reference),
                (Gpao.fraction == ItemValide.fraction),
                (Gpao.type_mvt == gpao_type),
                (Gpao.etat << [TypeEtatGpao.DRAFT.value, TypeEtatGpao.DRAFT2.value]),
            ]

            # If gpao_type is 'D', exclude items that have a Seuil
            if gpao_type == TypeMouvementGpao.COMPLEMENT.value:  # 'D'
                where_conditions.append(Seuil.pk.is_null())
            else:
                where_conditions.append(Gpao.id_pilulier == ItemValide.id_pilulier)

            query = query.where(*where_conditions)

            # if gpao_type == TypeMouvementGpao.OUTPUT.value:
            # For OUTPUT type, we need to group by service and liste
            # query = query.group_by(Service.code, Gpao.liste, fn.DATE(Gpao.chrono))
            query = query.group_by(ListeValide.id_chargement)
            # elif gpao_type == TypeMouvementGpao.COMPLEMENT.value:
            # For COMPLEMENT type, we group by service and date
            #    query = query.group_by(Service.code, fn.DATE(Gpao.chrono))

            # Order by chrono descending
            query = query.order_by(-ListeValide.chrono)

            options = []
            for line in query:
                chrono_str = line.chrono.strftime("%Y/%m/%d")
                options.append(
                    {
                        "code": line.servicemodel.code,
                        "label": line.servicemodel.libelle,
                        "chrono": chrono_str,
                        "id_chargement": line.id_chargement,
                        # if gpao_type == TypeMouvementGpao.OUTPUT.value
                        # else None,
                        "type_servi": line.type_servi,
                    }
                )

            return {"wards": options}, HTTP_200_OK

        except Exception as e:
            logger.error(f"Error fetching wards: {str(e)}")
            return {"error": f"Failed to fetch wards: {str(e)}"}, HTTP_500_INTERNAL_SERVER_ERROR

    def _create_gpao_entry(self, item: ListeItemModel) -> None:
        """Create a new GPAO entry for a global item"""
        new_gpao: Gpao = Gpao()
        new_gpao.chrono = datetime.now()
        new_gpao.poste = MEDIANWEB_POSTE
        new_gpao.dest = item.listemodel.service
        new_gpao.etat = "A"
        new_gpao.liste = item.listemodel.liste
        new_gpao.ref = item.reference
        new_gpao.qte = item.qte_dem - item.qte_serv
        new_gpao.lot = item.lot
        new_gpao.type_mvt = "D"
        new_gpao.tperemp = "0000-00-00 00:00:00"
        new_gpao.fraction = item.fraction
        new_gpao.cip = item.cip
        new_gpao.ucd = item.ucd
        new_gpao.magasin = item.magasin
        new_gpao.info = item.info
        new_gpao.qte_dem = item.qte_dem
        new_gpao.contenant = ""
        new_gpao.item = item.item
        new_gpao.pk_item = item.pk
        new_gpao.item_wms = item.item_wms
        new_gpao.liste = item.listemodel.liste
        new_gpao.ipp = item.listemodel.num_ipp
        new_gpao.num_sej = item.listemodel.num_sej
        new_gpao.service = item.listemodel.service
        new_gpao.save()

        logger.info(f"Created Gpao ({new_gpao.pk}) entry for item {item.reference} with quantity {new_gpao.qte}")
