import math
from datetime import datetime
from enum import Enum

from median.constant import HistoryType
from median.database import mysql_db
from median.models import Historique, Product, Seuil, Gtin, Magasin
from peewee import DoesNotExist, JOIN, fn, Tuple

from common.models import WebThresholdParameter, WebThresholdSheet


class State(Enum):
    Add = 0
    Delete = 1
    Increase = 2
    Decrease = 3
    Error = 4
    Edit = 5
    Flat = 6


def _get_current_param(type_mag):
    return (WebThresholdParameter
            .select(WebThresholdParameter.pk,
                    WebThresholdParameter.patient_number,
                    WebThresholdParameter.day_threshold_min,
                    WebThresholdParameter.day_threshold_max,
                    WebThresholdParameter.loading_pass_threshold,
                    WebThresholdParameter.consuption_max,
                    WebThresholdParameter.consuption_min,
                    )
            .join(Magasin, on=Magasin.pk == WebThresholdParameter.equipment_pk)
            .where(Magasin.type_mag == type_mag)).get()


def _get_state(item):
    state = State.Flat
    try:
        if item.cip_blister is None or item.cip_pass is None or item.cip_box is None:
            state = State.Error
        elif item.threshold_min is None and item.threshold_max is None:
            if ((item.threshold_max_calc is not None and item.threshold_max_calc > 0) or
               (item.threshold_min_calc is not None and item.threshold_min_calc > 0)):
                state = State.Add
            else:
                state = State.Flat
        elif item.threshold_min_calc is None and item.threshold_max_calc is None:
            state = State.Delete
        elif item.threshold_min < item.threshold_min_calc and item.threshold_max < item.threshold_max_calc:
            state = State.Increase
        elif item.threshold_min > item.threshold_min_calc and item.threshold_max > item.threshold_max_calc:
            state = State.Decrease
        elif item.threshold_min == item.threshold_min_calc and item.threshold_max == item.threshold_max_calc:
            state = State.Flat
        elif item.threshold_min != item.threshold_min_calc or item.threshold_max != item.threshold_max_calc:
            state = State.Edit
    except Exception:
        state = State.Error

    return state.value


def _sheet_to_obj(item):
    return {
        'state': _get_state(item),
        'pk': item.pk,
        'reference': item.reference,
        'reference_pk': item.reference_pk,
        'designation': item.designation,
        'fraction': item.fraction,
        'ucd': item.ucd,
        'quantity': item.annual_consumption_calc,
        'pass_number': item.pass_number,
        'threshold_min_user': item.threshold_min_user,
        'threshold_max_user': item.threshold_max_user,
        'calculation': {
            'replenish_delay': item.replenish_delay_calc,
            'facing_number': item.facing_number_calc,
            'threshold_max': item.threshold_max_calc,
            'threshold_min': item.threshold_min_calc
        },

        'threshold': {
            'min': item.threshold_min,
            'max': item.threshold_max,
        } if item.threshold_min is not None and item.threshold_max is not None else None,
        'cip': {
            'blister': item.cip_blister,
            'pass': item.cip_pass,
            'box': item.cip_box
        } if item.cip_blister is not None and item.cip_pass is not None and item.cip_box is not None else None
    }


def _get_sheet(annual_consumption, param, item, cip, equipment_pk):
    threshold_max_calc = None
    threshold_min_calc = None
    threshold_max_user = getattr(item, 'threshold_max_user', None)
    threshold_min_user = getattr(item, 'threshold_min_user', None)
    facing_number = 0
    replenish_delay = 0
    pass_after_replenish = 0
    pass_number = 0

    threshold_min_day = param.day_threshold_min
    threshold_max_day = param.day_threshold_max
    loading_pass = param.day_threshold_max
    min_consumption = param.consuption_min
    max_consumption = param.consuption_max
    if cip is not None and min_consumption <= annual_consumption <= max_consumption:
        threshold_min_calc = math.ceil(
            annual_consumption / 365 * threshold_min_day) if (threshold_min_day is not None and
                                                              threshold_min_day > 0) else 0

        qty_pass = cip.qt_pass if loading_pass else math.floor(cip.qt_pass / cip.qt_blister)

        if (threshold_min_calc is not None and threshold_max_day - threshold_min_day > 0 and qty_pass is not None
           and qty_pass > 0):

            threshold_max_calc = math.ceil(
                threshold_min_calc + (annual_consumption / 365 * (threshold_max_day - threshold_min_day)))

            threshold_max = threshold_max_calc if threshold_max_user is None else threshold_max_user
            threshold_min = threshold_min_calc if threshold_min_user is None else threshold_min_user
            pass_min_threshold = math.ceil(threshold_min / qty_pass)
            replenish_quantity = threshold_max - threshold_min

            replenish_case = math.ceil(replenish_quantity / cip.qt_boite) if cip.qt_boite > 0 else 0
            pass_after_replenish = math.ceil(((replenish_case * cip.qt_boite) / qty_pass) + pass_min_threshold)

            if annual_consumption / (365 * threshold_min_day) < 7:
                min = 7
            else:
                min = annual_consumption / (365 * threshold_min_day)

            max = annual_consumption / 365 * (threshold_max_day - threshold_min_day)

            pass_without_aide = math.ceil((min + max) / qty_pass)
            pass_with_aide = 0
            pass_number = pass_after_replenish \
                if pass_after_replenish > pass_with_aide + pass_without_aide else (
                   pass_with_aide + pass_without_aide)
            facing_number = math.ceil(pass_number / 2)
            replenish_delay = math.ceil(
                (replenish_case * cip.qt_pass) / (annual_consumption / 365)) if annual_consumption > 0 else 0

    sheet = WebThresholdSheet()
    sheet.reference = item.reference
    sheet.fraction = item.fraction
    sheet.ucd = item.ucd
    sheet.chrono = datetime.now()
    sheet.threshold_max = item.threshold_max
    sheet.threshold_min = item.threshold_min
    sheet.pass_number = pass_number
    sheet.annual_consumption_calc = annual_consumption
    sheet.threshold_max_user = threshold_max_user
    sheet.threshold_min_user = threshold_min_user
    sheet.threshold_max_calc = threshold_max_calc
    sheet.threshold_min_calc = threshold_min_calc
    sheet.facing_number_calc = facing_number
    sheet.replenish_delay_calc = replenish_delay
    sheet.pass_after_replenish_calc = pass_after_replenish
    if cip is not None:
        sheet.cip_blister = cip.qt_blister
        sheet.cip_pass = cip.qt_pass
        sheet.cip_box = cip.qt_boite
    sheet.equipment_pk = equipment_pk

    return sheet


def _saveParams(type_mag: str, params, equipment) -> WebThresholdParameter:
    try:
        param = _get_current_param(type_mag=type_mag)
    except DoesNotExist:
        param = WebThresholdParameter()

    param.consuption_min = int(params['consumption_min'])
    param.consuption_max = int(params['consumption_max'])
    param.patient_number = int(params['patient_number'])
    param.day_threshold_min = int(params['day_threshold_min'])
    param.day_threshold_max = int(params['day_threshold_max'])
    param.loading_pass_threshold = params['loading_pass_threshold']
    param.equipment_pk = equipment.pk

    param.save()
    return param


def _create_sheet_with_threshold(type_mag, ref_with_consumption, param, equipment):
    list_ref = set(map(lambda r: r['reference'], ref_with_consumption))
    list_ref_ucds = set(map(lambda r: r['gtin'], ref_with_consumption))

    items = list((Product.select(Product.reference.alias('reference'),
                                 Product.ucd.alias('ucd'),
                                 Seuil.stock_maxi.alias('threshold_max'),
                                 Seuil.stock_mini.alias('threshold_min'),
                                 Seuil.fraction.alias('fraction')
                                 )
                  .join(Seuil, on=((Seuil.reference == Product.reference) &
                                   (Seuil.zone == type_mag)))
                  .where(((Seuil.stock_mini > 0) | (Seuil.stock_maxi > 0)) &
                         (Product.reference.not_in(list_ref)))).objects())

    Cip_alias = Gtin.alias('cip_alias')
    cips = Cip_alias.select(fn.MAX(Gtin.qt_boite)).where(Cip_alias.ucd == Gtin.ucd)
    req_cip = list(Gtin.select(Gtin.cip, Gtin.qt_boite, Gtin.qt_blister, Gtin.qt_pass, Gtin.ucd)
                   .where((Gtin.ucd << list_ref_ucds) &
                          (Gtin.qt_boite == cips)).order_by(Gtin.qt_pass.desc()))
    res = []
    for item in items:
        cip = next(filter(lambda i: i.ucd == item.ucd, req_cip), None)
        sheet = _get_sheet(annual_consumption=0,
                           param=param,
                           item=item,
                           cip=cip,
                           equipment_pk=equipment.pk)
        res.append(sheet)

    with mysql_db.atomic():
        WebThresholdSheet.bulk_create(res, batch_size=100)


def _create_sheet_by_file(type_mag, file_item, param, equipment, division):
    list_ref_ucds = set(map(lambda r: r['gtin'], file_item['items']))
    list_threshold = set(map(lambda r: (r['reference'], r['fraction']), file_item['items']))
    items = file_item['items']

    thresholds = list(Seuil.select(Seuil.stock_mini, Seuil.stock_maxi, Seuil.fraction, Seuil.reference)
                      .where((Tuple(Seuil.reference, Seuil.fraction).in_(list_threshold)) &
                             (Seuil.zone == type_mag)))

    Cip_alias = Gtin.alias('cip_alias')
    cips = Cip_alias.select(fn.MAX(Gtin.qt_boite)).where(Cip_alias.ucd == Gtin.ucd)
    req_cip = list(Gtin.select(Gtin.cip, Gtin.qt_boite, Gtin.qt_blister, Gtin.qt_pass, Gtin.ucd)
                   .where((Gtin.ucd << list_ref_ucds) &
                          (Gtin.qt_boite == cips)).order_by(Gtin.qt_pass.desc()))
    res = []
    count = 0
    for item in items:
        cip = next(filter(lambda i: i.ucd == item['gtin'], req_cip), None)

        threshold = next(filter(lambda t: t.reference == item['reference'] and t.fraction == item['fraction'],
                                thresholds), None)

        if threshold is not None:
            item['threshold_max'] = threshold.stock_maxi
            item['threshold_min'] = threshold.stock_mini
        else:
            item['threshold_max'] = None
            item['threshold_min'] = None

        item['ucd'] = item['gtin']
        q = type('item', (object,), item)
        sheet = _get_sheet(annual_consumption=q.consumption / division,
                           param=param,
                           item=q,
                           cip=cip,
                           equipment_pk=equipment.pk)
        res.append(sheet)
        count = count + 1

    with mysql_db.atomic():
        WebThresholdSheet.bulk_create(res, batch_size=100)


def _create_sheet_by_date(type_mag, start, end, param, equipment, division):
    diff = 0
    min_date = (Historique.select(Historique.chrono)
                .where((Historique.poste == type_mag) &
                       (Historique.type_mouvement == HistoryType.Sortie.value) &
                       (Historique.chrono >= start) & (Historique.quantite_demande > 0))
                .order_by(Historique.chrono.asc())).limit(1)
    if any(min_date):
        diff = (end - min_date[0].chrono).days

    ref_with_consumption = (Historique
                            .select(Historique.reference.alias('reference'),
                                    Product.designation.alias('designation'),
                                    Historique.fraction.alias('fraction'),
                                    fn.IF(Product.ucd == '', Historique.ucd,  Product.ucd).alias('ucd'),
                                    Seuil.stock_mini.alias('threshold_min'),
                                    Seuil.stock_maxi.alias('threshold_max'),
                                    (fn.IFNULL(fn.SUM(Historique.quantite_demande), 0)).alias('quantity'))
                            .join(Product, on=Historique.reference == Product.reference)
                            .join(Seuil, JOIN.LEFT_OUTER, on=(Seuil.reference == Product.reference) &
                                                             (Seuil.fraction == Historique.fraction) &
                                                             (Seuil.zone == type_mag))
                            .where((fn.SUBSTRING(Historique.adresse, 1, 3) == equipment.mag) &
                                   (Historique.type_mouvement == HistoryType.Sortie.value) &
                                   (Historique.chrono >= start) &
                                   (Historique.chrono <= end))
                            .group_by(Historique.reference, Historique.fraction, Product.ucd))

    list_ref = ref_with_consumption.objects()

    list_ref_ucds = set(map(lambda r: r.ucd, list_ref))

    Cip_alias = Gtin.alias('cip_alias')
    cips = Cip_alias.select(fn.MAX(Gtin.qt_boite)).where(Cip_alias.ucd == Gtin.ucd)
    req_cip = list(Gtin.select(Gtin.cip, Gtin.qt_boite, Gtin.qt_blister, Gtin.qt_pass, Gtin.ucd)
                   .where((Gtin.ucd << list_ref_ucds) &
                          (Gtin.qt_boite == cips)).order_by(Gtin.qt_pass.desc()))
    res = []

    count = 0

    for item in list_ref:
        cip = next(filter(lambda i: i.ucd == item.ucd, req_cip), None)
        annual_consumption = math.ceil(365 * item.quantity / diff)
        sheet = _get_sheet(annual_consumption=(annual_consumption / division), param=param, item=item, cip=cip,
                           equipment_pk=equipment.pk)

        res.append(sheet)
        count = count + 1

    with mysql_db.atomic():
        WebThresholdSheet.bulk_create(res, batch_size=100)
    list_items = list(map(lambda r: {'reference': r.reference, 'gtin': r.ucd}, list_ref))
    _create_sheet_with_threshold(type_mag=type_mag, ref_with_consumption=list_items, param=param,
                                 equipment=equipment)
