from models.database import execute_query, execute_single_query, execute_update_query

class ProjectModel:
    @staticmethod
    def get_all_projects():
        query = """
        SELECT p.*, s.name as survey_name 
        FROM projects p 
        LEFT JOIN surveys s ON p.survey_id = s.id 
        ORDER BY p.created_at DESC
        """
        return execute_query(query)
    
    @staticmethod
    def get_project_by_id(project_id):
        query = """
        SELECT p.*, s.name as survey_name 
        FROM projects p 
        LEFT JOIN surveys s ON p.survey_id = s.id 
        WHERE p.id = %s
        """
        return execute_single_query(query, (project_id,))
    
    @staticmethod
    def create_project(name, slug, survey_id, start_date, end_date, show_results='show_by_admin'):
        query = "INSERT INTO projects (name, slug, survey_id, start_date, end_date, show_results) VALUES (%s, %s, %s, %s, %s, %s)"
        return execute_update_query(query, (name, slug, survey_id, start_date, end_date, show_results))
    
    @staticmethod
    def update_project(project_id, name, slug, survey_id, start_date, end_date, show_results='show_by_admin'):
        query = "UPDATE projects SET name = %s, slug = %s, survey_id = %s, start_date = %s, end_date = %s, show_results = %s WHERE id = %s"
        return execute_update_query(query, (name, slug, survey_id, start_date, end_date, show_results, project_id))
    
    @staticmethod
    def delete_project(project_id):
        query = "DELETE FROM projects WHERE id = %s"
        return execute_update_query(query, (project_id,))
    
    @staticmethod
    def get_projects_count():
        query = "SELECT COUNT(*) as count FROM projects"
        result = execute_single_query(query)
        return result['count'] if result else 0
    
    @staticmethod
    def get_project_by_slug(slug):
        query = """
        SELECT p.*, s.name as survey_name 
        FROM projects p 
        LEFT JOIN surveys s ON p.survey_id = s.id 
        WHERE p.slug = %s
        """
        return execute_single_query(query, (slug,))