# -*- coding: utf8 -*-
from sqlalchemy import text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
)

from zope.sqlalchemy import ZopeTransactionExtension, mark_changed

from datetime import *
from dateutil.relativedelta import relativedelta
import transaction

from .default import (
    execute_query,
)

from ..models.default import (
    get_agences
)

def ctl_solde_resa(request, cd_cli, ref):
    # Controler le solde de l'élève
    query = "CALL spCTL_SOLDE_RESA(:cd_cli, :ref)"
    results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'ref': ref}).first()
    return results.resultat

def get_rdv_by_code(request, logged_in):
    query = "CALL spGet_ELEVES_RDV(:logged_in)"
    results = request.dbsession.execute(query, {'logged_in': logged_in}).fetchall()
    return results


def get_rdvB_by_no(request, no_ligne):
    query = """SELECT eleves_cpt.*, moniteurs.nom AS moniteur 
    FROM eleves_cpt LEFT JOIN moniteurs ON eleves_cpt.cd_mon = moniteurs.cd_mon
    WHERE no_ligne=:no_ligne"""
    results = request.dbsession.execute(query, {'no_ligne': no_ligne}).first()
    return results

def get_rdvA_by_no(request, no_ligne):
    query = """SELECT s.LIBELLE, l.* FROM pla_moto_lignes l
    INNER JOIN pla_moto s on s.type = l.type AND s.date = l.date AND s.groupe = l.groupe
    WHERE l.no_ligne=:no_ligne;"""
    results = request.dbsession.execute(query, {'no_ligne': no_ligne}).first()
    return results

def get_rdvA_by_id(request, type, date, groupe, cd_cli):
    query = "SELECT * FROM pla_moto_lignes WHERE type=:type AND date=:date AND groupe=:groupe AND cd_cli=:cd_cli;"
    results = request.dbsession.execute(query, {'type': type, 'date': date, 'groupe': groupe, 'cd_cli': cd_cli}).first()
    return results

def ctl_delai_annul(request, dateRDV, heureRDV, cree_le, statut):
    # controler que le delai de suppression est OK
    
    # lire le délai d'annulation des HCB
    # query = "SELECT delai_annul FROM p_agences WHERE code = 6;"
    # results = request.dbsession.execute(query, {}).first()
    # delai = results.delai_annul
    delai = 2  # jours
    
    # statut du rdv est moniteur absent ?
    if statut >= 10 and statut <= 14 :
        return True
    # la date de création du rdv est aujourd'hui ou hier ?
    nDays = (cree_le.date() - date.today()).days
    if nDays == 0 or nDays == -1 :
        return True
    
    # si Vendredi ou Samedi, ajouter un jour supplémentaire
    if date.today().weekday() == 4 or date.today().weekday() == 5 :
        delai = delai + 1

    # datetime du RDV
    dtRDV = datetime.strptime('%s %02d:00:00' % (dateRDV.strftime('%Y-%m-%d'), heureRDV), '%Y-%m-%d %H:%M:%S')
    # datetime aujourd'hui + délai
    dtDelai =  datetime.now() + timedelta(hours=delai * 24)
    if dtRDV > dtDelai:
        return True	
    else:
        return False

def ctl_delai_annul_A(request, dateRDV):
    # controler que le delai de suppression est OK
    
    # lire le délai d'annulation des HCB
    # query = "SELECT delai_annul FROM p_agences WHERE code = 6;"
    # results = request.dbsession.execute(query, {}).first()
    # delai = results.delai_annul
    delai = 2  # jours
    
    # si Vendredi ou Samedi, ajouter un jour supplémentaire
    if date.today().weekday() == 4 or date.today().weekday() == 5 :
        delai = delai + 1

    # date aujourd'hui + délai
    dtDelai =  datetime.today() + timedelta(days=delai)
    if dateRDV > dtDelai:
        return True	
    else:
        return False

def upd_eleves_rdv_annule(request, planning, no_ligne, logged_in):
    if planning == "B":
        # delettrer la ligne de compte
        query = "CALL spUPD_ELEVES_DELETTRER(:no_ligne)"
        execute_query(request, query, {'no_ligne': no_ligne})
        # annuler l'heure de l'élève
        query = """UPDATE eleves_cpt SET ref=NULL, debit=0, mtval=0, qte=0, fin_reservation=NULL,
        intitule=CONCAT('RDV de ', noplan,' h annulé (', statut, ')'), noplan=0, cd_uti = 'WEB' WHERE no_ligne = :no_ligne;"""
        execute_query(request, query, {'no_ligne': no_ligne})
        # revaloriser la ligne de compte
        query = "CALL spUpd_PLANNING_B_VALORISER(:logged_in)"
        execute_query(request, query, {'logged_in': logged_in})        
    else:
        # relire le RDV de type planning A
        query = "SELECT * FROM pla_moto_lignes WHERE no_ligne=:no_ligne;"
        rdvA = request.dbsession.execute(query, {'no_ligne': no_ligne}).first()
        # obtiens le no de la ligne cpt correspondant
        ligne_cpt = rdvA.ligne_cpt
        # exécute le traitement d'annulation
        query = """CALL spDel_PLA_MOTO_LIGNES(:no_ligne, :ligne_cpt, :logged_in, 'WEB');"""
        execute_query(request, query, {'logged_in': logged_in, 'no_ligne': no_ligne, 'ligne_cpt': ligne_cpt})
        
def upd_eleves_rdv_statut3(request, planning, no_ligne):
    
    # mettre le statut du RDV = 3 (décommandé avant 48h)
    if planning == "B":
        query = """UPDATE eleves_cpt SET statut=3, cd_uti = 'WEB', fin_reservation=NULL WHERE no_ligne = :no_ligne;"""
        execute_query(request, query, {'no_ligne': no_ligne})
    else:
        query = "UPDATE pla_moto_lignes SET statut=3, cd_uti = 'WEB' WHERE no_ligne=:no_ligne;"
        execute_query(request, query, {'no_ligne': no_ligne})

def get_rdva_dispos(request, type_lecon, cd_cli):
    # lire les horaires dispos du planning A
    
    # lire l'heure courante pour déterminer le groupe du jour à afficher
    heure = datetime.now().hour 
    if heure < 8 :
        gr_debut = 'A'
    elif heure < 12:
        gr_debut = 'B'
    elif heure < 15:
        gr_debut = 'c'
    elif heure < 18:
        gr_debut = 'D'
    else:
        gr_debut = 'E'
    # covertir heure en groupe TA
    TA_debut = chr(heure + 57)
    
    # Lire les dispos du jour à partir du groupe suivant l'heure actuelle
    # + les horaires dispos des jours suivants
    # + les résa de l'élève
    # + les résa en statut = 3, décommandé moins de 48h, et groupe dispo=0
    if type_lecon == 'PLATEAU':
        query = """SELECT type, date, groupe, 0 as cd_cli, 0 as statut FROM pla_moto 
        WHERE type='A' AND date = CURRENT_DATE AND groupe >= :gr_debut AND groupe <= 'D' AND libelle NOT LIKE '1er plat%' AND dispo > 0 AND valide ='N'
UNION
SELECT l.type, l.date, l.groupe, l.cd_cli, l.statut FROM pla_moto_lignes l JOIN pla_moto m ON l.type=m.type AND l.date=m.date AND l.groupe=m.groupe
WHERE l.type='A' AND l.date = CURRENT_DATE AND l.groupe >= :gr_debut AND l.groupe <= 'D' AND l.route = 0 AND l.statut = 3 AND l.valide ='N' AND m.dispo=0 AND m.libelle NOT LIKE '1er plat%'
UNION
SELECT type, date, groupe, cd_cli, statut FROM pla_moto_lignes WHERE (type='A' OR type='F') AND date >= CURRENT_DATE AND cd_cli = :cd_cli AND statut=0 AND valide='N'
UNION
SELECT type, date, groupe, 0 as cd_cli, 0 as statut FROM pla_moto 
        WHERE type='A' AND date > CURRENT_DATE AND groupe <= 'D' AND libelle NOT LIKE '1er plat%' AND dispo > 0 AND valide ='N'
UNION
SELECT l.type, l.date, l.groupe, l.cd_cli, l.statut FROM pla_moto_lignes l JOIN pla_moto m ON l.type=m.type AND l.date=m.date AND l.groupe=m.groupe
WHERE l.type='A' AND l.date > CURRENT_DATE AND l.groupe <= 'D' AND l.route = 0 AND l.statut = 3 AND l.valide ='N' AND m.dispo=0 AND libelle NOT LIKE '1er plat%';"""
        results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'gr_debut': gr_debut, 'TA_debut': TA_debut}).fetchall()
    # lecon = ROUTE    
    elif type_lecon == 'ROUTE':
        query = """SELECT type, date, groupe, 0 as cd_cli, 0 as statut FROM pla_moto 
        WHERE type='A' AND date = CURRENT_DATE AND groupe >= :gr_debut AND groupe <= 'D' AND route_dispo > 0 AND valide ='N'
UNION
SELECT l.type, l.date, l.groupe, l.cd_cli, l.statut FROM pla_moto_lignes l JOIN pla_moto m ON l.type=m.type AND l.date=m.date AND l.groupe=m.groupe
WHERE l.type='A' AND l.date = CURRENT_DATE AND l.groupe >= :gr_debut AND l.groupe <= 'D' AND l.route <> 0 AND l.statut = 3 AND l.valide ='N' AND m.dispo=0
UNION
SELECT type, date, groupe, cd_cli, statut FROM pla_moto_lignes WHERE (type='A' OR type='F') AND date >= CURRENT_DATE AND cd_cli = :cd_cli AND statut=0 AND valide ='N'
UNION
SELECT type, date, groupe, 0 as cd_cli, 0 as statut FROM pla_moto 
        WHERE type='A' AND date > CURRENT_DATE AND groupe <= 'D' AND route_dispo > 0 AND valide ='N'
UNION
SELECT l.type, l.date, l.groupe, l.cd_cli, l.statut FROM pla_moto_lignes l JOIN pla_moto m ON l.type=m.type AND l.date=m.date AND l.groupe=m.groupe
WHERE l.type='A' AND l.date > CURRENT_DATE AND l.groupe <= 'D' AND l.route <> 0 AND l.statut = 3 AND l.valide ='N' AND m.dispo=0;"""
        results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'gr_debut': gr_debut, 'TA_debut': TA_debut}).fetchall()
    # lecon =examen blanc
    else:
        query = """SELECT type, date, groupe, 0 as cd_cli, 0 as statut FROM pla_moto 
        WHERE type='F' AND date = CURRENT_DATE AND groupe > :TA_debut AND dispo > 0 AND valide ='N'
UNION
SELECT l.type, l.date, l.groupe, l.cd_cli, l.statut FROM pla_moto_lignes l JOIN pla_moto m ON l.type=m.type AND l.date=m.date AND l.groupe=m.groupe
WHERE l.type='F' AND l.date = CURRENT_DATE AND l.groupe > :TA_debut AND l.statut = 3 AND l.valide ='N' AND m.dispo=0
UNION
SELECT type, date, groupe, cd_cli, statut FROM pla_moto_lignes WHERE (type='A' OR type='F') AND date >= CURRENT_DATE AND cd_cli = :cd_cli AND valide ='N'
UNION
SELECT type, date, groupe, 0 as cd_cli, 0 as statut FROM pla_moto 
        WHERE type='F' AND date > CURRENT_DATE AND dispo > 0 AND valide ='N'
UNION
SELECT l.type, l.date, l.groupe, l.cd_cli, l.statut FROM pla_moto_lignes l JOIN pla_moto m ON l.type=m.type AND l.date=m.date AND l.groupe=m.groupe
WHERE l.type='F' AND l.date > CURRENT_DATE AND l.groupe <= 'D' AND l.statut = 3 AND l.valide ='N' AND m.dispo=0;"""
    results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'gr_debut': gr_debut, 'TA_debut': TA_debut}).fetchall()
    return results
        
def get_rdvb_dispos(request, cd_cli):

    # lire les horaires dispos du planning B
    query = "CALL spGet_PLANNINGB_DISPO(:cd_cli);"
    results = request.dbsession.execute(query, {'cd_cli': cd_cli}).fetchall()
    return results


def update_rdva(request, rdv_type, rdv_date, rdv_groupe, old_cli, newcli, qte, ref, route, nDelai):
    # lire lieu en fonction du groupe
    query = "SELECT lieu from P_LIEUX WHERE type = :type;"
    results = request.dbsession.execute(query, {'type': rdv_type + rdv_groupe}).first()
    if results:
        lieu = results.lieu
    else:
        lieu = '???'
    
    query = "CALL spUpd_PLA_MOTO_LIG(:rdv_type,:rdv_date,:rdv_groupe,:old_cli,:newcli,0,:lieu,'',:qte,:ref,:route,6,:nDelai,0,'WEB');"
    execute_query(request, query, {'rdv_type':rdv_type, 'rdv_date':rdv_date, 'rdv_groupe':rdv_groupe, 'old_cli':old_cli, 'newcli':newcli,
                                   'lieu':lieu, 'qte':qte, 'ref':ref, 'route':route, 'nDelai':nDelai, })
    
    query = "CALL spUpd_PLANNING_A_VALORISER(:cd_cli, :ref);"
    execute_query(request, query, {'cd_cli':newcli, 'ref':ref})

def update_rdvb(request, no_ligne, old_cd_cli, cd_cli, date, heure, qte, ref, cd_mon, agence, comment, statut, typeHeure, nDelai, cd_cli_old, cd_uti):
    query = "CALL spUpd_PLANNING_B(:no_ligne,:old_cd_cli,:cd_cli,:date,:heure,:qte,:ref,:cd_mon,:agence,0,:comment,:statut,:typeHeure,:nDelai,:cd_cli_old,:cd_uti);"
    execute_query(request, query, {'no_ligne':no_ligne, 'old_cd_cli':old_cd_cli, 'cd_cli':cd_cli, 'date':date, 'heure':heure, 'qte':qte, 'ref':ref,
                                   'cd_mon':cd_mon, 'agence':agence, 'comment':comment, 'statut':statut, 'typeHeure':typeHeure,
                                   'nDelai':nDelai, 'cd_cli_old':cd_cli_old, 'cd_uti':cd_uti})
    
    query = "CALL spUpd_PLANNING_B_VALORISER(:cd_cli);"
    execute_query(request, query, {'cd_cli':cd_cli})
    
def get_eleve_cpt_extrait(request, logged_in):
    query = "CALL spGet_ELEVES_CPT_byEXTRAIT(:logged_in)"
    results = request.dbsession.execute(query, {'logged_in': logged_in}).fetchall()
    return results

def ctl_heures_resaB(request, user, cd_cli, cd_mon, date, heure, qte):
    # controler heures en double
    query = "CALL spCTL_HEURES_EN_DOUBLE(:user, :cd_cli, :cd_mon, :date, :heure, :qte)"
    results = request.dbsession.execute(query, {'user': user,'cd_cli': cd_cli,'cd_mon': cd_mon,'date': date,'heure': heure,'qte': qte}).fetchall()
    if results:
        return 1
    
    # max 2h cumulées par jour 
    query = "SELECT COALESCE(sum(qte),0) AS total FROM eleves_cpt WHERE cd_cli=:cd_cli AND date=:date AND NoPlan > 0;"
    results = request.dbsession.execute(query, {'user': user,'cd_cli': cd_cli,'date': date}).first()
    if results.total + qte > 2:
        return 2

    # max 4h cumulées par semaine 
    lundi = date - timedelta(days=date.weekday())
    samedi = lundi + timedelta(days=5)
    query = "SELECT COALESCE(sum(qte),0) AS total FROM eleves_cpt WHERE cd_cli=:cd_cli AND date>=:lundi AND date<=:samedi AND NoPlan > 0;"
    results = request.dbsession.execute(query, {'user': user,'cd_cli': cd_cli,'lundi': lundi,'samedi': samedi}).first()
    if results.total + qte > 4:
        return 3
    
    return 0

def ins_t_log_nuit(request, proc, message):
    
    # insert une ligne dans t_log_nuit
    query = "INSERT t_log_nuit (proc, msg) values (:proc, :message);"
    execute_query(request, query, {'proc': proc, 'message': message})

def ctl_heures_resaA(request, type_lecon, cd_cli, rdv_date, rdv_groupe):
    # lire l'agence 6 pour obtenir les Max des lecons A possibles
    agence6 = get_agences(request, 6)

    # Controler que l'élève n'est pas déjà inscrit dans ce créneau
    query = "SELECT type, date, groupe, cd_cli, statut FROM pla_moto_lignes WHERE date = :date AND groupe = :groupe AND cd_cli = :cd_cli;"
    results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'date': rdv_date, 'groupe': rdv_groupe}).fetchall()
    if results:
        return 'Vous avez déjà un rendez-vous dans ce créneau.'
    
    madate = datetime.strptime(rdv_date, '%Y-%m-%d')
    
    # cas des lecons ROUTE
    if type_lecon == 'R':
        # controler 2 leçons de plateau sont prises ou réservée avant la date de la route
        query = "SELECT count(*) AS total FROM pla_moto_lignes WHERE type = 'A' AND date < :rdv_date AND cd_cli = :cd_cli AND route = 0;"
        results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'rdv_date': rdv_date}).first()
        if results.total < 2:
            return 'Vous devez avoir 2 leçons PLATEAU avant de pouvoir prendre une ROUTE.'
    
    # rdv d'aujourd'hui ou dans période de dérogation ?
    derogation = date.today() + timedelta(days = agence6.MaxJoursDerogation)
    if madate.date() <= derogation:
        # controler 3 leçons max par journée
        query = "SELECT type, date, groupe, cd_cli, statut FROM pla_moto_lignes WHERE date = :date AND cd_cli = :cd_cli;"
        results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'date': rdv_date}).fetchall()
        if len(results) > 3:
            return 'Vous ne pouvez pas prendre plus de 3 rendez-vous par jour.'
    else:       
        # controler une seule leçon par journée
        query = "SELECT type, date, groupe, cd_cli, statut FROM pla_moto_lignes WHERE date = :date AND cd_cli = :cd_cli;"
        results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'date': rdv_date}).fetchall()
        if results:
            return 'Vous avez déjà un rendez-vous dans cette journée.'
        
        # max leçons par semaine 
        lundi = madate - timedelta(days=madate.weekday())
        samedi = lundi + timedelta(days=5)
        query = "SELECT count(*) AS total FROM pla_moto_lignes WHERE cd_cli = :cd_cli AND date>=:lundi AND date<=:samedi;"
        results = request.dbsession.execute(query, {'cd_cli': cd_cli,'lundi': lundi,'samedi': samedi}).first()
        if results.total >= agence6.MaxLeconASemaine:
            return 'Vous ne pouvez pas prendre plus de %s rendez-vous par semaine.' % str(agence6.MaxLeconASemaine)
    
        # max leçons par mois 
        premier = date.today()
        dernier = premier + relativedelta(days=28)
        query = "SELECT count(*) AS total FROM pla_moto_lignes WHERE cd_cli = :cd_cli AND date>=:premier AND date<=:dernier;"
        results = request.dbsession.execute(query, {'cd_cli': cd_cli,'premier': premier,'dernier': dernier}).first()
        if results.total >= agence6.MaxLeconAMois:
            return 'Vous ne pouvez pas prendre plus de %s rendez-vous par 28 jours.' % str(agence6.MaxLeconAMois)
    
        # une seule lecon 18-21h par semaine 
        query = "SELECT count(*) AS total FROM pla_moto_lignes WHERE type = 'A' AND groupe = 'D' AND cd_cli=:cd_cli AND date>=:lundi AND date<=:samedi;"
        results = request.dbsession.execute(query, {'cd_cli': cd_cli,'lundi': lundi,'samedi': samedi}).first()
        if results.total > 0:
            return "Vous ne pouvez prendre qu'un seul créneau 18-21h par semaine."
                
    return ''

def ctl_heures_TA(request, cd_cli):
    # compter le nb examen blanc A
    query = "SELECT type, date, groupe, cd_cli, statut FROM pla_moto_lignes WHERE type = 'F' AND cd_cli = :cd_cli AND statut=0"
    results = request.dbsession.execute(query, {'cd_cli': cd_cli}).fetchall()
    return results

def ctl_heures_plateau(request, cd_cli):
    # compter le nombre heures plateau
    query = "SELECT count(*) as total FROM eleves_cpt WHERE cd_cli = :cd_cli AND ref = 'HCA3' AND route=0;"
    results = request.dbsession.execute(query, {'cd_cli': cd_cli}).first()
    return results.total

def ctl_pratique_ok(request, login):
    # lire l'eleve connecte
    query = """SELECT tr_p_ok FROM eleves WHERE cd_cli=:login ;"""
    results = request.dbsession.execute(query, {'login': login}).first()
    if results.tr_p_ok:
        return True
    else:
        return False

def get_inscriptions(request):
    # lire les inscriptions WEB dans planning A et B
    query = """
    SELECT ref, date, fin_reservation, cd_cli, no_ligne, noplan, circuit, debit, cree_le, statut, cd_cli_old
        FROM eleves_cpt WHERE NOT isnull(fin_reservation) AND cd_uti = 'WEB' ORDER BY date;"""
    results = request.dbsession.execute(query, {}).fetchall()
    return results

def get_solde_eleve(request, cd_cli, ref):
    # Controler le solde de l'élève
    query = "CALL spCTL_SOLDE_RESA(:cd_cli, :ref)"
    results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'ref': ref}).first()
    return results

def update_reservation_confirm(request, action, no_ligne, ref, statut, cd_cli_old):
    # confirmer les résa WEB        
    query = "CALL spUPD_RESERVATION_CONFIRM(:action, :no_ligne, :ref, :statut, :cd_cli_old);"
    execute_query(request, query, {'action': action, 'no_ligne': no_ligne, 'ref': ref, 'statut': statut, 'cd_cli_old': cd_cli_old})

def insert_email_resa(request, ref, cd_cli, type, date, heure, statut):
    query = """INSERT INTO email_resa (ref, cd_cli, resa_date, resa_type, resa_statut) 
    VALUES (:ref, :cd_cli, DATE_ADD(:date, INTERVAL :heure HOUR), :type, :statut);"""
    execute_query(request, query, {'ref': ref, 'cd_cli': cd_cli, 'type': type, 'date': date, 'heure': heure, 'statut': statut})

def get_email_resa(request, type):
    # lire les rappels non envoyés
    query = "CALL spGet_EMAIL_RESA(:type);"
    results = request.dbsession.execute(query, {'type': type}).fetchall()
    return results

def insert_log(request, proc, msg):
    query = "INSERT t_log_nuit (proc,msg) VALUES (:proc,:msg);"
    execute_query(request, query, {'proc': proc, 'msg' :msg})

def update_email_resa(request, no_id):
    query = "UPDATE email_resa SET envoye_le = NOW() WHERE no_id = :no_id;"
    execute_query(request, query, {'no_id': no_id})