from models.database import execute_query, execute_single_query, execute_update_query

class OptionModel:
    @staticmethod
    def get_all_options():
        query = """
        SELECT o.*, t.name as temperament_name, t.color as temperament_color 
        FROM options o 
        LEFT JOIN temperaments t ON o.temperament_id = t.id 
        ORDER BY o.position ASC, o.created_at DESC
        """
        return execute_query(query)
    
    @staticmethod
    def get_option_by_id(option_id):
        query = """
        SELECT o.*, t.name as temperament_name, t.color as temperament_color 
        FROM options o 
        LEFT JOIN temperaments t ON o.temperament_id = t.id 
        WHERE o.id = %s
        """
        return execute_single_query(query, (option_id,))
    
    @staticmethod
    def get_options_by_temperament(temperament_id):
        query = """
        SELECT o.*, t.name as temperament_name, t.color as temperament_color 
        FROM options o 
        LEFT JOIN temperaments t ON o.temperament_id = t.id 
        WHERE o.temperament_id = %s 
        ORDER BY o.position ASC
        """
        return execute_query(query, (temperament_id,))
    
    @staticmethod
    def create_option(name, position, temperament_id):
        query = "INSERT INTO options (name, position, temperament_id) VALUES (%s, %s, %s)"
        return execute_update_query(query, (name, position, temperament_id))
    
    @staticmethod
    def update_option(option_id, name, position, temperament_id):
        query = "UPDATE options SET name = %s, position = %s, temperament_id = %s WHERE id = %s"
        return execute_update_query(query, (name, position, temperament_id, option_id))
    
    @staticmethod
    def delete_option(option_id):
        query = "DELETE FROM options WHERE id = %s"
        return execute_update_query(query, (option_id,))
    
    @staticmethod
    def get_options_count():
        query = "SELECT COUNT(*) as count FROM options"
        result = execute_single_query(query)
        return result['count'] if result else 0