import datetime
import math
import os
from enum import Enum

import numpy as np
import peewee
from median.models import SuiviProd, Config, Compteur, ListeValide
from peewee import fn, DoesNotExist
from ressources.blueprint.stats_utils import estimate_coef, lineary_regression

CONTRAT_DOSE_PILLBOX = 'contract_dose_pillbox'
CONTRAT_CADENCE_PILLBOX = 'contract_cadence_pillbox'
CONTRAT_CADENCE_DOSE_PILLBOX = 'contract_cadence_dose_pillbox'

CONTRAT_DOSE_STACK = 'contract_dose_stack'
CONTRAT_CADENCE_STACK = 'contract_cadence_stack'
CONTRAT_CADENCE_DOSE_STACK = 'contract_cadence_dose_stack'

CONTRAT_COMPLETION_PERCENTAGE = 'contract_completion_percentage'

CONTRAT_AVALABILITY_PERCENTAGE = 'contract_availability_percentage'
CONTRAT_CUT_REJECT_PERCENTAGE = 'contract_cut_rejet_percentage'
CONTRAT_BLOCKED_BOX_PER_HOUR = 'contract_blocked_box_per_hour'
CONTRAT_DUPLICATE_THOUSAND = 'contract_duplicate_per_thousand'

suivi_prod_db = os.environ.get('MEDIAN_DB_NAME_SUIVI_PROD', None)


class State(Enum):
    OK = 1
    WARNING = 0
    KO = -1
    UNKNOW = None


class Period(Enum):
    LAST30 = 0
    CURRENT_MONTH = 1
    CURRENT_YEAR = 2
    PREV_YEAR = 3


def getSumValue(variable, counter_values):
    v = list(map(lambda x: x['value'], filter(lambda c: c['variable'] == variable, counter_values)))
    return sum(v) if v is not None else 0


def get_last_value(variable, counter_values):
    v = list(map(lambda x: x['value'], filter(lambda c: c['variable'] == variable, counter_values)))

    return v[-1] if v is not None and len(v) > 0 else 0


def getMeanValue(variable, counter_values):
    v = list(map(lambda x: x['value'], filter(lambda c: c['variable'] == variable, counter_values)))

    return round(np.mean(np.array(v)), 2) if v is not None and len(v) > 1 else 0


def __trends_coeff(val_counts, val_times):
    counts = np.array(val_counts)
    times = np.array(val_times)
    count_coeff = estimate_coef(times, counts) if len(counts) > 0 else None
    count_r2 = lineary_regression(count_coeff, times, counts) if (count_coeff is not None and
                                                                  count_coeff[0] is not None and
                                                                  count_coeff[1] is not None) else None

    return round(count_coeff[0] / abs(
        count_coeff[1]) * 100.0, 2) if count_r2 is not None and count_r2 > 0.65 else None


def isSuivi_db_connected():
    is_connected = True

    if suivi_prod_db is None or len(suivi_prod_db.strip()) == 0:
        is_connected = False
    else:
        try:
            list(SuiviProd.select().limit(1))
        except peewee.OperationalError:
            is_connected = False
    return is_connected


def get_counters(start, end, type_mags, all_variables=None):
    if not isSuivi_db_connected():
        return []

    variables = [
        'API_Nbr_de_Pilulier_Med', 'API_Nbr_DU_Pose_Med', 'API_Nbr_Sachet_Pose_Med', 'API_Nbr_SachetP_Pose_Med',
        'API_Nbr_Carnet_Pilulier_Med', 'API_Nbr_Sachet_Pose_Carnet_Med', 'API_Nbr_SachetP_Pose_Carnet_Med',
        'API_Nbr_Du_Pose_Carnet_Med',
        'API_Temps_Cueil_Pil_Med',
        'API_Temps_Cueil_Carnet_Med', 'API_Nbr_Carnet_Pilulier_heure_Med', 'API_Nbr_de_Pilulier_heure_Med',
        'API_DISPONIBILITE_Med',
        'API_Nbr_Ticket_Pose_Med', 'API_Nbr_Ticket_Pose_Carnet_Med',
        'API_Temps_Panne_Med',
        'API_TEMPS_ATTENTE_OPERATEUR_Med', 'API_TEMPS_ATTENTE_ECRAN_Med', 'API_TEMPS_RESOLUTION_DEFAUT_Med',
        'API_TEMPS_UTILISATION_Med', 'API_Temps_Dchgt_Plateau_Med',

        'API_Nbr_Coupe_Med', 'API_Nbr_BoitePass_CP_Med',
        'API_Nbr_Rejet_Dlect_E1_Remp_Med', 'API_Nbr_Rejet_Dlect_E2_Remp_Med',  # cut read defect
        'API_Nbr_Rejet_Dpd_E1_Remp_Med', 'API_Nbr_Rejet_Dpd_E2_Remp_Med',  # cut dose presence defect
        'API_Nbr_Rejet_Dps_E1_Remp_Med', 'API_Nbr_Rejet_Dps_E2_Remp_Med',  # cut sachet presence defect
        'API_Nbr_Rejet_Dlect_E1_Cu_Med', 'API_Nbr_Rejet_Dlect_E2_Cu_Med',  # picking read defect
        'API_Nbr_Rejet_Dpd_E1_Cu_Med', 'API_Nbr_Rejet_Dpd_E2_Cu_Med',  # picking dose presence defect
        'API_Nbr_Rejet_Dps_E1_Cu_Med', 'API_Nbr_Rejet_Dps_E2_Cu_Med',  # picking sachet presence defect

    ] if all_variables is None else all_variables

    counter_values = get_datas_query(start=start, end=end, type_mag=type_mags, variables=variables)

    for value in counter_values:
        pillbox_doses_num = ((getSumValue('API_Nbr_DU_Pose_Med', value['variables']) +
                              getSumValue('API_Nbr_Sachet_Pose_Med', value['variables']) +
                              getSumValue('API_Nbr_SachetP_Pose_Med', value['variables'])))
        pillbox_number = getSumValue('API_Nbr_de_Pilulier_Med', value['variables'])

        stack_doses_num = ((getSumValue('API_Nbr_Sachet_Pose_Carnet_Med', value['variables']) +
                            getSumValue('API_Nbr_SachetP_Pose_Carnet_Med', value['variables']) +
                            getSumValue('API_Nbr_Du_Pose_Carnet_Med', value['variables'])))
        stack_number = getSumValue('API_Nbr_Carnet_Pilulier_Med', value['variables'])

        picking_pillbox_time = getSumValue('API_Temps_Cueil_Pil_Med', value['variables'])
        picking_stack_time = getSumValue('API_Temps_Cueil_Carnet_Med', value['variables'])
        value['picking_time'] = picking_pillbox_time + picking_stack_time

        # cut
        value['cut_number'] = getSumValue('API_Nbr_Coupe_Med', value['variables'])
        value['cut_stock'] = getSumValue('API_Nbr_BoitePass_CP_Med', value['variables'])
        value['cut_e1_defect_presence'] = (getSumValue('API_Nbr_Rejet_Dpd_E1_Remp_Med', value['variables']) +
                                           getSumValue('API_Nbr_Rejet_Dps_E1_Remp_Med', value['variables']))
        value['cut_e2_defect_presence'] = (getSumValue('API_Nbr_Rejet_Dpd_E2_Remp_Med', value['variables']) +
                                           getSumValue('API_Nbr_Rejet_Dps_E2_Remp_Med', value['variables']))
        value['cut_e1_defect_read'] = getSumValue('API_Nbr_Rejet_Dlect_E1_Remp_Med', value['variables'])
        value['cut_e2_defect_read'] = getSumValue('API_Nbr_Rejet_Dlect_E2_Remp_Med', value['variables'])
        value['cut_e1_defect_total'] = value['cut_e1_defect_presence'] + value['cut_e1_defect_read']
        value['cut_e2_defect_total'] = value['cut_e2_defect_presence'] + value['cut_e2_defect_read']

        value['picking_e1_defect_presence'] = (getSumValue('API_Nbr_Rejet_Dpd_E1_Cu_Med', value['variables']) +
                                               getSumValue('API_Nbr_Rejet_Dps_E1_Cu_Med', value['variables']))
        value['picking_e2_defect_presence'] = (getSumValue('API_Nbr_Rejet_Dpd_E2_Cu_Med', value['variables']) +
                                               getSumValue('API_Nbr_Rejet_Dps_E2_Cu_Med', value['variables']))
        value['picking_e1_defect_read'] = getSumValue('API_Nbr_Rejet_Dlect_E1_Cu_Med', value['variables'])
        value['picking_e2_defect_read'] = getSumValue('API_Nbr_Rejet_Dlect_E2_Cu_Med', value['variables'])
        value['picking_e1_defect_total'] = value['cut_e1_defect_presence'] + value['cut_e1_defect_read']
        value['picking_e2_defect_total'] = value['cut_e2_defect_presence'] + value['cut_e2_defect_read']
        value['cut_rejet'] = value['cut_number'] - value['cut_stock']

        # time
        value['time_loading'] = getSumValue('API_Temps_Chgt_Blister_Med',
                                            value['variables'])
        value['time_waiting_ope'] = getSumValue('API_TEMPS_ATTENTE_OPERATEUR_Med',
                                                value['variables']) / 3600  # Tps attente opérateur
        value['time_waiting_before_defect'] = getSumValue('API_TEMPS_ATTENTE_ECRAN_Med',
                                                          value['variables']) / 3600  # Tps avt intervention panne
        value['time_defect_resolve'] = getSumValue('API_TEMPS_RESOLUTION_DEFAUT_Med',
                                                   value['variables']) / 3600  # Tps de cycle panne
        value['time_working'] = getSumValue('API_TEMPS_UTILISATION_Med',
                                            value['variables']) / 3600  # Tps de cycle total
        value['time_load_tray'] = getSumValue('API_Temps_Dchgt_Plateau_Med',
                                              value['variables'])  # Tps de déchargement plateau

        # Pillbox
        value['pillbox_num'] = pillbox_number
        value['pillbox_dose_num'] = pillbox_doses_num
        value['pillbox_unit_dose'] = getSumValue('API_Nbr_DU_Pose_Med', value['variables'])
        value['pillbox_sachet'] = getSumValue('API_Nbr_Sachet_Pose_Med', value['variables'])
        value['pillbox_powder_sachet'] = getSumValue('API_Nbr_SachetP_Pose_Med', value['variables'])
        value['picking_pillbox_time'] = picking_pillbox_time
        value['pillbox_ticket'] = getSumValue('API_Nbr_Ticket_Pose_Med', value['variables'])

        value['pillbox_cadence_dose'] = pillbox_doses_num / picking_pillbox_time if picking_pillbox_time > 0 else 0
        value['pillbox_dose'] = pillbox_doses_num / pillbox_number if pillbox_number > 0 else 0
        value['pillbox_cadence_h'] = pillbox_number / picking_pillbox_time if picking_pillbox_time > 0 else 0

        # Stack
        value['stack_num'] = stack_number
        value['stack_dose_num'] = stack_doses_num
        value['stack_cadence_dose'] = stack_doses_num / picking_stack_time if picking_stack_time > 0 else 0
        value['stack_dose'] = stack_doses_num / stack_number if stack_number > 0 else 0
        value['stack_cadence_h'] = stack_number / picking_stack_time if picking_stack_time > 0 else 0
        value['stack_unit_dose'] = getSumValue('API_Nbr_DU_Pose_Med', value['variables'])
        value['stack_sachet'] = getSumValue('API_Nbr_Sachet_Pose_Carnet_Med', value['variables'])
        value['stack_powder_sachet'] = getSumValue('API_Nbr_Du_Pose_Carnet_Med', value['variables'])
        value['picking_stack_time'] = picking_stack_time
        value['stack_ticket'] = getSumValue('API_Nbr_Ticket_Pose_Carnet_Med', value['variables'])

        value['availability'] = round(getMeanValue('API_DISPONIBILITE_Med', value['variables']), 2)
    return counter_values


def get_trends(val_counts, val_weeks):
    counts = np.array(val_counts)

    weeks = np.array(range(len(val_weeks)))
    count_coeff = estimate_coef(weeks, counts) if len(counts) > 0 else None
    count_r2 = lineary_regression(count_coeff, weeks, counts) \
        if count_coeff is not None and count_coeff[0] is not None and count_coeff[1] is not None else None

    return {
        'datas': val_counts,
        'weeks': val_weeks,
        'trends': round(count_coeff[0] / abs(
            count_coeff[1]) * 100.0, 2) if count_r2 is not None and count_r2 > 0.65 and count_coeff[1] > 0 else None,

    }


def _get_config(property):
    try:
        config = (Config.select(Config.value)
                  .where((Config.propriete == property))).get()
        value = config.value
    except DoesNotExist:
        value = 0
    return int(value)


def get_datas_query(start, end, type_mag, variables):
    if not isSuivi_db_connected():
        return []

    subquery = (SuiviProd.select(
        fn.DATE(SuiviProd.chrono).alias('date'),
        SuiviProd.poste.alias('poste'),
        SuiviProd.variable.alias('variable'),
        fn.MAX(SuiviProd.pk).alias('max_pk'))
                .where((SuiviProd.chrono < end) &
                       (SuiviProd.chrono >= start) &
                       (SuiviProd.variable << variables) &
                       (SuiviProd.poste << type_mag))
                .group_by(SuiviProd.poste, SuiviProd.variable, fn.DATE(SuiviProd.chrono)))

    counters = (SuiviProd
                .select(SuiviProd.pk, SuiviProd.variable, SuiviProd.valeur_num,
                        SuiviProd.valeur_alpha, fn.DATE(SuiviProd.chrono).alias('date'))
                .join(subquery, on=((subquery.c.max_pk == SuiviProd.pk) &
                                    (subquery.c.variable == SuiviProd.variable) &
                                    (subquery.c.poste == SuiviProd.poste) &
                                    (subquery.c.date == fn.DATE(SuiviProd.chrono))))
                .where((SuiviProd.chrono < end) &
                       (SuiviProd.variable << variables) &
                       (SuiviProd.poste << type_mag))
                .order_by(fn.DATE(SuiviProd.chrono).asc()))

    counter_values = []
    for counter in counters:

        num_week = counter.date.isocalendar().week
        year = counter.date.year

        counter_value = next(filter(lambda c: c['date']['num'] == num_week and c['date']['year'] == year,
                                    counter_values), None)

        if counter_value is None:
            counter_value = {'date': {'num': num_week, 'year': year},
                             'variables': []}
            counter_values.append(counter_value)

        counter_value['variables'].append({'variable': counter.variable,
                                           'value': counter.valeur_num,
                                           'date': counter.date})
    return counter_values


def createSheet(writer, headers, datas, val):
    format_court = writer.add_format({'num_format': 'dd/mm/yyyy'})

    t = list(filter(lambda a: a['val'] == val, headers))

    worksheet = writer.add_worksheet(t[0]['label'])
    col = 0
    for h in t[0]['headers']:
        worksheet.write(0, col, h)
        col = col + 1

    row = 1
    if len(datas) > 0:
        for coupe in datas:
            worksheet.write_datetime(row, 0, coupe.x_chrono, format_court)
            worksheet.write(row, 1, coupe.x_qte_mvt)
            row = row + 1


def get_start_from_period(period):
    now = datetime.datetime.utcnow()

    if period == Period.CURRENT_MONTH.value:
        start = datetime.datetime(now.year, now.month, 1)
    elif period == Period.CURRENT_YEAR.value:
        start = datetime.datetime(now.year, 1, 1)
    elif period == Period.PREV_YEAR.value:
        start = datetime.datetime(now.year - 1, 1, 1)
    else:
        start = now - datetime.timedelta(days=30)
    return start


def get_end_from_period(period):
    end = datetime.datetime.utcnow()
    if period == Period.PREV_YEAR.value:
        end = datetime.datetime(end.year, 1, 1)

    return end


def get_data_from_variable(variable, start=None, end=None, type_mag=None, counter_values=None):
    counters = counter_values if counter_values is not None \
               else get_counters(start=start, end=end, type_mags=[type_mag])

    val_counts = list(filter(lambda s: s > 0,
                             map(lambda d: d[variable], counters)))

    val_weeks = list(map(lambda c: c['date'], filter(lambda s: s[variable] > 0, counters)))

    return get_trends(val_counts=val_counts, val_weeks=val_weeks)


def get_counter_ratio(counter_values, variable1, variable2, variable_ratio, min_threshold=None,
                      max_threshold=None, display_data=True):
    trend = get_data_from_variable(counter_values=counter_values, variable=variable_ratio)
    mean = round(np.mean(np.array(trend['datas'])), 2) if len(trend['datas']) else 0

    sum_1 = sum(filter(lambda s: s > 0, map(lambda d: d[variable1], counter_values)))

    sum_2 = sum(filter(lambda s: s > 0, map(lambda d: d[variable2], counter_values)))

    ratio = round(sum_1 / sum_2, 2) if sum_2 > 0 else 0

    return {
        'trends': trend['trends'],
        'datas': trend['datas'] if display_data else None,
        'weeks': trend['weeks'] if display_data else None,
        'sum': ratio,
        'mean': mean,
        'threshold': {
            'min': min_threshold,
            'max': max_threshold
        }
    } if not math.isnan(mean) else None


def get_counter_mean(counter_values, variable, min_threshold=None, max_threshold=None):
    trend = get_data_from_variable(counter_values=counter_values, variable=variable)
    mean = round(np.mean(np.array(trend['datas'])), 2) if len(trend['datas']) else 0

    return {
        'trends': trend['trends'],
        'datas': trend['datas'],
        'weeks': trend['weeks'],
        'mean': mean,
        'threshold': {
            'min': min_threshold,
            'max': max_threshold
        }
    } if not math.isnan(mean) else None


def get_counter_sum(counter_values, variable, min_threshold=None, max_threshold=None):
    trend = get_data_from_variable(counter_values=counter_values, variable=variable)
    value = round(sum(trend['datas']), 2)
    np.array(trend['datas'])
    mean = round(np.mean(np.array(trend['datas'])), 2) if len(trend['datas']) else 0
    return {
        'trends': trend['trends'],
        'datas': trend['datas'],
        'weeks': trend['weeks'],
        'sum': value,
        'mean': mean,
        'threshold': {
            'min': min_threshold,
            'max': max_threshold
        }
    } if not math.isnan(value) and len(trend['datas']) > 0 else None


def get_equipment_case(equipment_mag=None):
    configs = (Config.select(Config.cle, Config.value)
               .where(Config.propriete == 'k_min_chargement')
               .order_by(Config.value))

    load_counters = Compteur.select().where(Compteur.cle.startswith("ID_CHARGEMENT_"))
    equipments = []
    old = None
    for counter in load_counters:
        equipment = counter.cle[14:]
        config = next(filter(lambda c: c.cle == equipment, configs), None)

        if config is not None:
            if old is not None:
                old['max'] = config.value

            old = {
                'equipment': equipment,
                'min': config.value,
                'max': None
            }
            equipments.append(old)

    if equipment_mag is not None:
        equipments = list(filter(lambda e: e['equipment'] == equipment_mag, equipments))

    cases = list(map(lambda m: ((ListeValide.id_chargement.cast('UNSIGNED') >= int(m['min'])) &
                                ((m['max'] is None) |
                                 ((ListeValide.id_chargement.cast('UNSIGNED') < (int(m['max'])
                                                                                 if m['max'] is not None else 0)))),
                                m['equipment']),
                     equipments))
    return peewee.Case(None, cases) if len(equipments) > 0 else None
