import json
import operator
from functools import reduce

from median.models import Magasin, Product, Seuil, Stock, ZoneMag, Zone, Dispensation, DispensationItem
from peewee import fn


def get_all_request(request):
    args = json.loads(request.data)
    _equipments = args.get('equipments', [])
    _criterias = args.get('criterias', [])
    _fractions = args.get('fractions', [])

    andexpr = True
    if len(_equipments) > 0:
        andexpr = Magasin.pk << _equipments

    if len(_fractions) > 0:
        andexpr = reduce(operator.and_, [andexpr, (Seuil.fraction << _fractions)])

    if len(_criterias) > 0:
        lst = list(map(lambda s: (
            (Product.designation.contains(s.strip())) |
            (Magasin.libelle.contains(s.strip())) |
            (Seuil.reference.contains(s.strip()))
        ), _criterias))
        search = reduce(operator.and_, lst)
        expr = reduce(operator.and_, [andexpr, search])
    else:
        expr = andexpr

    stock_quantity = (
        Stock.select(
            fn.IFNULL(fn.SUM(Stock.quantite), 0).alias('qte')
        ).where(
            (Stock.reference == Seuil.reference) &
            (Stock.fraction == Seuil.fraction) &
            (Stock.magasin == Magasin.mag))
        )

    stock_admin = (
        Stock().select(
            fn.IFNULL(fn.SUM(Stock.quantite), 0).alias("total")
        ).join(
            ZoneMag, on=(Stock.magasin == ZoneMag.mag)
        ).join(
            Zone, on=((Zone.zone == ZoneMag.zone) & (Zone.appro == 1))
        ).where(
            Stock.zone_admin == Magasin.type_mag, Stock.reference == Seuil.reference,
            Stock.fraction == Seuil.fraction)
        )

    ordered_quantity = (Dispensation.select(
        (fn.IFNULL(fn.SUM(DispensationItem.qte_dem - DispensationItem.qte_serv), 0)).alias('cmd')).join(
        DispensationItem, on=(
            (Dispensation.liste == DispensationItem.liste)
            & (Dispensation.mode == DispensationItem.mode)))
                        .where(  # noqa
        (Dispensation.mode == 'E')
        & (Seuil.fraction == DispensationItem.fraction)
        & (DispensationItem.reference == Seuil.reference)
        & (Dispensation.zone_fin == Magasin.type_mag))
                        .group_by(Dispensation.zone_fin))

    return (Seuil.select(
        Seuil.pk.alias('pk'),
        Seuil.reference.alias('reference'),
        Product.designation.alias('designation'),
        Magasin.libelle.alias('equipment_designation'),
        Seuil.fraction.alias('fraction'),
        Seuil.stock_mini.alias('mini'),
        Seuil.stock_maxi.alias('maxi'),
        fn.IFNULL(ordered_quantity, 0).alias('ordered_quantity'),
        fn.IFNULL(stock_quantity, 0).alias('stk_quantity'),
        fn.IFNULL(stock_admin, 0).alias('stk_admin'))
            .join(Product, on=Seuil.reference == Product.reference)
            .join(Magasin, on=Seuil.zone == Magasin.type_mag)
            .where(expr)
            )


def get_obj_excel_threshold_v2(item):
    return {
        'fraction': item.fraction,
        'equipment': item.equipment_designation,
        'reference': item.reference,
        'designation': item.designation,
        'quantity': item.stk_quantity + item.stk_admin,
        'mini': item.mini,
        'maxi': item.maxi,
        'ordered_quantity': item.ordered_quantity,
        'command_quantity': (0 if item.stk_quantity + item.stk_admin + item.ordered_quantity >= item.mini
                             else item.maxi - (item.stk_quantity + item.stk_admin) + item.ordered_quantity)
    }
