from app import db

Semestres_Competences = db.Table("Semestres_Competences",
    db.Column("Semestre_num", db.String(32), db.ForeignKey("semestre.num")),
    db.Column("Competence_code", db.String(32), db.ForeignKey("competence.code"))
)

ACs_Competences = db.Table("ACs_Competences",
    db.Column("AC_code", db.String(32), db.ForeignKey("AC.code")),
    db.Column("Competence_code", db.String(32), db.ForeignKey("competence.code"))
)

Ressources_ACs = db.Table("Ressources_ACs",
    db.Column("Ressource_code", db.String(32), db.ForeignKey("ressource.code")),
    db.Column("AC_code", db.String(32), db.ForeignKey("AC.code"))
)

Ressources_SAEs = db.Table("Ressources_SAEs",
    db.Column("Ressource_code", db.String(32), db.ForeignKey("ressource.code")),
    db.Column("SAE_code", db.String(32), db.ForeignKey("SAE.code"))
)

SAEs_ACs = db.Table("SAEs_ACs",
    db.Column("SAE_code", db.String(32), db.ForeignKey("SAE.code")),
    db.Column("AC_code", db.String(32), db.ForeignKey("AC.code"))
)

class Semestre(db.Model):
    num = db.Column(db.String(1), primary_key = True)
    ues = db.relationship("Competence", order_by="Competence.code", secondary=Semestres_Competences, lazy=False, backref=db.backref("semestres", lazy=False))

    def export(self):
        result = dict(self.__dict__)
        result.pop("_sa_instance_state", None)
        return result

    def __repr__(self):
        return "<Semestre {}>".format(self.num)

class Competence(db.Model):
    code = db.Column(db.String(32), primary_key = True, info={'label': 'Code'})
    nom = db.Column(db.String(255), info={'label': 'Nom'})
    diminutif = db.Column(db.String(30), info={'label': 'Diminutif'})
    description = db.Column(db.Text(), info={'label': 'Description'})
    composantes = db.Column(db.Text(), info={'label': 'Composantes'})
    situations = db.Column(db.Text(), info={'label': 'Situations'})
    niveaux = db.Column(db.Text(), info={'label': 'Niveaux'})
    acs = db.relationship("AC", order_by="AC.code", secondary=ACs_Competences, lazy=False, backref=db.backref("competences", lazy=False))

    def updateCoefSAE(self, saes, query, coef=0):
        listeSAE = [coefsae.sae for coefsae in self.saes]
        for sae in saes:
            if sae in query:
                if sae in listeSAE:
                    CoefSAE.query.filter_by(sae=sae, competence=self).first().coef = coef
                    listeSAE.remove(sae)
                else: 
                    self.saes.append(CoefSAE(competence=self, sae=sae, coef=coef))
        for sae in listeSAE:
            db.session.delete(CoefSAE.query.filter_by(sae=sae, competence=self).first())
        db.session.commit()

    def updateCoefRessource(self, ressources, query, coef=0):
        listeRessource = [coefressource.ressource for coefressource in self.ressources]
        for ressource in ressources:
            if ressource in query:
                if ressource in listeRessource:
                    CoefRessource.query.filter_by(ressource=ressource, competence=self).first().coef = coef
                    listeRessource.remove(ressource)
                else:
                    self.ressources.append(CoefRessource(competence=self, ressource=ressource, coef=coef))
        for ressource in listeRessource:
            db.session.delete(CoefRessource.query.filter_by(ressource=ressource, competence=self).first())
        db.session.commit()

    def export(self):
        result = dict(self.__dict__)
        result.pop("_sa_instance_state", None)
        return result

    def getLabel(self):
        return self.code + " - " + self.nom

    def __repr__(self):
        return "<Competence {}>".format(self.code)

class CoefSAE(db.Model):
    competence_code = db.Column(db.String(32), db.ForeignKey("competence.code"), primary_key=True)
    sae_code = db.Column(db.String(32), db.ForeignKey("SAE.code"), primary_key=True)
    coef = db.Column(db.String(2))
    competence = db.relationship("Competence", lazy=False, backref=db.backref("saes", cascade="save-update, merge, delete, delete-orphan"))
    sae = db.relationship("SAE", lazy=False, backref=db.backref("competences", cascade="save-update, merge, delete, delete-orphan"))

    def export(self):
        result = dict(self.__dict__)
        result.pop("_sa_instance_state", None)
        return result

class CoefRessource(db.Model):
    competence_code = db.Column(db.String(32), db.ForeignKey("competence.code"), primary_key=True)
    ressource_code = db.Column(db.String(32), db.ForeignKey("ressource.code"), primary_key=True)
    coef = db.Column(db.String(2))
    competence = db.relationship("Competence", lazy=False, backref=db.backref("ressources", cascade="save-update, merge, delete, delete-orphan"))
    ressource = db.relationship("Ressource", lazy=False, backref=db.backref("competences", cascade="save-update, merge, delete, delete-orphan"))

    def export(self):
        result = dict(self.__dict__)
        result.pop("_sa_instance_state", None)
        return result

class AC(db.Model):
    code = db.Column(db.String(32), primary_key = True, info={'label': 'Code'})
    titre = db.Column(db.String(255), info={'label': 'Titre'})
    saes = db.relationship("SAE", secondary=SAEs_ACs, lazy=False, backref=db.backref("acs", lazy=False))
    ressources = db.relationship("Ressource", order_by="Ressource.code", secondary=Ressources_ACs, lazy=False, backref=db.backref("acs", lazy=False))

    def export(self):
        result = dict(self.__dict__)
        result.pop("_sa_instance_state", None)
        return result

    def getLabel(self):
        return self.code + " - " + self.titre

    def __repr__(self):
        return "<AC {}>".format(self.code)
  
class PN(db.Model):
    code = db.Column(db.String(32), primary_key = True, info={'label': 'Code'})
    nom = db.Column(db.String(255), info={'label': 'Nom'})
    diminutif = db.Column(db.String(30), info={'label': 'Diminutif'})
    description = db.Column(db.Text(), info={'label': 'Description'})
    type = db.Column(db.String(1))

    def export(self):
        result = dict(self.__dict__)
        result.pop("_sa_instance_state", None)
        return result

    def __repr__(self):
        return "<PN {}>".format(self.code)

class SAE(db.Model):
    code = db.Column(db.String(32), primary_key = True, info={'label': 'Code'})
    titre = db.Column(db.String(255), info={'label': 'Titre'})
    semestre = db.Column(db.String(255), info={'label': 'Semestre'})
    heures_encadrees = db.Column(db.String(3), info={'label': 'Heures Encadrées'})
    heures_tp = db.Column(db.String(3), info={'label': 'Heures TP'})
    projet = db.Column(db.String(3), info={'label': 'Projet'})
    description = db.Column(db.Text(), info={'label': 'Description'})
    ressources = db.relationship("Ressource", order_by="Ressource.code", secondary=Ressources_SAEs, lazy=False, backref=db.backref("saes", lazy=False))
    livrables = db.Column(db.Text(), info={'label': 'Livrables'})
    motscles = db.Column(db.String(255), info={'label': 'Mots Clés'})

    def getCoef(self,competence):
        return CoefSAE.query.filter_by(sae=self, competence=competence).first().coef

    def setCoef(self,coef,competence):
        CoefSAE.query.filter_by(sae=self, competence=competence).first().coef = coef

    def getCode(self):
        return self.code[3:]

    def export(self):
        result = dict(self.__dict__)
        result.pop("_sa_instance_state", None)
        return result

    def getLabel(self):
        return self.code + " - " + self.titre

    def __repr__(self):
        return "<SAE {}>".format(self.code)

class Ressource(db.Model): 
    code = db.Column(db.String(32), primary_key = True, info={'label': 'Code'})
    nom = db.Column(db.String(255), info={'label': 'Nom'})
    semestre = db.Column(db.String(255), info={'label': 'Semestre'})
    heures_formation = db.Column(db.String(3), info={'label': 'Heures Formation'})
    heures_tp = db.Column(db.String(3), info={'label': 'Heures TP'})
    prerequis = db.Column(db.String(255), info={'label': 'Prerequis'})
    contexte = db.Column(db.Text(), info={'label': 'Contexte'})
    contenu = db.Column(db.Text(), info={'label': 'Contenu'})
    motscles = db.Column(db.String(255), info={'label': 'Mots Clés'})

    def getCoef(self,competence):
        return CoefRessource.query.filter_by(ressource=self, competence=competence).first().coef

    def setCoef(self,coef,competence):
        CoefRessource.query.filter_by(ressource=self, competence=competence).first().coef = coef

    def getCode(self):
        return self.code[1:]

    def export(self):
        result = dict(self.__dict__)
        result.pop("_sa_instance_state", None)
        return result

    def getLabel(self):
        return self.code + " - " + self.nom

    def __repr__(self):
        return "<Ressource {}>".format(self.code)