import json
import os
from datetime import datetime

from dateutil.relativedelta import relativedelta
from flask import Blueprint, jsonify, request, send_file
from flask_jwt_extended import jwt_required
from common.status import HTTP_200_OK
from median.models import Adresse, Magasin, Historique, Product
from median.constant import EtatAdresse, EcoType, HistoryType
from peewee import fn

from ressources.astus.utils import generate_memory_excel_file

astus_stats_blueprint = Blueprint('astus_stats', __name__)

dotation_hour = os.environ.get('DOTATION_HOUR', 16)


@astus_stats_blueprint.route('/filling', methods=['POST'])
@jwt_required()
def get_filling_percentage():
    data = json.loads(request.data)

    equipments = data.get('equipments', [])
    expr = (Magasin.eco_type == EcoType.Astus.value)
    if len(equipments) > 0:
        expr = expr & (Magasin.pk << equipments)

    total = (Adresse.select(Adresse.pk)
             .join(Magasin, on=Adresse.magasin == Magasin.mag)
             .where(expr &
                    Adresse.etat.not_in([EtatAdresse.Bloque.value])).count())

    total_free = (Adresse.select(Adresse.pk)
                  .join(Magasin, on=Adresse.magasin == Magasin.mag)
                  .where(expr &
                         Adresse.etat << [EtatAdresse.Libre.value])
                  .count())

    res = {
        "data": {
            'total': total,
            'total_free': total_free
        }
    }

    return jsonify(res), HTTP_200_OK


@astus_stats_blueprint.route('/mostused', methods=['POST'])
@jwt_required()
def get_most_used_stat():
    data = json.loads(request.data)
    equipments = data.get('equipments', [])

    if len(equipments) > 0:
        equipment_expr = (Magasin.pk << equipments)
    else:
        equipment_expr = (Magasin.eco_type == EcoType.Astus.value)

    equipments = Magasin.select(Magasin.mag).where(equipment_expr)

    expr_like = None
    for equipment in equipments:
        if expr_like is None:
            expr_like = (Historique.adresse.startswith(equipment.mag))
        else:
            expr_like = expr_like | (Historique.adresse.startswith(equipment.mag))

    if expr_like is None:
        expr = True
    else:
        expr = expr_like

    now = datetime.now()
    start = f'{str(now.year)}-{str(now.month)}-01'
    end = f'{str(now.year)}-{str(now.month + 1)}-01'

    used = (Historique
            .select(Historique.reference.alias('reference'),
                    Historique.fraction.alias('fraction'),
                    Product.designation.alias('designation'),
                    fn.SUM(Historique.quantite_mouvement).alias('qty'))
            .join(Product, on=(Product.reference == Historique.reference))
            .where(expr &
                   (Historique.type_mouvement == HistoryType.Sortie.value) &
                   (Historique.quantite_mouvement > 0) &
                   (Historique.chrono.between(start, end)))
            .group_by(Historique.reference, Historique.fraction)
            .order_by(fn.SUM(Historique.quantite_mouvement).desc())
            .limit(10))

    return jsonify(
        {
            "list": [
                {
                    'reference': item.reference,
                    'fraction': item.fraction,
                    'designation': item.designation,
                    'qty': item.qty
                } for item in used.objects()
            ]
        }
    ), HTTP_200_OK


def replenish_stat_request():
    data = json.loads(request.data)
    year = data['year']
    equipments = data.get('equipments', [])

    if len(equipments) > 0:
        expr = (Magasin.pk << equipments)
    else:
        expr = (Magasin.eco_type == EcoType.Astus.value)

    start = f'{str(year)}-01-01'
    end = f'{str(year + 1)}-01-01'

    return (Historique
            .select(fn.YEAR(Historique.chrono).alias('year'),
                    fn.MONTH(Historique.chrono).alias('month'),
                    fn.COUNT(Historique.chrono).alias('nb_replenish'),
                    fn.SUM(fn.IF(fn.HOUR(Historique.chrono) >= dotation_hour, 1, 0)).alias('nb_replenish_outside')
                    )
            .join(Magasin, on=Magasin.mag == fn.SUBSTRING(Historique.adresse, 1, 3))
            .where(expr &
                   (Historique.type_mouvement == HistoryType.Entree.value) &
                   (Historique.chrono.between(start, end)))
            .group_by(fn.YEAR(Historique.chrono), fn.MONTH(Historique.chrono)))


def get_obj_replenish(item):
    return {
        'date': f'{item.year}-{str(item.month).rjust(2, "0")}-01',
        'count': item.nb_replenish,
        'outsideCount': item.nb_replenish_outside
    }


@astus_stats_blueprint.route('/replenish', methods=['POST'])
@jwt_required()
def get_replenish_stat():
    replenish = replenish_stat_request()

    return jsonify(
        {
            "list": [
                get_obj_replenish(item) for item in replenish
            ]
        }
    ), HTTP_200_OK


@astus_stats_blueprint.route('/replenish/export', methods=['POST'])
@jwt_required()
def get_replenish_stat_export():
    data = json.loads(request.data)
    headers = data['translations']
    replenish = replenish_stat_request()

    memory_file = generate_memory_excel_file(headers=headers, items=replenish,
                                             transform_function=get_obj_replenish)

    return send_file(memory_file, as_attachment=True, download_name="seuil.xlsx")


@astus_stats_blueprint.route('/replenish/detail', methods=['POST'])
@jwt_required()
def get_replenish_stat_detail():
    data = json.loads(request.data)
    date = data['date']
    equipments = data.get('equipments', [])

    start = datetime.strptime(date, '%Y-%m-%d')
    end = start + relativedelta(months=1)

    if len(equipments) > 0:
        expr = (Magasin.pk << equipments)
    else:
        expr = (Magasin.eco_type == EcoType.Astus.value)

    replenish = (Historique
                 .select(Magasin.pk.alias('pk'),
                         Magasin.libelle.alias('label'),
                         Magasin.avatar.alias('avatar'),
                         Magasin.eco_type.alias('eco_type'),
                         fn.COUNT(Historique.chrono).alias('nb_replenish'),
                         fn.SUM(fn.IF(fn.HOUR(Historique.chrono) >= 16, 1, 0)).alias('nb_replenish_outside')
                         )
                 .join(Magasin, on=Magasin.mag == fn.SUBSTRING(Historique.adresse, 1, 3))
                 .where(expr &
                        (Historique.type_mouvement == HistoryType.Entree.value) &
                        (Historique.chrono.between(start, end)))
                 .group_by(Magasin.pk))

    return jsonify(
        {
            "list": [
                {
                    "equipment": {
                        'pk': item.pk,
                        'label': item.label,
                        'avatar': item.avatar,
                        'eco_type': item.eco_type
                    },
                    'count': item.nb_replenish,
                    'outsideCount': item.nb_replenish_outside
                } for item in replenish.objects()
            ]
        }
    ), HTTP_200_OK
