from models.database import execute_query, execute_single_query, execute_update_query

class QuestionModel:
    @staticmethod
    def get_all_questions():
        query = """
        SELECT q.*, f.name as feature_name, s.name as survey_name, o.position as option_position, o.name as option_name
        FROM questions q 
        LEFT JOIN features f ON q.feature_id = f.id 
        LEFT JOIN surveys s ON f.survey_id = s.id 
        LEFT JOIN options o ON q.option_id = o.id
        ORDER BY q.created_at DESC
        """
        return execute_query(query)
    
    @staticmethod
    def get_question_by_id(question_id):
        query = """
        SELECT q.*, f.name as feature_name, s.name as survey_name, o.position as option_position, o.name as option_name
        FROM questions q 
        LEFT JOIN features f ON q.feature_id = f.id 
        LEFT JOIN surveys s ON f.survey_id = s.id 
        LEFT JOIN options o ON q.option_id = o.id
        WHERE q.id = %s
        """
        return execute_single_query(query, (question_id,))
    
    @staticmethod
    def get_questions_by_feature(feature_id):
        query = """
        SELECT q.*, f.name as feature_name, s.name as survey_name,
               o.position as option_position, o.name as option_name,
               t.name as temperament_name, t.color as temperament_color
        FROM questions q 
        LEFT JOIN features f ON q.feature_id = f.id 
        LEFT JOIN surveys s ON f.survey_id = s.id 
        LEFT JOIN options o ON q.option_id = o.id
        LEFT JOIN temperaments t ON o.temperament_id = t.id
        WHERE q.feature_id = %s 
        ORDER BY (o.position IS NULL) ASC, o.position ASC, q.id ASC
        """
        return execute_query(query, (feature_id,))
    
    @staticmethod
    def create_question(name, feature_id, option_id=None):
        query = "INSERT INTO questions (name, feature_id, option_id) VALUES (%s, %s, %s)"
        return execute_update_query(query, (name, feature_id, option_id))
    
    @staticmethod
    def update_question(question_id, name, feature_id, option_id=None):
        query = "UPDATE questions SET name = %s, feature_id = %s, option_id = %s WHERE id = %s"
        return execute_update_query(query, (name, feature_id, option_id, question_id))
    
    @staticmethod
    def delete_question(question_id):
        query = "DELETE FROM questions WHERE id = %s"
        return execute_update_query(query, (question_id,))
    
    @staticmethod
    def get_questions_count():
        query = "SELECT COUNT(*) as count FROM questions"
        result = execute_single_query(query)
        return result['count'] if result else 0