# -*- 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 jours_feries_france.compute import JoursFeries
import transaction

from .default import (
    execute_query,
)
from ..views.default import (
    to_age,
    )

def nextWorkingDay(date):
    # si date est un jour ferié, retourne le jour suivant
    workDay = date
    jf = JoursFeries.for_year(date.year)
    for jferie, dferie in jf.items():
        if date == dferie:
            workDay = date + timedelta(days = 1) # incrément = 1 jour
            return workDay
        
    return workDay

def isHolidayDay(date):
    # si date est un jour ferié, retourne True
    jf = JoursFeries.for_year(date.year)
    for jferie, dferie in jf.items():
        if date == dferie:
            return jferie
        
    return ''

def get_departements(request):
    """ Lire toutes les départements """
    query = "SELECT * FROM p_departements order by no_tri;"
    results = request.dbsession.execute(query).fetchall()
    return results

def get_origines(request):
    """ Lire toutes les origines """
    query = "SELECT * FROM p_origines where affiche_web order by libelle;"
    results = request.dbsession.execute(query).fetchall()
    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 bd_aem.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_stages_by_type(request, stage_type):
    if stage_type == 'PAP':
        """ Lire tous les stage PAP dispo a partir de damain """
        query = """
    SELECT 
        concat(type,semaine,groupe) as stage_id,
        groupe,
        debut, fin,
        CASE
            WHEN dispo <= 0 THEN 'Complet'
            WHEN dispo =1 THEN '1 place restante'
            WHEN dispo > 5 THEN 'Disponible' 
            ELSE concat(dispo, ' places restantes')
        END as dispo,
        total
        FROM bd_aem.pla_stage where type='C' and debut > DATE_FORMAT(NOW() ,'%Y-%m-%d');"""
        results = request.dbsession.execute(query).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,
            CASE
                WHEN dispo <= 0 THEN 'Complet'
                WHEN dispo =1 THEN '1 place restante'
                WHEN dispo > 5 THEN 'Disponible' 
                ELSE concat(dispo, ' places restantes')
            END as dispo,
            total
            FROM bd_aem.pla_stage where type='M' and debut > DATE_FORMAT(NOW() ,'%Y-%m-%d') and libelle like 'pem%';"""
        results = request.dbsession.execute(query).fetchall()
    elif stage_type == 'PASA2':
        """ 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' as lieu1, '69670 VAUGNERAY' as lieu2,
            CASE
                WHEN dispo <= 0 THEN 'Complet'
                WHEN dispo =1 THEN '1 place restante'
                WHEN dispo > 5 THEN 'Disponible' 
                ELSE concat(dispo, ' places restantes')
            END as dispo,
            total
            FROM bd_aem.pla_stage where type='M' and debut > DATE_FORMAT(NOW() ,'%Y-%m-%d') and libelle like 'pas%';"""
        results = request.dbsession.execute(query).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' as lieu1, '69670 VAUGNERAY' as lieu2,
            CASE
                WHEN dispo <= 0 THEN 'Complet'
                WHEN dispo =1 THEN '1 place restante'
                WHEN dispo > 5 THEN 'Disponible' 
                ELSE concat(dispo, ' places restantes')
            END as dispo,
            total
            FROM bd_aem.pla_stage where type='B' and debut > DATE_FORMAT(NOW() ,'%Y-%m-%d') and groupe ='G';"""
        results = request.dbsession.execute(query).fetchall()
    elif stage_type == 'POST':
        """ Lire tous les stages POST PERMIS dispo a partir de demain """
        query = """
        SELECT 
             concat(type,semaine,groupe) as stage_id,
             debut, fin,
             libelle,
            CASE
                WHEN dispo <= 0 THEN 'Complet'
                WHEN dispo =1 THEN '1 place restante'
                WHEN dispo > 5 THEN 'Disponible' 
                ELSE concat(dispo, ' places restantes')
            END as dispo,
            total
            FROM bd_aem.pla_stage where type='B' and debut > DATE_FORMAT(NOW() ,'%Y-%m-%d') and libelle like 'POST %';"""
        results = request.dbsession.execute(query).fetchall()
    else:
        results = None
    return results

def get_tarifs(request, ref):
    """ Lire le tarif seleon la référence """
    query = "select puttc from tarifs where ref = :ref;"
    results = request.dbsession.execute(query, {'ref': ref}).first()
    return results.puttc

def get_permis(request):
    """ Lire les permis demandés """
    query = "SELECT * FROM permis WHERE type_examen != 'GL' AND agecode > 0;"
    results = request.dbsession.execute(query, ).fetchall()
    return results


def ins_eleve_by_formule(request, formule, civilite, nom, nom_jf, prenom, date_nais, lieu_nais, dept_nais, adres1, adres2, codpost, ville, no_tel_dom, no_tel_bur, no_tel_gsm, mail, no_permis, obtenu_le, lieu_permis, cas, origine, infraction_lieu, infraction_le, infraction_a, NePasSpammer, agence):

    # controler que l'élève n'est pas déjà créé
    cd_cli = isEleveCreated(request, civilite, nom, nom_jf, prenom, date_nais, lieu_nais, dept_nais, formule)
    if cd_cli > 0:
        # insérer une ligne de SUIVI
        source = 'BIS - Orgine : %s' % origine
        query = "CALL spUpd_ELEVES_SUIVI('SUIVI',0,:cd_cli,1,0,:source,'WEB')"
        execute_query(request, query, {'cd_cli': cd_cli, 'source':source})
        return cd_cli

    missing = 0
    if formule == 'POINT':
        permis = 'POINT'
        permis_obtenu = 'B'
    elif formule == 'PEM125':
        permis = 'AL'
        permis_obtenu = 'B'
    elif formule == 'PASSERELLE':
        permis = 'A'
        permis_obtenu = 'A2'
    elif formule == 'ST_CODE_3J':
        permis = 'NR'
        permis_obtenu = ''
        # cocher toutes les cases
        missing = -1
    elif formule == 'B96':
        permis = 'B'
        permis_obtenu = 'B'
        # recherche agence de suivi selon le code postal
        # agence_suivi = get_agence_suivi(request, codpost)
        # if agence_suivi:
        #    agence = agence_suivi.agence
        # else:
        #     agence = 6
    elif formule == 'ROUSSEAU':
        permis = no_permis  # permis demandé
        permis_obtenu = ''
        agence = 9
    else:
        return 0
    # créer une fiche élève selon formule
    query = """CALL spUpd_ELEVES_ETATCIVIL(0, :civilite, :nom, :prenom, :nom_jf, '', '', '', :adres1, :adres2, :codpost, :ville, :no_tel_dom, :no_tel_gsm, :no_tel_bur, '', '', 'FR', :date_nais, :lieu_nais, :dept_nais ,'FR', 'P', '', :NePasSpammer, 0, 0, :mail, 0, :agence, 'WEB')"""
    results = request.dbsession.execute(query, {'civilite':civilite, 'nom':nom, 'nom_jf':nom_jf, 'prenom':prenom, 'date_nais':date_nais.strftime("%Y/%m/%d"), 
                                                'lieu_nais':lieu_nais, 'dept_nais':dept_nais, 'adres1':adres1, 'adres2':adres2, 'codpost':codpost, 'ville':ville,
                                                'no_tel_dom':no_tel_dom, 'no_tel_bur':no_tel_bur, 'no_tel_gsm':no_tel_gsm, 
                                                'mail':mail, 'NePasSpammer':NePasSpammer, 'agence':agence}).first()
    cd_cli = results.newcode

    # mise à jour onglet INSCRIPTION
    query = """
CALL spUpd_ELEVES_INSCRIT(:cd_cli,:permis,:formule,0,0,0,0,'',-1,-1,:missing,:missing,-1,:missing,:missing,:missing,:missing,0,0,0, \
:cas, :lieu_permis, :infraction_lieu,NOW(),'','','NR','NR', '', '','','','','',0,'','','','','','',0,0,'WEB')"""
    execute_query(request, query, {'cd_cli': cd_cli, 'lieu_permis':lieu_permis, 'cas':cas[:5], 'infraction_lieu':infraction_lieu, 'permis':permis, \
                                   'formule':formule,  'missing':missing})

    if formule == 'POINT':
        # mise à jour date d'infraction
        query = """
    UPDATE ELEVES SET permis_delivre_le=:obtenu_le, infraction_le=:infraction_le, infraction_a=:infraction_a
    WHERE cd_cli=:cd_cli"""
        execute_query(request, query, {'cd_cli': cd_cli, 'obtenu_le':obtenu_le.strftime("%Y/%m/%d"), 'infraction_le':infraction_le, 'infraction_a':infraction_a})

    # génére les écritures d'inscription selon la formule 
    query = "CALL spINS_ELEVES_DEBIT_INSCRIPTION(:formule, :cd_cli, NOW(), :agence, 'WEB')"
    execute_query(request, query, {'cd_cli': cd_cli, 'formule':formule, 'agence':agence})

    # ajouter le permis déjà obtenu_le
    if len(permis_obtenu) > 0:
        query = """
INSERT INTO eleves_permis (cd_cli, permis, obtenu_le, numero, lieu, cd_uti) 
VALUES (:cd_cli, :permis_obtenu, :obtenu_le, :no_permis, :lieu_permis, 'WWW');"""
        execute_query(request, query, {'cd_cli': cd_cli, 'permis_obtenu': permis_obtenu, 'obtenu_le': obtenu_le.strftime("%Y/%m/%d"), 'no_permis': no_permis, 'lieu_permis': lieu_permis})

        # mise à jour un coup pour le CERFA 02 (dernier permis  obtenu)
        query = "UPDATE eleves_permis SET cd_uti='WEB' WHERE cd_cli=:cd_cli"
        execute_query(request, query, {'cd_cli': cd_cli})

    # mettre une date de fin résa pour le PASS ROUSSEAU
    if formule == 'ROUSSEAU':
        query = "UPDATE eleves_cpt SET date_valeur=CURRENT_DATE(),fin_reservation=CURRENT_DATE() WHERE cd_cli = :cd_cli AND ref = 'PASS_ROUSS';"
        execute_query(request, query, {'cd_cli': cd_cli})
        
        # créer éventuellement un prospect selon le code postal
        ins_prospect(request, cd_cli, civilite, nom, prenom, adres1, adres2, codpost, ville, no_tel_gsm, email, permis, \
                     'Pass_Rousseau', 'Pass Rousseau', 'Achat de Pass Rousseau via le web')
                        
    # insérer une ligne de SUIVI
    source = 'Orgine : %s' % origine
    query = "CALL spUpd_ELEVES_SUIVI('SUIVI',0,:cd_cli,1,0,:source,'WEB')"
    execute_query(request, query, {'cd_cli': cd_cli, 'source':source})

    return cd_cli


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"""
    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

def isEleveCreated(request, civilite, nom, nom_jf, prenom, date_nais, lieu_nais, dept_nais, formule):

    # vérifier que l'élève n'est pas déjà créé
    query = """SELECT cd_cli as cd_cli FROM eleves
WHERE nom=:nom AND prenom=:prenom AND nom_jf=:nom_jf AND date_nais=:date_nais AND lieu_nais=:lieu_nais AND dept_nais=:dept_nais 
AND formule=:formule AND cloture_le IS NULL AND DATEDIFF(cree_le,CURRENT_DATE) < 15;"""
    results = request.dbsession.execute(query, {'nom':nom, 'prenom':prenom, 'nom_jf':nom_jf, 'date_nais':date_nais, 'lieu_nais':lieu_nais, 'dept_nais':dept_nais, 'formule':formule}).first()
    if results:
        return results.cd_cli
    else:
        return 0

def get_pass_dispo(request):
    # lire les pass rousseau dosponibles
    query = """SELECT * FROM pass_rousseau WHERE cd_cli IS NULL;"""
    results = request.dbsession.execute(query, )
    return results.first()

def get_agence_suivi(request, cp):
    """ Lire l'agence de suivi de l'élève """
    query = "SELECT * FROM p_cp_agence WHERE code = :cp;"
    results = request.dbsession.execute(query,  {'cp': cp}).first()
    return results

def get_prospects_by_code(request, code):
    # lire le contact par son code
    query = """SELECT * FROM prospects e WHERE e.cd_prospect=:code;"""
        
    results = request.dbsession.execute(query, {'code': code}).first()
    return results

def update_prospect_gagne(request, cd_prospect, cd_cli, cd_uti, agence):
    query = "UPDATE prospects SET cd_cli=:cd_cli, statut='Gagné', cloture_le=CURRENT_DATE, a_relancer_le=NULL, cd_uti=:cd_uti WHERE cd_prospect=:cd_prospect;"
    execute_query(request, query, {'cd_prospect': cd_prospect, 'cd_cli': cd_cli, 'cd_uti': cd_uti, 'agence': agence})

    query = "DELETE FROM prospects_rdv where cd_prospect = :cd_prospect AND debut_rdv > CURRENT_DATE;"
    execute_query(request, query, {'cd_prospect': cd_prospect})

def get_stages_code(request):
    " Lire tous les stage CODE dispo a partir de demain "
    query = """
SELECT date, groupe,
    CONCAT(type,date,groupe) as stage_id,
	CASE 
		WHEN DAYOFWEEK(DATE) = 7 THEN 'Stage 3 jours les samedis'
		ELSE 'Stage 3 jours en semaine'
	END as jour,
	CASE 
		WHEN DAYOFWEEK(DATE) = 7 THEN 'samedis'
		ELSE 'semaine'
	END as type,
    getLieu_CODE(groupe, 1) as lieu1,
    getLieu_CODE(groupe, 2) as lieu2,
    getLieu_CODE(groupe, 3) as lieu3,
	CASE
		WHEN dispo <= 0 THEN 'Complet'
		WHEN dispo =1 THEN '1 place restante'
		WHEN dispo > 5 THEN 'Places disponibles' 
		ELSE concat(dispo, ' places restantes')
	END as dispo
FROM bd_aem.pla_moto where type = 'D' AND date > CURRENT_DATE() AND date < DATE_ADD(CURRENT_DATE(),INTERVAL 3 MONTH) AND libelle like 'J1%' AND total > 0;"""
    results = request.dbsession.execute(query).fetchall()
    return results

def get_moto_by_id(request, moto_id):
    """ Lire le stage demandé """
    type = moto_id[0:1]
    date = moto_id[1:11]
    groupe = moto_id[11:12]
    query = """
SELECT *,
    CASE 
    WHEN DAYOFWEEK(DATE) = 7 THEN 'Stage 3 jours les samedis'
    ELSE 'Stage 3 jours en semaine'
END as jour,
TO_SEMAINE(DATE_FORMAT(date, "%a %e")) as date1,
CASE 
    WHEN DAYOFWEEK(DATE) = 7 THEN TO_SEMAINE(CONCAT(DATE_FORMAT(DATE_ADD(date, INTERVAL 7 DAY), "%a %e")))
    ELSE TO_SEMAINE(CONCAT(DATE_FORMAT(DATE_ADD(date, INTERVAL 1 DAY), "%a %e")))
END as date2,
CASE 
    WHEN DAYOFWEEK(DATE) = 7 THEN TO_SEMAINE(DATE_FORMAT(DATE_ADD(date, INTERVAL 14 DAY), "%a %e %b %Y"))
    ELSE TO_SEMAINE(DATE_FORMAT(DATE_ADD(date, INTERVAL 2 DAY), "%a %e %b %Y"))
END as date3,
getLieu_CODE(groupe, 1) as lieu1,
getLieu_CODE(groupe, 2) as lieu2,
getLieu_CODE(groupe, 3) as lieu3
FROM bd_aem.pla_moto where type=:type and date=:date and groupe=:groupe;"""
    results = request.dbsession.execute(query, {'type': type, 'date': date, 'groupe': groupe}).first()
    return results

def ins_stage_code(request, ref, moto_id, cd_cli):

    # inscrire un élève dans un stage PAP
    type = moto_id[0:1]
    dateCode = datetime.strptime(moto_id[1:11], '%Y-%m-%d')
    groupe = moto_id[11:12]
    
    n = 0
    dcode = dateCode.date()        

    if dateCode.weekday() == 5 : 
        # dateCode = samedi ? ajout sur 3 SAMEDIS
        while n < 3:
            # insérer l'élève dans le stage CODE 
            query = "CALL spINS_PLA_CODE_LIG(:type, :dateCode, :groupe, :cd_cli, :ref);"
            execute_query(request, query, {'type':type, 'dateCode': dcode.strftime('%Y-%m-%d'), 'groupe': groupe, 'cd_cli': cd_cli, 'ref':ref})
            dcode = dcode + timedelta(days = 7) # incrément = 1 semaine
            n = n + 1
    else:
        # dateCode = lundi ? ajout sur 3 jours consécutifs
        import pdb;pdb.set_trace()
        while n < 3:
            # insérer l'élève dans le stage CODE 
            query = "CALL spINS_PLA_CODE_LIG(:type, :dateCode, :groupe, :cd_cli, :ref);"
            execute_query(request, query, {'type':type, 'dateCode':  dcode.strftime('%Y-%m-%d'), 'groupe': groupe, 'cd_cli': cd_cli, 'ref':ref})
            n = n + 1
            dcode = dcode + timedelta(days = 1) # incrément = 1 jour
            dcode =  nextWorkingDay(dcode)   # saute 1 jour si jour férié
            
    return 

def ins_prospect(request, cd_cli, civilite, nom, prenom, adres1, adres2, codpost, ville, no_tel_gsm, mail, permis, type, origine, obs):
    # recherche agence de suivi
    agence_suivi = get_agence_suivi(request, codpost)
    if agence_suivi :           
        query = "CALL spINS_PROSPECTS(:cd_cli,:civilite,:nom,:prenom,:ville,:no_tel_gsm,:email,:permis,:age, :agence_suivi)"
        results = request.dbsession.execute(query, {'cd_cli': cd_cli, 'civilite': civilite, 'nom':nom, 'prenom': prenom, 'ville':ville, 
                                       'no_tel_gsm': no_tel_gsm, 'email':mail, 'permis': permis, 'age': to_age(date_nais, '>'), 'agence_suivi':agence_suivi.agence})
        cd_prospect = results.newcode
        query = "UPDATE prospects SET cp=:codpost, type_contact=:type, origine=:origine, observation=:obs WHERE cd_prospect = :cd_prospect;"
        execute_query(request, query, {'cd_prospect': cd_prospect, 'codpost':codpost, 'type':type, 'origine':origine, 'obs':obs})