import json
import logging
from datetime import datetime, timedelta

from common.status import HTTP_200_OK, HTTP_204_NO_CONTENT, HTTP_500_INTERNAL_SERVER_ERROR, \
    HTTP_404_NOT_FOUND, HTTP_400_BAD_REQUEST
from flask import Blueprint, request
from flask_jwt_extended import jwt_required, get_jwt_identity
from median.constant import EcoType
from median.database import mysql_db
from median.models import Magasin, Adresse, Emplacements, Compteur, Stock, Product
from median.views import RawConfig
from peewee import DoesNotExist, fn, SQL, JOIN

from ressources.blueprint.stores.store_services import get_equipment_where_request

stores_blueprint = Blueprint('magasins', __name__)

logger = logging.getLogger('median')


@stores_blueprint.route('<string:type_mag>', methods=['GET'])
@jwt_required()
def get(type_mag):
    try:
        mags = Magasin \
            .select(Magasin) \
            .where(Magasin.type_mag == type_mag) \
            .order_by(Magasin.type_mag)

        logger.info('Lines : %s.' % len(mags))

        if len(mags) == 0:
            logger.warning(f"No stores found for type: {type_mag}")
            return {"message": f"No stores found for type: {type_mag}"}, HTTP_404_NOT_FOUND

    except Exception as error:
        logger.error('erreur recuperation magasin')
        return {'message': error.args}, HTTP_400_BAD_REQUEST

    return ([{
        'pk': m.pk,
        'mag': m.mag,
        'type_mag': m.type_mag,
        'last_reap': m.last_reap.isoformat() if m.last_reap is not None else None,
        'eco_type': m.eco_type,
        'libelle': m.libelle,
        'type_machine': m.type_machine,
        'nb_col': m.dim_3,
        'nb_line': m.dim_2,
    } for m in mags])


@stores_blueprint.route('', methods=['GET'])
@jwt_required()
def get_all():
    try:
        user_id = get_jwt_identity()

        d = datetime.now()

        nb_peremption = RawConfig().read('k_eco_nb_péremption_astus')
        nb_peremption = nb_peremption.value if nb_peremption is not None else 10
        date_max = (datetime.now() + timedelta(days=nb_peremption)).strftime('%Y-%m-%d')
        date_max_30 = (datetime.now() + timedelta(days=nb_peremption - 30)).strftime('%Y-%m-%d')
        #
        # outstocks = (Product
        #              .select(fn.COUNT(Product.pk.distinct()))
        #              .join(Adresse, on=((Adresse.emplacement == Product.reference) & (Adresse.bloque_reappro == 0)))
        #              .join(Stock, JOIN.LEFT_OUTER,
        #                    on=((Stock.reference == Product.reference) & (Stock.date_peremption > d) &
        #                        (Stock.adresse.startswith(Magasin.mag))))
        #              .where((Stock.quantite.is_null()) &
        #                     (Adresse.adresse.startswith(Magasin.mag)) &
        #                     (Magasin.eco_type == EcoType.Astus.value)))

        mags = Magasin \
            .select(Magasin,
                    (Stock.select(fn.SUM(Stock.quantite))
                     .where((Stock.magasin == Magasin.mag) &
                            (Stock.date_peremption < date_max))).alias('expiration_qty'),
                    (Stock.select(fn.SUM(Stock.quantite))
                     .where((Stock.magasin == Magasin.mag) &
                            (Stock.date_peremption < date_max_30) &
                            (Stock.date_peremption >= date_max))).alias('expiration_30_qty'),
                    (Adresse
                     .select(fn.Count(Adresse.pk))
                     .where((Adresse.magasin == Magasin.mag) & (Adresse.bloque > 0))).alias('bloque'),
                    (Adresse
                     .select(fn.Count(Adresse.pk))
                     .where((Adresse.magasin == Magasin.mag)
                            & (Adresse.bloque != 1))).alias('adress_total'),
                    (Adresse
                     .select(fn.Count(Adresse.pk))
                     .where((Adresse.magasin == Magasin.mag)
                            & (Adresse.emplacement != "")
                            & (Adresse.bloque != 1))).alias('fill_total'),
                    (Product
                     .select(fn.COUNT(Product.pk.distinct()))
                     .join(Adresse, on=((Adresse.emplacement == Product.reference) & (Adresse.bloque_reappro == 0)))
                     .join(Stock, JOIN.LEFT_OUTER,
                           on=((Stock.reference == Product.reference) & (Stock.date_peremption > d) &
                               (Stock.adresse == Adresse.adresse)))
                     .where((Stock.quantite.is_null()) &
                            (Adresse.adresse.startswith(Magasin.mag)) &
                            (Magasin.eco_type == EcoType.Astus.value))).alias('out_stock_ref')
                    ) \
            .where(get_equipment_where_request(user_id)) \
            .order_by(Magasin.type_mag)

        logger.info('Lines : %s.' % len(mags))
    except Exception as error:
        logger.error(f'erreur recuperation magasin : {error.args}')
        return {'message': error.args}, HTTP_400_BAD_REQUEST

    res = []

    for m in mags:
        obj = {
            'pk': m.pk,
            'mag': m.mag,
            'avatar': m.avatar,
            'type_mag': m.type_mag,
            'last_reap': m.last_reap.isoformat() if m.last_reap is not None else None,
            'eco_type': m.eco_type,
            'libelle': m.libelle,
            'type_machine': m.type_machine,
            'nb_bloque': m.bloque,
            'nb_col': m.dim_3,
            'nb_line': m.dim_2,
            'params': None
        }

        if m.eco_type in [EcoType.Cueillette.value, EcoType.Coupe.value]:
            obj['params'] = {}
            acced = obj['params']
            adr_taquin = Adresse.select(fn.substr(Adresse.adresse, 1, 8)).where(
                (Adresse.magasin == m.mag) & (Adresse.format == 'BOITE PASS') &
                (Adresse.etat == 'L') & (Adresse.contenant != '') & (Adresse.bloque == 0)
            ).group_by(fn.substr(Adresse.adresse, 1, 8)).having(fn.COUNT(SQL('*')) == 2)
            acced['nb_taquin'] = len(adr_taquin)

            AdresseFront = Adresse.alias()
            AdresseBack = Adresse.alias()

            adr_demi_taquin = AdresseFront.select(AdresseFront.adresse).join(
                AdresseBack, on=(
                    (AdresseFront.magasin == AdresseBack.magasin) &
                    (fn.substr(AdresseFront.adresse, 1, 8) == fn.substr(AdresseBack.adresse, 1, 8))
                )
            ).where(
                (AdresseFront.adresse.endswith('1')) &
                (AdresseBack.adresse.endswith('3')) &
                (AdresseFront.contenant != '') &
                (AdresseBack.contenant == '')
            )
            logger.info(adr_demi_taquin)
            acced['nb_demi_taquin'] = len(adr_demi_taquin)
            obj['expiration_qty'] = m.expiration_qty

        elif m.eco_type in [EcoType.Astus.value]:
            obj['expiration_30_qty'] = m.expiration_30_qty
            obj['expiration_qty'] = m.expiration_qty
            obj['occupancyRate'] = round((float(m.fill_total) / float(m.adress_total)) * float(100.00), 2)
            obj['out_stock_ref'] = m.out_stock_ref

        res.append(obj)

    return res, HTTP_200_OK


@stores_blueprint.route('', methods=['PUT'])
@jwt_required()
def update():
    args = json.loads(request.data)
    adr = args['adr']
    type = args['type']

    logger.info('Création/Suppression contenant: "%s"' % (adr))

    if not adr:
        logger.warning('Adresse manquante!')
        return {'message': 'Adresse manquante!'}, HTTP_500_INTERNAL_SERVER_ERROR

    try:

        if type == 'suppression':
            n = (Adresse
                 .update({Adresse.contenant: ''})
                 .where(Adresse.adresse == adr))
            n.execute()
        else:

            with mysql_db.atomic():
                cpt = (
                    Compteur.select()
                    .where(Compteur.cle == 'CONTENANT_PASS')
                    .for_update()
                    .get()
                )
                result = cpt.val
                cpt.val = cpt.val + 1
                cpt.save()

            logger.info('Création/Suppression contenant: "%s"' % (adr))

            m = (Adresse
                 .update({Adresse.contenant: str(result).zfill(9)})
                 .where(Adresse.adresse == adr))

            m.execute()

        res_contenant = Emplacements.select(Emplacements.pk, Emplacements.adr, Emplacements.etat,
                                            Emplacements.libelle,
                                            Emplacements.x_format,
                                            Emplacements.bloque, Emplacements.ref, Emplacements.qte,
                                            Emplacements.contenant,
                                            Emplacements.fraction, Emplacements.designation, Emplacements.ucd,
                                            Emplacements.sortie, Emplacements.magasin,
                                            Emplacements.entree, Emplacements.lot, Emplacements.peremp) \
            .where(Emplacements.adr == adr).get()
    except DoesNotExist:
        logger.error('Ce produit n\'existe pas! Réf: "%s"' % (adr))
        return {'message': 'Product does not exist'}, 404
    except Exception as error:
        logger.error(error.args)
        return {'message': error.args}, 503

    logger.info("""L'adresse a été modifié sans problème. Réf: "%s" """ % (adr))
    if res_contenant is not None:
        return {
            'magasin': res_contenant.magasin,
            'pk': res_contenant.pk,
            'adr': res_contenant.adr,
            'format': res_contenant.x_format,
            'bloque': res_contenant.bloque,
            'libelle': res_contenant.libelle,
            'etat': res_contenant.etat,
            'contenant': res_contenant.contenant,
            'reference': res_contenant.ref,
            'quantite': res_contenant.qte,
            'fraction': res_contenant.fraction,
            'designation': res_contenant.designation,
            'ucd': res_contenant.ucd,
            'sortie': str(res_contenant.sortie or ''),
            'entree': str(res_contenant.entree or ''),
            'lot': res_contenant.lot,
            'peremp': str(res_contenant.peremp or '')
        }, HTTP_200_OK
    else:
        return {}, HTTP_204_NO_CONTENT


@stores_blueprint.route("/selectbox", methods=["GET", "POST"])
@jwt_required()
def get_all_for_ref_field_selectbox():
    # This returns an array of options for a reference field (see freefields in config_blueprint)
    # Due to its dynamic nature, it HAS to return an array, named "data"
    # The params HAVE to be the name of an attribute of the peewee Magasin model (ex: eco_type, not x_eco_type)

    params = None
    if request.method == "POST":
        if request.data:
            params = json.loads(request.data)

    query = Magasin.select(Magasin.mag).distinct()

    # Add where conditions based on params
    if params:
        for key, value in params.items():
            if hasattr(Magasin, key):
                query = query.where(getattr(Magasin, key) == value)
            else:
                logger.error(f'Magasin selectbox api : unknown attribute {key}')

    stores = query.order_by(+Magasin.mag)
    return {"data": [store.mag for store in stores]}, HTTP_200_OK
