# -*- 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
import transaction

from dateutil.relativedelta import relativedelta
from .default import (
    execute_query,
    get_eleves_by_code,
)
from ..views.default import (
    envoyerMail,
)

def get_tarifs(request, permis):
    """Lire les tarifs selon le permis"""
    if permis == "B":
        query = "SELECT REF, LIB, PUTTC FROM tarifs WHERE ref IN ('HCB','INTERNET','ECHECT','EXAM_CODE','RVP','CIRCUIT') order by fam, lib;"
    elif permis == "B78":
        query = "SELECT REF, LIB, PUTTC FROM tarifs WHERE ref IN ('HCB78','INTERNET','ECHECT','EXAM_CODE','RVP','CIRCUIT') order by fam, lib;"
    else:
        query = "SELECT REF, LIB, PUTTC FROM tarifs WHERE ref IN ('HCA3','TA','INTERNET','ECHECT','EXAM_CODE') order by fam, lib;"
    results = request.dbsession.execute(query, {})
    return results.fetchall()

def ins_tickets(request, cd_cli, date_ope, reference, montant, autorisation, erreur, prochain_montant, reconduction):

    # ajout d'une ligne dans la table tickets_cb
    query = """INSERT INTO tickets_cb (date, cd_cli, ref_commande, montant, prochain_montant, autorisation, erreur) 
			VALUES (:date_ope, :cd_cli, :reference, :montant, :prochain_montant, :autorisation, :erreur);"""
    execute_query(request, query, {'cd_cli':cd_cli, 'date_ope': date_ope.strftime('%Y-%m-%d %H:%M'), 'reference':reference,
                            'montant': montant, 'autorisation':autorisation, 'erreur':erreur, 'prochain_montant': prochain_montant})

    #2e ou 3e réglement d'un abonnement ? oui, on ne créé pas d'écriture
    if reconduction == '' and erreur == '00000':
        # non, Créer une écriture de crédit dans le compte du client si OK
        query = "CALL spINS_ELEVES_CREDIT_WEB(:cd_cli, :date_ope, :montant, :reference);"
        execute_query(request, query, {'cd_cli':cd_cli, 'date_ope': date_ope.strftime('%Y-%m-%d'), 'montant': montant, 'reference':reference})
        # réglement comptant ?
        if prochain_montant > 0:
            # non, créer 2e écriture de crédit dans le compte du client
            date_suivante = date_ope + relativedelta(months=+1)
            query = "CALL spINS_ELEVES_CREDIT_WEB(:cd_cli, :date_ope, :montant, :reference);"
            execute_query(request, query, {'cd_cli':cd_cli, 'date_ope': date_suivante.strftime('%Y-%m-%d'), 'montant': prochain_montant, 'reference':reference})
            # 3ème écriture 
            date_suivante = date_ope + relativedelta(months=+2)
            query = "CALL spINS_ELEVES_CREDIT_WEB(:cd_cli, :date_ope, :montant, :reference);"
            execute_query(request, query, {'cd_cli':cd_cli, 'date_ope': date_suivante.strftime('%Y-%m-%d'), 'montant': prochain_montant, 'reference':reference})
    
    return None

def confirmerPass(request, cd_cli):

    # controler la disponibilité des PASS 
    query = "SELECT * FROM pass_rousseau WHERE cd_cli IS NULL order by no_id;"
    results = request.dbsession.execute(query, {}).first()
    if results :
        # lire le nom & prenom de l'élève
        eleve = get_eleves_by_code(request, cd_cli)
        nompren = "%s %s %s" % (eleve.CIVILITE, eleve.NOM, eleve.PRENOM)

        # affecter le PASS au client
        query = "UPDATE pass_rousseau SET cd_cli=:cd_cli, nom=:nom, code_postal=:code_postal, vendu_le=CURRENT_DATE() WHERE no_id=:no_id;"
        execute_query(request, query, {'cd_cli':cd_cli, 'nom': eleve.NOMPREN, 'code_postal': eleve.CODEPOST, 'no_id': results.no_id})
    
        # confirmer l'inscription du client
        query = "UPDATE eleves_cpt SET fin_reservation = NULL WHERE cd_cli = :cd_cli AND ref = 'PASS_ROUSS';"
        execute_query(request, query, {'cd_cli':cd_cli})
        
        # cloturer la fiche du client
        query = "UPDATE eleves SET cloture_le = CURRENT_DATE, statut = 90 WHERE cd_cli = :cd_cli;"
        execute_query(request, query, {'cd_cli':cd_cli})

        body = getMessageText(request, 'envoiPassRousseau', nompren, '', '', results.identifiant, results.mdp)

        destinataires=[eleve.email, request.registry.settings['monaem.comm_email'],]
        envoyerMail(request, destinataires, "Votre inscription au code en ligne : vos identifiants", body)
    else:
        # lire le nom & prenom de l'élève
        eleve = get_eleves_by_code(request, cd_cli)
        nompren = "%s %s %s" % (eleve.CIVILITE, eleve.NOM, eleve.PRENOM)
        body = getMessageText(request, 'dispoPassRousseau', nompren, '', '', '', '')

        destinataires=[eleve.email, request.registry.settings['monaem.comm_email'],]
        envoyerMail(request, destinataires, "Votre inscription au code en ligne EN ATTENTE", body)
        
    return None