import json
import math
import operator
import os
import uuid
from datetime import datetime
from functools import reduce
from itertools import groupby

import pandas as pd
from common.models import WebThresholdParameter
from common.models import WebThresholdSheet
from common.status import HTTP_200_OK, HTTP_204_NO_CONTENT, HTTP_500_INTERNAL_SERVER_ERROR
from dateutil import parser
from flask import Blueprint, request
from flask_jwt_extended import jwt_required
from median.constant import PickingMachineType
from median.models import Product, Seuil, Gtin, Magasin, Ucd, Patient, Adresse, Cip
from peewee import fn, DoesNotExist

from ressources.blueprint.threshold_simulator.threshold_sim_service import _get_sheet, _saveParams, \
    _create_sheet_by_date, _create_sheet_by_file, _create_sheet_with_threshold, _sheet_to_obj, _get_current_param

threshold_sim_blueprint = Blueprint('threshold_sim', __name__)


@threshold_sim_blueprint.route('<string:type_mag>/<string:sheet_pk>', methods=['DELETE'])
@jwt_required()
def delete_sheet(type_mag, sheet_pk):
    item = (WebThresholdSheet.select()
            .join(Magasin, on=Magasin.pk == WebThresholdSheet.equipment_pk)
            .where((WebThresholdSheet.pk == sheet_pk) &
                   (Magasin.type_mag == type_mag))).get()

    item.delete_instance()
    return {}, HTTP_204_NO_CONTENT


@threshold_sim_blueprint.route('<string:type_mag>/<string:sheet_pk>', methods=['PUT'])
@jwt_required()
def update_sheet(type_mag, sheet_pk):
    data = json.loads(request.data)

    item = (WebThresholdSheet.select()
            .join(Magasin, on=Magasin.pk == WebThresholdSheet.equipment_pk)
            .where((WebThresholdSheet.pk == sheet_pk) &
                   (Magasin.type_mag == type_mag))).get()

    item.threshold_max_user = int(data.get('threshold_max', None)) if data.get('threshold_max',
                                                                               None) is not None else None
    item.threshold_min_user = int(data.get('threshold_min', None)) if data.get('threshold_min',
                                                                               None) is not None else None

    param = _get_current_param(type_mag=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 == item.ucd) &
                           (Gtin.qt_boite == cips)).order_by(Gtin.qt_pass.desc())).limit(1))

    cip = Gtin()
    if not any(req_cip):
        cip.qt_boite = item.cip_box
        cip.qt_blister = item.cip_blister
        cip.qt_pass = item.cip_pass
    else:
        cip = req_cip[0]

    sheet = _get_sheet(annual_consumption=item.annual_consumption_calc, cip=cip,
                       equipment_pk=item.equipment_pk, item=item, param=param)
    sheet.pk = item.pk
    sheet.save()

    return {}, HTTP_204_NO_CONTENT


@threshold_sim_blueprint.route('<string:type_mag>/<string:sheet_pk>', methods=['GET'])
@jwt_required()
def get_sheet_line(type_mag, sheet_pk):
    req = (WebThresholdSheet.select(
        WebThresholdSheet.pk.alias('pk'),
        WebThresholdSheet.reference.alias('reference'),
        WebThresholdSheet.fraction.alias('fraction'),
        Product.designation.alias('designation'),
        Product.pk.alias('reference_pk'),
        WebThresholdSheet.ucd.alias('ucd'),
        WebThresholdSheet.annual_consumption_calc.alias('annual_consumption_calc'),
        WebThresholdSheet.threshold_min.alias('threshold_min'),
        WebThresholdSheet.threshold_max.alias('threshold_max'),
        WebThresholdSheet.cip_blister.alias('cip_blister'),
        WebThresholdSheet.cip_box.alias('cip_box'),
        WebThresholdSheet.cip_pass.alias('cip_pass'),
        WebThresholdSheet.pass_number.alias('pass_number'),
        WebThresholdSheet.facing_number_calc.alias('facing_number_calc'),
        WebThresholdSheet.replenish_delay_calc.alias('replenish_delay_calc'),
        WebThresholdSheet.threshold_max_calc.alias('threshold_max_calc'),
        WebThresholdSheet.threshold_min_calc.alias('threshold_min_calc'),
        WebThresholdSheet.threshold_min_user.alias('threshold_min_user'),
        WebThresholdSheet.threshold_max_user.alias('threshold_max_user')
    )
           .join(Product, on=Product.reference == WebThresholdSheet.reference)
           .join(Magasin, on=Magasin.pk == WebThresholdSheet.equipment_pk)
           .join(WebThresholdParameter, on=WebThresholdParameter.equipment_pk == Magasin.pk)
           .where((Magasin.type_mag == type_mag) & (WebThresholdSheet.pk == sheet_pk))).objects()

    return {'data': _sheet_to_obj(req[0])}, HTTP_200_OK


@threshold_sim_blueprint.route('<string:type_mag>/params', methods=['GET'])
@jwt_required()
def get_params(type_mag):
    try:
        param = (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()
    except DoesNotExist:
        mag = (Magasin.select(Magasin.pk).where((Magasin.type_mag == type_mag))).get()
        param = WebThresholdParameter()
        param.equipment_pk = mag.pk
        param.save()

    patients_count = param.patient_number if param.patient_number is not None else Patient.select(Patient.pk).count()
    return {
        'pk': param.pk,
        'patient_number': patients_count,
        'day_threshold_min': param.day_threshold_min,
        'day_threshold_max': param.day_threshold_max,
        'loading_pass_threshold': param.loading_pass_threshold,
        'consumption_max': param.consuption_max,
        'consumption_min': param.consuption_min,
    }, HTTP_200_OK


@threshold_sim_blueprint.route('<string:type_mag>/params/<string:param_pk>', methods=['PUT'])
@jwt_required()
def update_params(type_mag, param_pk):
    data = json.loads(request.data)

    param = (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((WebThresholdParameter.pk == param_pk) & (Magasin.type_mag == type_mag))).get()

    param.patient_number = data['patient_number']
    param.consuption_min = data['consumption_min']
    param.consuption_max = data['consumption_max']
    param.day_threshold_min = data['day_threshold_min']
    param.day_threshold_max = data['day_threshold_max']
    param.loading_pass_threshold = data['loading_pass_threshold']

    param.save()

    return {
    }, HTTP_204_NO_CONTENT


@threshold_sim_blueprint.route('<string:type_mag>/total', methods=['GET'])
@jwt_required()
def get_total_sheet(type_mag):
    # Check if warehous exists
    mag = Magasin.get_or_none(type_mag=type_mag)
    if mag is None:
        return {
            'alertMessage': 'threshold.sim.warehouse.unknown',
            'param': []
        }, HTTP_500_INTERNAL_SERVER_ERROR

    if mag.eco_type not in ('C', 'L'):
        return {
            'alertMessage': 'threshold.sim.warehouse.eco.type.error',
            'param': []
        }, HTTP_500_INTERNAL_SERVER_ERROR

    try:
        item = (WebThresholdSheet
                .select(fn.COUNT(WebThresholdSheet.pk).alias('nb_ref'),
                        fn.IFNULL(
                            fn.SUM(fn.IFNULL(WebThresholdSheet.facing_number, WebThresholdSheet.facing_number_calc)),
                            0)
                        .alias('total_facing'),
                        fn.IFNULL(fn.SUM(fn.IFNULL(WebThresholdSheet.annual_consumption,
                                                   WebThresholdSheet.annual_consumption_calc)), 0)
                        .alias('total_consomption'),
                        fn.IFNULL(fn.SUM(WebThresholdSheet.pass_after_replenish_calc), 0).alias('total_pass')
                        )
                .join(Magasin, on=Magasin.pk == WebThresholdSheet.equipment_pk)
                .join(WebThresholdParameter, on=WebThresholdParameter.equipment_pk == Magasin.pk)
                .where((Magasin.type_mag == type_mag) &
                       (WebThresholdSheet.annual_consumption_calc >= WebThresholdParameter.consuption_min) &
                       (WebThresholdSheet.annual_consumption_calc <= WebThresholdParameter.consuption_max) &
                       ~WebThresholdSheet.is_closed)).get()
    except DoesNotExist:
        item = {
            'total_facing': 0,
            'total_facing_simple': 0,
            'total_pass': 0,
            'nb_ref': 0,
            'total_consomption': 0,
            'total_per_pillbox': 0
        }

    try:
        param = (WebThresholdParameter.select(
            WebThresholdParameter.patient_number
        ).join(
            Magasin, on=Magasin.pk == WebThresholdParameter.equipment_pk
        ).where((Magasin.type_mag == type_mag))).get()
    except DoesNotExist:
        param = None

    patients_count = param and param.patient_number or Patient.select(Patient.pk).count()

    try:
        global_facing = (Adresse.select(Adresse.pk).join(
            Magasin, on=Magasin.mag == Adresse.magasin
        ).where(
            (Magasin.type_mag == type_mag) &
            (((Magasin.type_machine << [PickingMachineType.Acced_Dose_Unit.value,
                                        PickingMachineType.Aide_V3.value]) & Adresse.adresse.contains('BP')) |
             ((Magasin.type_machine << [PickingMachineType.Acced_Boite_Pass.value, PickingMachineType.Acced_110.value,
                                        PickingMachineType.Aide_V2.value]) & Adresse.format.contains('BOITE PASS'))) &
            (Adresse.adresse.endswith('1'))
        )
        ).count()
    except DoesNotExist:
        global_facing = 0

    return {
        'total_facing': item.total_facing,
        'total_global_facing': global_facing,
        'total_facing_simple': item.total_facing * 2 - item.total_pass,
        'total_pass': item.total_pass,
        'nb_ref': item.nb_ref,
        'total_consomption': item.total_consomption,
        'total_per_pillbox': item.total_consomption / 365 / patients_count,
    }, HTTP_200_OK


@threshold_sim_blueprint.route('<string:type_mag>/sheet', methods=['POST'])
@jwt_required()
def get_current_sheet(type_mag):
    data = json.loads(request.data)
    search_list = data.get('criterias', [])
    states = data.get('states', [])

    if len(search_list) > 0:
        lst = list(map(lambda s: (
            (WebThresholdSheet.reference.contains(s.strip())) |
            (fn.LOWER(Product.designation).contains(s.strip()))
        ), search_list))
        search = reduce(operator.and_, lst)
    else:
        search = True

    req = (WebThresholdSheet.select(
        WebThresholdSheet.pk.alias('pk'),
        WebThresholdSheet.reference.alias('reference'),
        WebThresholdSheet.fraction.alias('fraction'),
        Product.designation.alias('designation'),
        Product.pk.alias('reference_pk'),
        WebThresholdSheet.ucd.alias('ucd'),
        WebThresholdSheet.annual_consumption_calc.alias('annual_consumption_calc'),
        WebThresholdSheet.threshold_min.alias('threshold_min'),
        WebThresholdSheet.threshold_max.alias('threshold_max'),
        WebThresholdSheet.cip_blister.alias('cip_blister'),
        WebThresholdSheet.cip_box.alias('cip_box'),
        WebThresholdSheet.cip_pass.alias('cip_pass'),
        WebThresholdSheet.facing_number_calc.alias('facing_number_calc'),
        WebThresholdSheet.replenish_delay_calc.alias('replenish_delay_calc'),
        WebThresholdSheet.threshold_max_calc.alias('threshold_max_calc'),
        WebThresholdSheet.threshold_min_calc.alias('threshold_min_calc'),
        WebThresholdSheet.threshold_min_user.alias('threshold_min_user'),
        WebThresholdSheet.threshold_max_user.alias('threshold_max_user'),
        WebThresholdSheet.pass_number.alias('pass_number')
    )
           .join(Product, on=Product.reference == WebThresholdSheet.reference)
           .join(Magasin, on=Magasin.pk == WebThresholdSheet.equipment_pk)
           .join(WebThresholdParameter, on=WebThresholdParameter.equipment_pk == Magasin.pk)
           .where((Magasin.type_mag == type_mag) &
                  (~WebThresholdSheet.is_closed) &
                  search)
           .order_by(WebThresholdSheet.annual_consumption_calc.desc()))

    items = [_sheet_to_obj(item) for item in req.objects()]

    if len(states) > 0:
        items = list(filter(lambda i: i['state'] in states, items))

    req_state = (WebThresholdSheet.select(
        WebThresholdSheet.pk.alias('pk'),
        WebThresholdSheet.reference.alias('reference'),
        WebThresholdSheet.fraction.alias('fraction'),
        Product.designation.alias('designation'),
        Product.pk.alias('reference_pk'),
        WebThresholdSheet.ucd.alias('ucd'),
        WebThresholdSheet.annual_consumption_calc.alias('annual_consumption_calc'),
        WebThresholdSheet.threshold_min.alias('threshold_min'),
        WebThresholdSheet.threshold_max.alias('threshold_max'),
        WebThresholdSheet.cip_blister.alias('cip_blister'),
        WebThresholdSheet.cip_box.alias('cip_box'),
        WebThresholdSheet.cip_pass.alias('cip_pass'),
        WebThresholdSheet.facing_number_calc.alias('facing_number_calc'),
        WebThresholdSheet.replenish_delay_calc.alias('replenish_delay_calc'),
        WebThresholdSheet.threshold_max_calc.alias('threshold_max_calc'),
        WebThresholdSheet.threshold_min_calc.alias('threshold_min_calc'),
        WebThresholdSheet.threshold_min_user.alias('threshold_min_user'),
        WebThresholdSheet.threshold_max_user.alias('threshold_max_user'),
        WebThresholdSheet.pass_number.alias('pass_number')
    )
                 .join(Product, on=Product.reference == WebThresholdSheet.reference)
                 .join(Magasin, on=Magasin.pk == WebThresholdSheet.equipment_pk)
                 .join(WebThresholdParameter, on=WebThresholdParameter.equipment_pk == Magasin.pk)
                 .where((Magasin.type_mag == type_mag) &
                        (~WebThresholdSheet.is_closed)))
    states = [_sheet_to_obj(item) for item in req_state.objects()]

    state_count = [
        {
            "value": k,
            "count": len([d["state"] for d in g])
        }
        for k, g in groupby(sorted(states, key=lambda d: d["state"]),
                            key=lambda d: d["state"])
    ]

    return {
        'states': state_count,
        'list': items,

    }, HTTP_200_OK


@threshold_sim_blueprint.route('<string:type_mag>', methods=['PATCH'])
@jwt_required()
def recalculate_sheet(type_mag):
    param = (WebThresholdParameter
             .select(WebThresholdParameter.day_threshold_min,
                     WebThresholdParameter.day_threshold_max,
                     WebThresholdParameter.consuption_min,
                     WebThresholdParameter.consuption_max,
                     WebThresholdParameter.loading_pass_threshold)
             .join(Magasin, on=Magasin.pk == WebThresholdParameter.equipment_pk)
             .where((Magasin.type_mag == type_mag))).get()

    req = (WebThresholdSheet.select(
        WebThresholdSheet.pk.alias('pk'),
        Magasin.pk.alias('equipment_pk'),
        WebThresholdSheet.reference.alias('reference'),
        WebThresholdSheet.fraction.alias('fraction'),
        Product.designation.alias('designation'),
        WebThresholdSheet.ucd.alias('ucd'),
        WebThresholdSheet.annual_consumption_calc.alias('annual_consumption_calc'),
        WebThresholdSheet.threshold_min.alias('threshold_min'),
        WebThresholdSheet.threshold_max.alias('threshold_max'),
        WebThresholdSheet.cip_blister.alias('cip_blister'),
        WebThresholdSheet.cip_box.alias('cip_box'),
        WebThresholdSheet.cip_pass.alias('cip_pass'),
        WebThresholdSheet.facing_number_calc.alias('facing_number_calc'),
        WebThresholdSheet.replenish_delay_calc.alias('replenish_delay_calc'),
        WebThresholdSheet.threshold_max_calc.alias('threshold_max_calc'),
        WebThresholdSheet.threshold_min_calc.alias('threshold_min_calc'),
        WebThresholdSheet.threshold_max_user.alias('threshold_max_user'),
        WebThresholdSheet.threshold_min_user.alias('threshold_min_user'),
        WebThresholdSheet.pass_number.alias('pass_number')
    )
           .join(Product, on=Product.reference == WebThresholdSheet.reference)
           .join(Magasin, on=Magasin.pk == WebThresholdSheet.equipment_pk)
           .where((Magasin.type_mag == type_mag) &
                  (~WebThresholdSheet.is_closed)))

    for item in req.objects():
        cip = None
        if item.cip_box is not None or item.cip_blister is not None or item.cip_pass is not None:
            cip = Gtin()
            cip.qt_pass = item.cip_pass
            cip.qt_blister = item.cip_blister
            cip.qt_boite = item.cip_box

        sheet = _get_sheet(annual_consumption=item.annual_consumption_calc, cip=cip,
                           equipment_pk=item.equipment_pk, item=item, param=param)
        sheet.pk = item.pk
        sheet.save()
    return {}, HTTP_204_NO_CONTENT


def verify_line(reference, fraction, gtin, consumption, errors, cip):
    product = None

    try:
        product = (Product.select()
                   .join(Ucd, on=Ucd.reference == Product.reference)
                   .where(Product.reference == reference)).get()
    except DoesNotExist:
        errors.append('threshold_simulator.error.reference.unknown')

    if product is not None:
        ucd = None
        try:
            ucd = Ucd.select().where((Ucd.reference == reference) & (Ucd.ucd == gtin)).get()
        except DoesNotExist:
            errors.append('threshold_simulator.error.gtin.unknown')

        if ucd is not None and cip is not None:
            try:
                Cip.select().where((Cip.ucd == gtin) & (Cip.cip == cip)).get()
            except DoesNotExist:
                errors.append('threshold_simulator.error.cip.unknown')

    if math.isnan(consumption):
        errors.append('threshold_simulator.error.consumption.nan')

    if math.isnan(fraction):
        errors.append('threshold_simulator.error.fraction.nan')

    return len(errors) == 0


@threshold_sim_blueprint.route('analyze', methods=['PUT'])
@jwt_required()
def analyzeFile():
    num_ref = int(request.form['reference'])
    num_fraction = int(request.form['fraction'])
    num_gtin = int(request.form['gtin'])
    num_cip = int(request.form.get('cip', 0)) if (request.form.get('cip', None) is not None and
                                                  request.form.get('cip', None) != '') \
        else None
    num_consumption = int(request.form['annual_consumption'])

    for fname in request.files:
        f = request.files.get(fname)

        file_name = f'{uuid.uuid4()}.xlsx'

        directory = os.sep.join(
            [os.getcwd(), "tmp_export"])
        file = f'{directory}\\{file_name}'

        if not os.path.exists(directory):
            try:
                os.makedirs(directory, exist_ok=True)
            except OSError:
                return {
                    'alertMessage': 'ui.error.directory.creation',
                    'params': [directory]}, HTTP_500_INTERNAL_SERVER_ERROR

        try:
            f.save(file)

            df = pd.read_excel(file, sheet_name=0, header=None)
            num_line = 1
            reports = []
            for item in df.values.tolist():
                if num_line != 1:
                    report = {
                        'line': num_line,
                        'errors': [],
                        'reference': None,
                        'fraction': None,
                        'gtin': None,
                        'consumption': None,
                        'cip': None,
                    }
                    try:
                        reference = item[num_ref - 1]
                        report['reference'] = reference
                        fraction = item[num_fraction - 1]
                        report['fraction'] = fraction
                        gtin = str(item[num_gtin - 1])
                        report['gtin'] = gtin
                        consumption = item[num_consumption - 1]
                        report['consumption'] = consumption

                        cip = None
                        if num_cip is not None:
                            cip = item[num_cip - 1]
                        report['cip'] = cip

                        verify_line(fraction=fraction, reference=reference, gtin=gtin,
                                    consumption=consumption, errors=report['errors'],
                                    cip=cip)
                    except Exception:
                        report['errors'].append('threshold_simulator.error.column_number')
                    reports.append(report)
                num_line += 1

        finally:
            if os.path.exists(file):
                os.remove(file)

        return {
            'list': [{
                'reference': item['reference'],
                'fraction': item['fraction'],
                'gtin': str(item['gtin']),
                'consumption': item['consumption'],
                'cip': item['cip'],
                'errors': item['errors']} for item in reports]
        }, HTTP_200_OK


@threshold_sim_blueprint.route('<string:type_mag>', methods=['PUT'])
@jwt_required()
def update_sheet_threshold(type_mag):
    sheets = (WebThresholdSheet.select()
              .join(Magasin, on=WebThresholdSheet.equipment_pk == Magasin.pk)
              .where(Magasin.type_mag == type_mag))

    for sheet in sheets:

        try:
            threshold = (Seuil.select()
                         .where((Seuil.reference == sheet.reference) &
                                (Seuil.fraction == sheet.fraction) &
                                (Seuil.zone == type_mag))).get()
        except DoesNotExist:
            threshold = Seuil()
            threshold.zone = type_mag
            threshold.reference = sheet.reference
            threshold.fraction = sheet.fraction

        threshold_min = 0
        threshold_max = 0

        if sheet.threshold_min_user is not None:
            threshold_min = sheet.threshold_min_user
        elif sheet.threshold_min_calc is not None:
            threshold_min = sheet.threshold_min_calc
        threshold.stock_mini = threshold_min

        if sheet.threshold_max_user is not None:
            threshold_max = sheet.threshold_max_user
        elif sheet.threshold_min_calc is not None:
            threshold_max = sheet.threshold_max_calc
        threshold.stock_maxi = threshold_max

        threshold.save()
        sheet.is_closed = True
        sheet.closing_date = datetime.utcnow()
        sheet.save()

    return {}, HTTP_204_NO_CONTENT


@threshold_sim_blueprint.route('<string:type_mag>', methods=['POST'])
@jwt_required()
def create_sheet(type_mag: str):
    data = json.loads(request.data)

    dates = data['dates']
    file = data['file']
    params = data['params']

    equipment = Magasin.select(Magasin.mag, Magasin.pk).where(Magasin.type_mag == type_mag).get()
    param = _saveParams(type_mag=type_mag, params=params, equipment=equipment)

    (WebThresholdSheet.update(is_closed=True, closing_date=datetime.utcnow())
     .where(WebThresholdSheet.equipment_pk == equipment.pk).execute())
    division = float(params.get('division', 1.0))
    if dates is not None:
        start = parser.parse(dates['start'])
        end = parser.parse(dates['end'])
        _create_sheet_by_date(type_mag=type_mag, start=start, end=end, param=param,
                              equipment=equipment, division=division)
    elif file is not None:
        _create_sheet_by_file(type_mag=type_mag, file_item=file, param=param, equipment=equipment)
        _create_sheet_with_threshold(type_mag=type_mag, ref_with_consumption=file['items'], param=param,
                                     equipment=equipment, division=division)

    return {}, HTTP_204_NO_CONTENT
