from models.database import execute_query, execute_single_query, execute_update_query

class FeatureModel:
    @staticmethod
    def get_all_features():
        query = """
        SELECT f.*, s.name as survey_name 
        FROM features f 
        LEFT JOIN surveys s ON f.survey_id = s.id 
        ORDER BY f.position ASC, f.created_at DESC
        """
        return execute_query(query)
    
    @staticmethod
    def get_feature_by_id(feature_id):
        query = """
        SELECT f.*, s.name as survey_name 
        FROM features f 
        LEFT JOIN surveys s ON f.survey_id = s.id 
        WHERE f.id = %s
        """
        return execute_single_query(query, (feature_id,))
    
    @staticmethod
    def get_features_by_survey(survey_id):
        query = """
        SELECT f.*, s.name as survey_name 
        FROM features f 
        LEFT JOIN surveys s ON f.survey_id = s.id 
        WHERE f.survey_id = %s 
        ORDER BY f.position ASC
        """
        return execute_query(query, (survey_id,))
    
    @staticmethod
    def create_feature(survey_id, name, position):
        query = "INSERT INTO features (survey_id, name, position) VALUES (%s, %s, %s)"
        return execute_update_query(query, (survey_id, name, position))
    
    @staticmethod
    def update_feature(feature_id, survey_id, name, position):
        query = "UPDATE features SET survey_id = %s, name = %s, position = %s WHERE id = %s"
        return execute_update_query(query, (survey_id, name, position, feature_id))
    
    @staticmethod
    def delete_feature(feature_id):
        query = "DELETE FROM features WHERE id = %s"
        return execute_update_query(query, (feature_id,))
    
    @staticmethod
    def get_features_count():
        query = "SELECT COUNT(*) as count FROM features"
        result = execute_single_query(query)
        return result['count'] if result else 0
    
    @staticmethod
    def get_feature_questions_count(feature_id):
        query = "SELECT COUNT(*) as count FROM questions WHERE feature_id = %s"
        result = execute_single_query(query, (feature_id,))
        return result['count'] if result else 0