import json
from models.database import execute_query, execute_single_query, execute_update_query


class RecommendationModel:
    @staticmethod
    def _deserialize_temperaments_orders(record):
        if record is None:
            return None
        orders = record.get('temperaments_orders') if record else None
        if isinstance(orders, str):
            try:
                record['temperaments_orders'] = json.loads(orders)
            except json.JSONDecodeError:
                record['temperaments_orders'] = []
        elif orders is None:
            record['temperaments_orders'] = []
        return record

    @staticmethod
    def _prepare_orders(temperaments_orders):
        temperaments_orders = temperaments_orders or []
        unique_ids = []
        for temperament_id in temperaments_orders:
            try:
                tid = int(temperament_id)
            except (TypeError, ValueError):
                continue
            if tid not in unique_ids:
                unique_ids.append(tid)
        return json.dumps(unique_ids)

    @staticmethod
    def get_all_recommendations():
        query = "SELECT * FROM recommendations ORDER BY created_at DESC"
        records = execute_query(query)
        return [RecommendationModel._deserialize_temperaments_orders(record) for record in records]

    @staticmethod
    def get_recommendation_by_id(recommendation_id):
        query = "SELECT * FROM recommendations WHERE id = %s"
        record = execute_single_query(query, (recommendation_id,))
        return RecommendationModel._deserialize_temperaments_orders(record)

    @staticmethod
    def create_recommendation(name, description, temperaments_orders):
        orders_json = RecommendationModel._prepare_orders(temperaments_orders)
        query = (
            "INSERT INTO recommendations (name, description, temperaments_orders) "
            "VALUES (%s, %s, %s)"
        )
        return execute_update_query(query, (name, description, orders_json))

    @staticmethod
    def update_recommendation(recommendation_id, name, description, temperaments_orders):
        orders_json = RecommendationModel._prepare_orders(temperaments_orders)
        query = (
            "UPDATE recommendations SET name = %s, description = %s, temperaments_orders = %s "
            "WHERE id = %s"
        )
        return execute_update_query(query, (name, description, orders_json, recommendation_id))

    @staticmethod
    def delete_recommendation(recommendation_id):
        query = "DELETE FROM recommendations WHERE id = %s"
        return execute_update_query(query, (recommendation_id,))

    @staticmethod
    def get_recommendations_count():
        query = "SELECT COUNT(*) as count FROM recommendations"
        result = execute_single_query(query)
        return result['count'] if result else 0