# -*- coding: utf-8 -*-

##############################################################################
#
# Gestion scolarite IUT
#
# Copyright (c) 1999 - 2022 Emmanuel Viennet.  All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#
#   Emmanuel Viennet      emmanuel.viennet@viennet.net
#
##############################################################################

"""Gestion des cursus (jurys suivant la formation)
"""
from sqlalchemy.sql import text

from app import db
from app.but import cursus_but
from app.scodoc import sco_cursus_dut

from app.comp.res_compat import NotesTableCompat
from app.comp import res_sem
from app.models import FormSemestre
import app.scodoc.notesdb as ndb

# SituationEtudParcours -> get_situation_etud_cursus
def get_situation_etud_cursus(
    etud: dict, formsemestre_id: int
) -> sco_cursus_dut.SituationEtudCursus:
    """renvoie une instance de SituationEtudCursus (ou sous-classe spécialisée)"""
    formsemestre = FormSemestre.query.get_or_404(formsemestre_id)
    nt: NotesTableCompat = res_sem.load_formsemestre_results(formsemestre)

    if formsemestre.formation.is_apc():
        return cursus_but.SituationEtudCursusBUT(etud, formsemestre_id, nt)

    parcours = nt.parcours
    if parcours.ECTS_ONLY:
        return sco_cursus_dut.SituationEtudCursusECTS(etud, formsemestre_id, nt)
    return sco_cursus_dut.SituationEtudCursusClassic(etud, formsemestre_id, nt)


def formsemestre_get_etud_capitalisation(
    formation_id: int, semestre_idx: int, date_debut, etudid: int
) -> list[dict]:
    """Liste des UE capitalisées (ADM) correspondant au semestre sem et à l'étudiant.

    Recherche dans les semestres de la même formation (code) avec le même
    semestre_id et une date de début antérieure à celle du semestre mentionné.
    Et aussi les UE externes validées.

    Resultat: [ { 'formsemestre_id' :
                  'ue_id' : ue_id dans le semestre origine
                  'ue_code' :
                  'moy_ue' :
                  'event_date' :
                  'is_external'
                  } ]
    """
    cnx = ndb.GetDBConnexion()
    cursor = cnx.cursor(cursor_factory=ndb.ScoDocCursor)
    cursor.execute(
        """
    SELECT DISTINCT SFV.*, ue.ue_code
    FROM notes_ue ue, notes_formations nf,
    notes_formations nf2, scolar_formsemestre_validation SFV, notes_formsemestre sem

    WHERE ue.formation_id = nf.id
    and nf.formation_code = nf2.formation_code
    and nf2.id=%(formation_id)s

    and SFV.ue_id = ue.id
    and SFV.code = 'ADM'
    and SFV.etudid = %(etudid)s

    and ( (sem.id = SFV.formsemestre_id
        and sem.date_debut < %(date_debut)s
        and sem.semestre_id = %(semestre_id)s )
        or (
            ((SFV.formsemestre_id is NULL) OR (SFV.is_external)) -- les UE externes ou "anterieures"
            AND (SFV.semestre_id is NULL OR SFV.semestre_id=%(semestre_id)s)
           ) )
    """,
        {
            "etudid": etudid,
            "formation_id": formation_id,
            "semestre_id": semestre_idx,
            "date_debut": date_debut,
        },
    )

    return cursor.dictfetchall()


def list_formsemestre_utilisateurs_uecap(formsemestre_id):
    """Liste des formsemestres pouvant utiliser une UE capitalisee de ce semestre
    (et qui doivent donc etre sortis du cache si l'on modifie ce
    semestre): meme code formation, meme semestre_id, date posterieure"""
    formsemestre = FormSemestre.query.get(formsemestre_id)

    cursor = db.session.execute(
        text(
            """
            SELECT sem.id
            FROM notes_formsemestre sem, notes_formations F
            WHERE sem.formation_id = F.id
            and F.formation_code = :formation_code
            and sem.semestre_id = :semestre_id
            and sem.date_debut >= :date_debut
            and sem.id != :formsemestre_id;
            """
        ),
        {
            "formation_code": formsemestre.formation.formation_code,
            "semestre_id": formsemestre.semestre_id,
            "formsemestre_id": formsemestre_id,
            "date_debut": formsemestre.date_debut,
        },
    )

    return [x[0] for x in cursor]