from models.database import execute_query, execute_single_query


class ReportModel:
    @staticmethod
    def get_project_temperament_counts(project_id):
        """
        Returns rows of temperament counts per person for a given project.
        Each row: people_id, people_name, temperament_id, temperament_name, temperament_color, cnt
        Only counts answers that have a linked option with a temperament.
        """
        query = """
        SELECT a.people_id,
               p.name AS people_name,
               t.id   AS temperament_id,
               t.name AS temperament_name,
               t.color AS temperament_color,
               COUNT(*) AS cnt
        FROM answers a
        JOIN peoples p ON p.id = a.people_id
        JOIN options o ON o.id = a.option_id
        JOIN temperaments t ON t.id = o.temperament_id
        WHERE a.project_id = %s
        GROUP BY a.people_id, p.name, t.id, t.name, t.color
        ORDER BY p.name ASC, t.id ASC
        """
        return execute_query(query, (project_id,))

    @staticmethod
    def get_person_temperament_counts(person_id):
        """
        Returns temperament counts for a single person across all their answers.
        Each row: temperament_id, temperament_name, temperament_color, cnt
        Only counts answers that have a linked option with a temperament.
        """
        query = """
        SELECT t.id   AS temperament_id,
               t.name AS temperament_name,
               t.color AS temperament_color,
               COUNT(*) AS cnt
        FROM answers a
        JOIN options o ON o.id = a.option_id
        JOIN temperaments t ON t.id = o.temperament_id
        WHERE a.people_id = %s
        GROUP BY t.id, t.name, t.color
        ORDER BY t.id ASC
        """
        return execute_query(query, (person_id,))

    @staticmethod
    def get_person_temperament_ranking(person_id):
        """
        Returns temperaments ordered by count (descending) for a person.
        Returns a list of temperament IDs in order from most to least frequent.
        """
        query = """
        SELECT t.id AS temperament_id,
               t.name AS temperament_name,
               COUNT(*) AS cnt
        FROM answers a
        JOIN options o ON o.id = a.option_id
        JOIN temperaments t ON t.id = o.temperament_id
        WHERE a.people_id = %s
        GROUP BY t.id, t.name
        ORDER BY cnt DESC, t.name ASC
        """
        results = execute_query(query, (person_id,))
        return [r['temperament_id'] for r in results]

    @staticmethod
    def find_matching_recommendation(person_id):
        """
        Find a recommendation that matches the person's temperament ranking.
        Returns the recommendation object if found, None otherwise.
        """
        from models.recommendation_model import RecommendationModel
        import json

        # Get person's temperament ranking
        person_ranking = ReportModel.get_person_temperament_ranking(person_id)

        if not person_ranking:
            return None

        # Get all recommendations
        recommendations = RecommendationModel.get_all_recommendations()

        best_match = None
        best_match_length = 0

        for rec in recommendations:
            rec_order = rec.get('temperaments_orders', [])
            if isinstance(rec_order, str):
                try:
                    rec_order = json.loads(rec_order)
                except Exception:
                    rec_order = []

            # Normalize to unique int IDs preserving order
            normalized = []
            seen = set()
            for tid in rec_order or []:
                try:
                    tid_int = int(tid)
                except (TypeError, ValueError):
                    continue
                if tid_int not in seen:
                    normalized.append(tid_int)
                    seen.add(tid_int)

            if not normalized:
                continue

            # Require full match for the configured order length.
            if len(person_ranking) < len(normalized):
                continue

            if person_ranking[:len(normalized)] == normalized[:len(normalized)]:
                # Prefer the most specific (longer) match
                if len(normalized) > best_match_length:
                    best_match = rec
                    best_match_length = len(normalized)

        return best_match