# -*- coding: utf8 -*-
import json
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
import transaction
from datetime import *


def execute_query(request, query, params):
    """Execute query and mark session as changed"""
    request.dbsession.execute(query, params)
    mark_changed(request.dbsession)
    transaction.commit()

def get_agences(request, code):
    if code == 0:
        query = "SELECT * FROM p_agences ORDER BY CODE;"
        results = request.dbsession.execute(query)
        return results.fetchall()
    else:
        query = "SELECT * FROM p_agences WHERE code = :code;"
        results = request.dbsession.execute(query, {'code': code})
        return results.first()

def get_date_examen(request, logged_in):
    # lire la date examen de l'eleve
    query = "call spGet_EXAMENS_AFF_PREVU(:logged_in)"
    results = request.dbsession.execute(query, {'logged_in': logged_in}).first()
    return results


def get_dates_fin_validite(request, logged_in):
    query = """SELECT 
               DATE_FORMAT(fin_forfait_code_le,'%d-%m-%Y') AS date_fin_code,
               DATE_FORMAT(visite_med_fin_le,'%d-%m-%Y') AS date_fin_vm
             FROM eleves WHERE cd_cli=:logged_in;"""
    results = request.dbsession.execute(query, {'logged_in': logged_in}).fetchall()
    return results


def get_eleve_agence(request, logged_in):
    # lire l'adresse email de l'agence de l'eleve
    query = "SELECT a.* FROM p_agences a INNER JOIN eleves e ON e.agence = a.code WHERE e.cd_cli=:logged_in"
    results = request.dbsession.execute(query, {'logged_in': logged_in}).first()
    return results


def get_eleve_stage(request, logged_in):
    # lire les donnees de l'eleve
    query = "call spGet_ELEVES_INFOS(:logged_in)"
    results = request.dbsession.execute(query, {'logged_in': logged_in}).fetchall()
    return results


def get_eleve_info(request, userid):
    query = "select nompren, email, permis_demande, formule from eleves where cd_cli=:userid"
    result = request.dbsession.execute(query, {'userid': userid}).first()
    return {'fullname': result[0],
            'email': result[1],
            'permis': result[2],
            'formule': result[3]
            }


def get_eleves_by_code(request, login):
    # lire l'eleve connecte
    query = """SELECT e.* FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
       WHERE cd_cli=:login """
    results = request.dbsession.execute(query, {'login': login}).first()
    return results


def get_eleve_justifs(request, cd_cli):
    # lire tous les justifs téléchargés ou non
    query = """SELECT * FROM eleves_upload
    WHERE cd_cli = :cd_cli and type='JUST' ORDER BY no_tri;"""
    results = request.dbsession.execute(query, {'cd_cli': cd_cli}).fetchall()
    return results


def get_eleves_by_date_nais(request, login, date_nais):
    # lire l'eleve connecte
    query = """SELECT e.* FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
       WHERE cd_cli=:login  AND DATE_NAIS = STR_TO_DATE(:date_nais, '%d/%m/%Y');"""
    results = request.dbsession.execute(query, {'login': login, 'date_nais': date_nais}).first()
    return results


def get_eleves_by_name_date_nais(request, name, forname, date_nais):
    # lire l'eleve connecte
    nomprenom = "%s %s" % (name, forname)
    query = """SELECT e.* FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
       WHERE NOMPREN=:nomprenom  AND DATE_NAIS = STR_TO_DATE(:date_nais, '%d/%m/%Y');"""
    results = request.dbsession.execute(query, {'nomprenom': nomprenom, 'date_nais': date_nais}).first()
    return results


def get_horaires_dispo(request, agence, date_debut):
    query = """
      SELECT c.date, c.noplan, c.qte, m.nom, c.intitule, to_SEMAINE(DATE_FORMAT(c.date, '%a %d %b')) AS DATE_CLAIR, TIME_FORMAT(SEC_TO_TIME(c.noplan * 3600), '%Hh%i') AS HEURE_CLAIR
      FROM eleves_cpt c
      LEFT JOIN moniteurs m ON c.cd_mon = m.cd_mon
      WHERE c.agence = :agence AND c.date = :date_debut AND cd_cli > 1 AND cd_cli < 100000 AND c.noplan > 0 ORDER BY noplan; 
"""
    results = request.dbsession.execute(query, {'agence': agence, 'date_debut': date_debut}).fetchall()
    return results


def update_last_connection(request, login, ua_string):
    """Update last connection for login """
    query = "UPDATE eleves SET dern_cnx_le=NOW(), device_used=:ua_string, mdp_oublie = NULL, mdp_oublie_date = NULL WHERE cd_cli=:login;"
    execute_query(request, query, {'login': login, 'ua_string': ua_string})


def update_details(request, login, new_values):
    s = ''
    for param in new_values.keys():
        if s:
            s += ",%s=:%s" % (param, param)
        else:
            s = "%s=:%s" % (param, param)
    new_values['login'] = login
    query = "UPDATE eleves SET %s WHERE CD_CLI=:login" % s
    execute_query(request, query, new_values)


def update_password(request, login, password):
    """Update password for member login"""
    query = "UPDATE eleves SET mdp_hash=SHA1(:password), mdp_oublie=NULL, mdp_oublie_date=NULL WHERE CD_CLI=:login"
    execute_query(request, query, {'login': login, 'password': password})


def is_lien_mdp_oublie(request, lien):
    query = "SELECT mdp_oublie FROM eleves WHERE mdp_oublie=:lien;"
    results = request.dbsession.execute(query, {'lien': lien}).fetchall()
    return len(results) > 0


def get_mdp_oublie_infos(request, lien):
    query = "SELECT * FROM eleves WHERE mdp_oublie=:lien;"
    results = request.dbsession.execute(query, {"lien": lien}).first()
    return results


def update_membre_mdp_oublie(request, cd_cli):
    import uuid, base64

    # get a UUID - URL safe, Base64
    uid = uuid.uuid1()
    urlslug = base64.urlsafe_b64encode(uid.bytes).decode("utf-8").rstrip('=\n').replace('/', '_')
    query = "UPDATE eleves SET mdp_oublie=:urlslug, mdp_oublie_date=now() WHERE cd_cli=:cd_cli;"
    execute_query(request, query, {'urlslug': urlslug, 'cd_cli': cd_cli})
    return urlslug


def to_int(x):
    try:
        number = int(x.replace(',', '.'))
        return number
    except ValueError:
        return 0


def get_faqs(request, faq_id):
    """Lire les FAQ"""
    if faq_id == 0:
        query = "SELECT * FROM p_faq ORDER BY theme, intitule;"
        results = request.dbsession.execute(query).fetchall()
    elif faq_id == -1:
        query = "SELECT * FROM p_faq where theme != 'INTERNE' ORDER BY theme, intitule;"
        results = request.dbsession.execute(query).fetchall()
    else:
        query = "SELECT * FROM p_faq where faq_id = :faq_id;"
        results = request.dbsession.execute(query, {'faq_id': faq_id}).first()
    return results


def get_eleves_by_name(request, name):
    if to_int(name) > 0:
        # lire l'eleve par son code
        query = """SELECT e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.statut 
		FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
		WHERE e.cd_cli=:name ;"""
    else:
        # lire l'eleve par son nom
        query = """SELECT e.nompren, e.cd_cli, e.permis_demande, e.formule, e.cree_le, e.statut 
		FROM eleves e INNER JOIN permis p ON e.permis_demande = p.cat
		WHERE e.nom like :name  ORDER BY e.nompren;"""
    results = request.dbsession.execute(query, {'name': name + "%"}).fetchall()
    return results


def get_departements(request, libelle):
    """ Lire toutes les départements """
    query = "SELECT * FROM p_departements WHERE libelle like :name order by libelle;"
    results = request.dbsession.execute(query, {'name': libelle + "%"}).fetchall()
    return results


def get_codespostaux(request, code):
    query = "SELECT * FROM p_codespostaux WHERE code_postal like :code;"
    results = request.dbsession.execute(query, {'code': code + "%"})
    return results.fetchall()


def get_messageConformation(request, ref):
    """ Lire le tarif seleon la référence """
    query = "select confirmation_md from tarifs where ref = :ref;"
    results = request.dbsession.execute(query, {'ref': ref}).first()
    if results:
        return results.confirmation_md
    else:
        return "<blockquote>Rappel de rendez-vous " + ref + "</blockquote>"


def get_eleve_docs(request, cd_cli, type):
    # lire les docs téléchargés du client
    query = """SELECT *, j.libelle FROM eleves_upload e INNER JOIN p_justifs j ON e.code = j.code
    WHERE e.cd_cli = :cd_cli AND e.type = :type AND (LENGTH(e.nom_fic) > 0 OR e.libelle_fic IS NOT NULL) ORDER BY j.libelle;"""
    results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'type': type}).fetchall()
    return results


def get_eleve_justifs_byligne(request, no_ligne):
    # Lire le justif dans élève
    query = """SELECT p.*, j.LIBELLE, j.conditions FROM eleves_upload p
    INNER JOIN p_justifs j ON j.code = p.code 
    WHERE p.no_ligne = :no_ligne;"""
    results = request.dbsession.execute(query, {'no_ligne': no_ligne}).first()

    return results


def get_eleve_justifs_manquant(request, cd_cli):
    # Lire les justifs manquant dans élève
    query = """SELECT e.* ,j.libelle, j.conditions FROM eleves_upload e, p_justifs j 
    WHERE e.cd_cli = :cd_cli and e.code=j.code AND e.type='JUST' AND LENGTH(e.nom_fic)=0 AND e.libelle_fic IS NULL ORDER BY e.no_tri;"""
    results = request.dbsession.execute(query, {'cd_cli': cd_cli}).fetchall()

    return results


def update_eleve_justif(request, no_ligne, new_values):
    # mettre à jour justifs de l'élève
    s = ''
    for param in new_values.keys():
        if s:
            s += ",%s=:%s" % (param, param)
        else:
            s = "%s=:%s" % (param, param)
    new_values['no_ligne'] = no_ligne
    query = "UPDATE eleves_upload SET %s WHERE no_ligne=:no_ligne" % s
    execute_query(request, query, new_values)


def delete_eleve_justif(request, no_ligne):
    query = "DELETE FROM eleves_upload WHERE no_ligne = :no_ligne ;"
    execute_query(request, query, {'no_ligne': no_ligne})


def init_eleve_justifs(request, cd_cli):
    # la liste des justifs de l'élève est-elle remplie ?
    justifs = get_eleve_justifs(request, cd_cli)
    if not justifs:
        # non, lire la fiche de l'élève
        eleve = get_eleves_by_code(request, cd_cli)
        # et créer les justifs à partir du permis demandé
        query = """INSERT INTO eleves_upload (cd_cli, code, type)
SELECT :cd_cli, code, 'JUST' FROM permis_justifs WHERE cat=:permis_demande;"""
        execute_query(request, query, {'cd_cli': cd_cli, 'permis_demande': eleve.PERMIS_DEMANDE})


def get_stages_by_type(request, stage_type,stage_debut,day_diff):
    
    condition = """;"""
   
    if day_diff < 15 :
        condition = """ and debut <= :stage_debut ;""" 

    if stage_type == 'POINT':
        """ Lire tous les stage PAP dispo a partir de damain """
        query = """
    SELECT 
        concat(type,semaine,groupe) as stage_id,
        type,
        semaine,
        groupe,
        debut, fin,
        dispo,
        CASE
            WHEN groupe = 'A' THEN 'CHARPENNES'
            ELSE 'VAUGNERAY'
        END as lieu,
        total
        FROM pla_stage where type='C' and dispo > 0 and debut > DATE_FORMAT(NOW() ,'%Y-%m-%d') """ + condition

        results = request.dbsession.execute(query,{"stage_debut":stage_debut}).fetchall()
      
    elif stage_type == 'PEM125':
        """ Lire tous les stage PEM dispo a partir de damain """
        query = """
        SELECT 
            concat(type,semaine,groupe) as stage_id,
            debut, fin,dispo,
            'VAUGNERAY' as lieu,
            total
            FROM pla_stage where type='M' and dispo > 0 and debut > DATE_FORMAT(NOW() ,'%Y-%m-%d') and libelle like 'pem%' """ + condition
        results = request.dbsession.execute(query,{"stage_debut":stage_debut}).fetchall()
        
    elif stage_type == 'PASSERELLE':
        """ Lire tous les stage PAS A2 dispo a partir de damain """
        query = """
        SELECT 
             concat(type,semaine,groupe) as stage_id,
             debut, fin,
            'RD 30 - LES AIGUILLONS ou 69670 VAUGNERAY' as lieu,dispo,
            total
            FROM pla_stage where type='M' and dispo > 0 and debut > DATE_FORMAT(NOW() ,'%Y-%m-%d') and libelle like 'pas%' """ + condition
        results = request.dbsession.execute(query,{"stage_debut":stage_debut}).fetchall()
    elif stage_type == 'B96':
        """ Lire tous les stage B96 dispo a partir de demain """
        query = """
        SELECT 
             concat(type,semaine,groupe) as stage_id,
             debut, fin,
            'RD 30 - LES AIGUILLONS ou 69670 VAUGNERAY' as lieu,
            dispo,
            total
            FROM pla_stage where type='B' and dispo > 0 and debut > DATE_FORMAT(NOW() ,'%Y-%m-%d') and groupe ='G' """ + condition
        results = request.dbsession.execute(query,{"stage_debut":stage_debut}).fetchall()
    else:
        results = list()
    return results

def get_stage_by_id(request, stage_id):
    """ Lire le stage demandé """
    type = stage_id[0:1]
    semaine = stage_id[1:7]
    groupe = stage_id[7:8]
    query = """
    SELECT 
        concat(type,semaine,groupe) as stage_id,
        debut, fin,
        getLieu_PAP(groupe, 2) as lieu1,
        getLieu_PAP(groupe, 3) as lieu2,
        dispo,
        total
        FROM pla_stage where type=:type and semaine=:semaine and groupe=:groupe;"""
    results = request.dbsession.execute(query, {'type': type, 'semaine': semaine, 'groupe': groupe}).first()
    return results

def get_stage_ligne_by_cd_cli(request,cd_cli,groupe):
    query = """SELECT * FROM pla_stage_lignes where cd_cli=:cd_cli and groupe=:groupe"""
    results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'groupe': groupe}).first()
    return results


def ins_stage_eleve(request, ref, stage_id, cd_cli, nom, prenom):
    # inscrire un élève dans un stage PAP
    type = stage_id[0:1]
    semaine = stage_id[1:7]
    groupe = stage_id[7:8]

    # controler inscription en double ?
    query = """SELECT s.debut FROM pla_stage_lignes l
    INNER JOIN pla_stage s on s.type = l.type AND s.semaine = l.semaine AND s.groupe = l.groupe
    WHERE l.type=:type AND l.cd_cli=:cd_cli and l.VALIDE ='N'"""
    results = request.dbsession.execute(query, {'type': type, 'cd_cli': cd_cli}).first()
    if results:
        return results.debut
    else:
        # insérer l'élève dans le stage
        query = """CALL spUpd_PLA_STAGE_LIG(:type, :semaine, :groupe, 0, :cd_cli, :nompren, '', :ref, 0, 6, 1, 0, 'WEB')"""
        execute_query(request, query, {'type': type, 'semaine': semaine, 'groupe': groupe, 'cd_cli': cd_cli, 'nompren': nom + ' ' + prenom, 'ref': ref})
        return None