from datetime import datetime

from flask_login import UserMixin

from .data import PILLARS
from .extensions import db


class Solution(db.Model):
    __tablename__ = "solutions"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    website = db.Column(db.String(200))
    logo_filename = db.Column(db.String(100))

    origin = db.Column(db.String(100))
    license = db.Column(db.String(100))
    platforms = db.Column(db.String(200))

    is_validated = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    scores = db.relationship(
        "Score", backref="solution", cascade="all, delete-orphan", lazy=True
    )

    tags = db.relationship(
        "Tag",
        secondary="solution_tags",
        backref=db.backref("solutions", lazy="dynamic"),
    )

    def calculate_scores(self):
        """
        Calcule les scores par pilier (sur 5) et le score global (sur 100).
        Retourne un dictionnaire : { 'pillars': { 'AS1': 3.5 ... }, 'final': 72.0 }
        """

        pillar_scores = {}
        sum_of_averages = 0
        valid_pillars_count = 0

        # Pour chaque pilier défini dans la config
        for p_code in PILLARS.keys():
            # On filtre les scores de la solution actuelle
            # condition 1 : le criterion_id commence par le code pilier (ex: AS1-1 commence par AS1)
            # condition 2 : on ignore les -1 (Non concerné)
            relevant_values = [
                s.value
                for s in self.scores
                if s.criterion_id.startswith(p_code) and s.value != -1
            ]

            if relevant_values:
                # Moyenne du pilier sur 5
                avg = sum(relevant_values) / len(relevant_values)
                pillar_scores[p_code] = round(avg, 2)

                sum_of_averages += avg
                valid_pillars_count += 1
            else:
                # Si aucune note ou que des N/A, on met 0 (ou on pourrait ignorer)
                pillar_scores[p_code] = 0

        # Calcul du Score Global sur 100
        # Formule : (Moyenne des moyennes des piliers) / 5 * 100  =>  Moyenne * 20
        if valid_pillars_count > 0:
            global_avg_on_5 = sum_of_averages / valid_pillars_count
            final_score = round(global_avg_on_5 * 20, 1)  # Ramené sur 100
        else:
            final_score = 0

        return {"pillars": pillar_scores, "final": final_score}


class Users(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(250), unique=True, nullable=False)
    password = db.Column(db.String(250), nullable=False)
    is_admin = db.Column(db.Boolean, default=False)


class Score(db.Model):
    __tablename__ = "scores"

    id = db.Column(db.Integer, primary_key=True)
    solution_id = db.Column(db.Integer, db.ForeignKey("solutions.id"), nullable=False)
    criterion_id = db.Column(db.String(10), nullable=False)

    value = db.Column(db.Integer, nullable=False)

    justification = db.Column(db.Text, nullable=True)
    source = db.Column(db.Text, nullable=True)


class Tag(db.Model):
    __tablename__ = "tags"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True, nullable=False)


class Article(db.Model):
    __tablename__ = "articles"

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    slug = db.Column(db.String(200), unique=True, nullable=False)
    content = db.Column(db.Text, nullable=False)
    published = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    author_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)

    author = db.relationship("Users", backref=db.backref("articles", lazy=True))


solution_tags = db.Table(
    "solution_tags",
    db.Column(
        "solution_id", db.Integer, db.ForeignKey("solutions.id"), primary_key=True
    ),
    db.Column("tag_id", db.Integer, db.ForeignKey("tags.id"), primary_key=True),
)