import logging
import operator
from datetime import datetime
from functools import reduce

from median.constant import EtatAdresse, HistoryType, TypeListe
from median.models import Adresse, Stock, Product, CodeBlocage, Seuil, Magasin, Historique, Config, Gpao
from peewee import Value, JOIN, fn, SQL

logger = logging.getLogger("median")


def get_locations_expr(v_search, v_filter_by_magasin, v_format, v_fonction, v_date_peremption, v_bloque):
    if v_search is None:
        v_search = ""

    ms = v_filter_by_magasin.split(",") if v_filter_by_magasin is not None and v_filter_by_magasin != "" else []

    mf = []

    if not v_format:
        format = Adresse.select(Adresse.format).distinct()
        for f in format:
            mf.append(f.format)
    else:
        mf = v_format.split(",")

    if v_fonction == "2":  # Emplacements vides (mais avec contenants)
        t = (Stock.reference.is_null() | (Stock.reference == "")) & (Adresse.contenant != "")
    elif v_fonction == "3":
        t = Stock.reference.is_null(False) & (Stock.reference != "")
    elif v_fonction == "4":  # Sans contenants
        t = Adresse.contenant == ""
    elif v_fonction == "5":
        t = Adresse.contenant != ""
    elif v_fonction == "6":
        adr_taquin = (
            Adresse.select(Adresse.adresse.alias("adr"))
            .where(
                (Adresse.magasin << ms)
                & (Adresse.format == "BOITE PASS")
                & (Adresse.etat == EtatAdresse.Libre.value)
                & (Adresse.contenant == "")
                & (Adresse.bloque == 0)
            )
            .group_by(fn.substr(Adresse.adresse, 1, fn.CHAR_LENGTH(Adresse.adresse) - 3))
            .having(fn.COUNT(SQL("*")) == 2)
        )
        t = Adresse.adresse << list(map(lambda s: s.adr, list(adr_taquin)))

    elif v_fonction == "7":
        AdresseFront = Adresse.alias()
        AdresseBack = Adresse.alias()

        adr_demi_taquin_subquery = (
            AdresseFront.select(AdresseFront.adresse.alias("adr"))
            .join(
                AdresseBack,
                on=(
                    (AdresseFront.magasin == AdresseBack.magasin)
                    & (
                        fn.substr(AdresseFront.adresse, 1, fn.CHAR_LENGTH(AdresseFront.adresse) - 3)
                        == fn.substr(AdresseBack.adresse, 1, fn.CHAR_LENGTH(AdresseBack.adresse) - 3)
                    )
                ),
            )
            .where(
                (AdresseFront.magasin << ms)
                & (AdresseFront.format == "BOITE PASS")
                & (AdresseFront.etat != EtatAdresse.Bloque.value)
                & (AdresseFront.bloque == 0)
                & (AdresseBack.format == "BOITE PASS")
                & (AdresseBack.etat == EtatAdresse.Libre.value)
                & (AdresseBack.bloque == 0)
                & (AdresseFront.adresse.endswith(" 1"))
                & (AdresseBack.adresse.endswith(" 3"))
                & (AdresseFront.contenant != "")
                & (AdresseBack.contenant == "")
            )
        )

        t = Adresse.adresse << list(map(lambda s: s.adr, list(adr_demi_taquin_subquery)))
    elif v_fonction == "8":
        if not v_date_peremption:
            dp = "9999-12-31"
        else:
            dp = v_date_peremption

        t = Stock.date_peremption <= dp
    elif v_fonction == "10":
        # BAD CONFIG : BP WITH PRODUCT IN FRONT, NO BP IN BACK
        adr_bad_config = get_faulty_adr()
        t = Adresse.adresse << list(map(lambda s: s.adr, list(adr_bad_config)))
    else:
        t = True

    andexpr = (
        (len(ms) == 0 or Adresse.magasin << ms)
        & (
            (v_bloque == -1)
            | ((v_bloque == 0) & ((Adresse.bloque == 0) & ((Stock.bloque.is_null()) | (Stock.bloque == 0))))
            | (
                (v_bloque > 0)
                & (
                    (Adresse.etat == EtatAdresse.Bloque.value)
                    | (Adresse.bloque > 0)
                    | ((Stock.bloque.is_null(False)) & (Stock.bloque > 0))
                )
            )
        )
        & (Adresse.format << mf)
        & t
    )

    if len(v_search) > 0:
        lst = []
        for search_term in v_search:
            search_term = search_term.strip()
            shortened_search_term = search_term.replace(" ", "")

            search_conditions = (
                Stock.lot.contains(search_term)
                | fn.REPLACE(Adresse.adresse, " ", "").contains(shortened_search_term)
                | Adresse.format.contains(search_term)
                | Stock.ucd.contains(search_term)
                | Product.designation.contains(search_term)
            )

            lst.append(search_conditions)
        search = reduce(operator.and_, lst)

        expr = reduce(operator.and_, [andexpr, search])
    else:
        expr = andexpr

    return expr


def get_buffer_locations_expr(v_search, v_filter_by_magasin, v_bloque):
    if v_search is None:
        v_search = ""

    ms = v_filter_by_magasin.split(",") if v_filter_by_magasin is not None and v_filter_by_magasin != "" else []

    and_expr = (
        (len(ms) == 0 or Stock.magasin << ms)
        & ((v_bloque == -1) | ((v_bloque == 0) & (Stock.bloque == 0)) | ((v_bloque > 0) & (Stock.bloque > 0)))
        & (Stock.magasin == Stock.adresse)
    )

    if len(v_search) > 0:
        lst = list(
            map(
                lambda s: (
                    (Stock.lot.contains(s.strip()))
                    | (
                        Stock.adresse.contains(s.strip())
                        | Stock.ucd.contains(s.strip())
                        | Product.designation.contains(s.strip())
                    )
                ),
                v_search,
            )
        )
        search = reduce(operator.and_, lst)

        expr = reduce(operator.and_, [and_expr, search])
    else:
        expr = and_expr

    return expr


def get_obj_locations(i):
    if (i.adr_container == "" or i.adr_container is None) and (i.contenant == "" or i.contenant is None):
        state = "Sans contenant"
    elif i.ref == "" or i.ref is None:
        state = "Vide"
    else:
        state = "Avec Produit"

    adr_lock = 0
    adr_reason = ""
    if i.adr_state is not None:
        if i.adr_state == EtatAdresse.Bloque.value and i.adressLock == 0:
            adr_lock = 1
        else:
            adr_lock = i.adressLock
            adr_reason = i.blocageLabel if adr_lock > 0 else ""

    return {
        "magasin": i.mag,
        "pk": i.pk,
        "adr": i.adr,
        "format": i.x_format,
        "bloque": adr_lock,
        "stockLock": i.stockLock if i.stockLock is not None else 0,
        "lock_custom_msg": adr_reason,
        "lock_msg": adr_reason,
        "lock_stock_msg": i.blocageStockLabel,
        "libelle": i.libelle,
        "etat": state,
        "contenant": i.contenant if i.contenant else i.adr_container,
        "reference": i.ref,
        "quantite": i.qte,
        "fraction": i.fraction,
        "designation": i.designation,
        "seuil_min": i.stock_mini,
        "seuil_max": i.stock_maxi,
        "ucd": i.ucd,
        "sortie": str(i.sortie or ""),
        "entree": str(i.entree or ""),
        "lot": i.lot,
        "peremp": str(i.peremp or ""),
        "isBuffer": i.isBuffer,
        "reference_pk": i.reference_pk,
        "address": {
            "state": i.address_state,
        },
        "qte_blister_bac": i.qte_blister_bac,
        "nb_dose_cut": i.nb_dose_cut,
    }


def get_obj_locations_export(i):
    designation = (
        i.product.designation if hasattr(i, "product") and i.product.designation is not None else i.designation
    )

    return {
        "adr": i.adr,
        "etat": getattr(i, "adr_state", ""),
        "contenant": i.contenant if i.contenant else i.adr_container,
        "quantite": i.qte if i.qte is not None else "",
        "reference": i.ref if i.ref is not None else "",
        "designation": designation if designation is not None else "",
        "fraction": i.fraction if i.fraction is not None else "",
        "ucd": i.ucd if i.ucd is not None else "",
        "format": i.x_format if hasattr(i, "x_format") else "",
        "seuil_min": getattr(i, "stock_mini", ""),
        "seuil_max": getattr(i, "stock_maxi", ""),
        "entree": str(i.entree or ""),
        "sortie": str(i.sortie or ""),
        "lot": i.lot if i.lot is not None else "",
        "peremp": str(i.peremp or ""),
    }


def get_all_format(args):
    v_filter_by_magasin = args["filterByMagasin"] if ("filterByMagasin" in args) else None
    v_bloque = args.get("bloque", -1)
    v_fonction = args.get("fonction", None)
    v_date_peremption = args.get("date_peremption", None)
    v_search = args.get("search", None)
    CodeBlocageAlias = CodeBlocage.alias("CodeBlocageAlias")

    expr = get_locations_expr(
        v_search=v_search,
        v_format=None,
        v_bloque=v_bloque,
        v_fonction=v_fonction,
        v_date_peremption=v_date_peremption,
        v_filter_by_magasin=v_filter_by_magasin,
    )

    formats = (
        Adresse.select(Adresse.format.alias("format"))
        .join(Magasin, on=Magasin.mag == Adresse.magasin)
        .join(Stock, JOIN.LEFT_OUTER, on=Stock.adresse == Adresse.adresse)
        .join(Product, JOIN.LEFT_OUTER, on=Product.reference == Stock.reference)
        .join(CodeBlocage, JOIN.LEFT_OUTER, on=Stock.bloque == CodeBlocage.valeur)
        .join(
            Seuil,
            JOIN.LEFT_OUTER,
            (Seuil.fraction == Stock.fraction)
            & (Seuil.reference == Stock.reference)
            & (Seuil.zone == Magasin.type_mag),
        )
        .join(CodeBlocageAlias, JOIN.LEFT_OUTER, on=CodeBlocageAlias.valeur == Stock.bloque)
        .where(expr)
        .group_by(Adresse.format)
    ).objects()

    return [{"format": f.format} for f in formats]


def get_all_locations_request(is_buffer, v_fonction):
    CodeBlocageAlias = CodeBlocage.alias("CodeBlocageAlias")

    query = (
        Adresse.select(
            Stock.pk,
            Adresse.adresse.alias("adr"),
            Adresse.contenant.alias("adr_container"),
            Adresse.etat.alias("adr_state"),
            Value(None).alias("libelle"),
            Adresse.format.alias("x_format"),
            Adresse.bloque.alias("adressLock"),
            Magasin.mag.alias("mag"),
            Stock.bloque.alias("stockLock"),
            Stock.reference.alias("ref"),
            Stock.quantite.alias("qte"),
            Stock.contenant.alias("contenant"),
            Stock.fraction,
            Product.designation.alias("designation"),
            Stock.ucd,
            Stock.date_sortie.alias("sortie"),
            Stock.date_entree.alias("entree"),
            Stock.lot,
            Stock.date_peremption.alias("peremp"),
            CodeBlocage.libelle.alias("blocageLabel"),
            CodeBlocageAlias.libelle.alias("blocageStockLabel"),
            Value(None).alias("comment"),
            Value(is_buffer).alias("isBuffer"),
            Seuil.stock_maxi,
            Seuil.stock_mini,
            Magasin.libelle.alias("storeLabel"),
            Product.pk.alias("reference_pk"),
            Value(None).alias("address_state"),
            Stock.qte_blister_bac,
            Stock.nb_dose_cut,
        )
        .join(Magasin, on=Magasin.mag == Adresse.magasin)
        .join(Stock, JOIN.LEFT_OUTER, on=Stock.adresse == Adresse.adresse)
        .join(Product, JOIN.LEFT_OUTER, on=Product.reference == Stock.reference)
        .join(CodeBlocage, JOIN.LEFT_OUTER, on=Adresse.bloque == CodeBlocage.valeur)
        .join(
            Seuil,
            JOIN.LEFT_OUTER,
            (Seuil.fraction == Stock.fraction)
            & (Seuil.reference == Stock.reference)
            & (Seuil.zone == Magasin.type_mag),
        )
        .join(CodeBlocageAlias, JOIN.LEFT_OUTER, on=CodeBlocageAlias.valeur == Stock.bloque)
    )

    # v_fonction = 4 is the locations without containers.
    # The following is needed to also filter the locations marked "M" as multiple
    # They have no container data in f_adr, but still can be linked to existing stock
    # (Check emplacementList.vue for the origin of "4")
    if v_fonction == "4":
        query = query.where(Stock.pk.is_null())

    return query


def get_all_buffer_request():
    return (
        Stock.select(
            Stock.pk,
            Stock.adresse.alias("adr"),
            Value("").alias("adr_container"),
            Value(None).alias("libelle"),
            Value(None).alias("adr_state"),
            Value("").alias("x_format"),
            Value(0).alias("adressLock"),
            Magasin.mag.alias("mag"),
            Stock.bloque.alias("stockLock"),
            Stock.reference.alias("ref"),
            Stock.quantite.alias("qte"),
            Stock.contenant.alias("contenant"),
            Stock.fraction,
            Product.designation.alias("designation"),
            Stock.ucd,
            Stock.date_sortie.alias("sortie"),
            Stock.date_entree.alias("entree"),
            Stock.lot,
            Stock.date_peremption.alias("peremp"),
            Value("").alias("blocageLabel"),
            CodeBlocage.libelle.alias("blocageStockLabel"),
            Value(None).alias("comment"),
            Value(True).alias("isBuffer"),
            Seuil.stock_maxi,
            Seuil.stock_mini,
            Magasin.libelle.alias("storeLabel"),
            Product.pk.alias("reference_pk"),
            Value(None).alias("address_state"),
            Stock.qte_blister_bac,
            Stock.nb_dose_cut,
        )
        .join(Product, on=Product.reference == Stock.reference)
        .join(CodeBlocage, JOIN.LEFT_OUTER, on=Stock.bloque == CodeBlocage.valeur)
        .switch(Stock)
        .join(Magasin, on=Magasin.mag == Stock.magasin)
        .switch(Stock)
        .join(
            Seuil,
            JOIN.LEFT_OUTER,
            (Seuil.fraction == Stock.fraction)
            & (Seuil.reference == Stock.reference)
            & (Seuil.zone == Magasin.type_mag),
        )
    )


def get_all_locations(args):
    v_filter_by_magasin = args["filterByMagasin"] if ("filterByMagasin" in args) else None
    v_format = args.get("format", None)
    v_bloque = args.get("bloque", -1)
    v_fonction = args.get("fonction", None)
    v_date_peremption = args.get("date_peremption", None)
    v_search = args.get("search", None)

    logger.info("Récupérer les emplacements")
    logger.info(v_format)

    expr = get_locations_expr(
        v_search=v_search,
        v_format=v_format,
        v_bloque=v_bloque,
        v_fonction=v_fonction,
        v_date_peremption=v_date_peremption,
        v_filter_by_magasin=v_filter_by_magasin,
    )

    total_stocks_all = (
        get_all_locations_request(is_buffer=False, v_fonction=v_fonction).where(expr).order_by(Adresse.adresse.asc())
    )

    expr = get_buffer_locations_expr(v_search=v_search, v_bloque=v_bloque, v_filter_by_magasin=v_filter_by_magasin)

    total_stocks_buffer = get_all_buffer_request().where(expr)

    if v_fonction == "1":
        total_stocks_union = total_stocks_all.union_all(total_stocks_buffer)
        total_stocks = total_stocks_union.order_by(total_stocks_union.c.adr)
    elif v_fonction != "9":
        total_stocks = total_stocks_all
    else:
        total_stocks = total_stocks_buffer.order_by(Stock.adresse)

    return total_stocks.objects()


def stock_mvt(stk, diff, usr):
    equipment = Magasin.select(Magasin.id_robot, Magasin.id_zone).where(Magasin.mag == stk.magasin).get()
    req_total = (
        Stock.select(fn.IFNULL(fn.SUM(Stock.quantite), 0).alias("total")).where(Stock.reference == stk.reference).get()
    )

    is_add = diff >= 0

    if is_add:
        dest = Config.select(Config.value).where(Config.propriete == "k_ua_entree").get()
    else:
        dest = Config.select(Config.value).where(Config.propriete == "k_ua_pec").get()

    Historique.create(
        chrono=datetime.now(),
        reference=stk.reference,
        adresse=stk.adresse,
        magasin=stk.magasin,
        quantite_mouvement=abs(diff),
        quantite_totale=req_total.total + diff,
        type_mouvement=HistoryType.Inventaire.value if not is_add else HistoryType.Entree.value,
        lot=stk.lot,
        pmp=0,
        commentaire=f"{'PERTE' if not is_add else 'AJOUT'} STOCK EMPLACEMENT",
        date_peremption=stk.date_peremption,
        contenant=stk.contenant,
        poste="MEDIANWEB",
        ucd=stk.ucd,
        info=f"{'PERTE' if not is_add else 'AJOUT'}  STOCK EMPLACEMENT",
        fraction=100,
        utilisateur=usr.username,
    )

    Gpao.create(
        chrono=datetime.now(),
        poste="MEDIANWEB",
        etat="A",
        ref=stk.reference,
        qte=abs(diff),
        lot=stk.lot,
        type_mvt=TypeListe.Inventory.value if not is_add else HistoryType.Entree.value,
        ucd=stk.ucd,
        dest=dest.value,
        tperemp=stk.date_peremption,
        id_robot=equipment.id_robot,
        id_zone=equipment.id_zone,
        magasin=stk.magasin,
    )


def get_faulty_adr():
    emptyBP_front_locations = (
        Adresse.select(
            fn.LEFT(Adresse.adresse, fn.INSTR(Adresse.adresse, fn.SUBSTRING_INDEX(Adresse.adresse, ".", -1))).alias(
                "left_adr"
            )
        )
        .join(Stock, JOIN.LEFT_OUTER, on=Stock.contenant == Adresse.contenant)
        .where((fn.TRIM(fn.SUBSTRING_INDEX(Adresse.adresse, ".", -1)) == "1") & (Adresse.contenant != ""))
        .group_by(Stock.contenant)
        .alias("emptyBP_front_locations")
    )

    noBP_back_locations = (
        Adresse.select(
            fn.LEFT(Adresse.adresse, fn.INSTR(Adresse.adresse, fn.SUBSTRING_INDEX(Adresse.adresse, ".", -1))).alias(
                "left_adr"
            )
        )
        .where((fn.TRIM(fn.SUBSTRING_INDEX(Adresse.adresse, ".", -1)) == "3") & (Adresse.contenant == ""))
        .alias("noBP_back_locations")
    )

    distinct_adresses = (
        Adresse.select(
            Adresse.adresse.alias("adresse"),
            fn.LEFT(Adresse.adresse, fn.INSTR(Adresse.adresse, fn.SUBSTRING_INDEX(Adresse.adresse, ".", -1))).alias(
                "left_adr"
            ),
        )
        .where((fn.LEFT(Adresse.adresse, 2) == "ST"))
        .distinct()
        .alias("distinct_adresses")
    )

    query = (
        distinct_adresses.select_from(distinct_adresses.c.adresse.alias("adr"))
        .join(
            emptyBP_front_locations, JOIN.INNER, on=emptyBP_front_locations.c.left_adr == distinct_adresses.c.left_adr
        )
        .join(noBP_back_locations, JOIN.INNER, on=noBP_back_locations.c.left_adr == distinct_adresses.c.left_adr)
    )

    return query
