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

class PeopleModel:
    @staticmethod
    def get_all_people():
        query = """
        SELECT p.*, pr.name as project_name 
        FROM peoples p 
        LEFT JOIN projects pr ON p.project_id = pr.id 
        ORDER BY p.created_at DESC
        """
        return execute_query(query)
    
    @staticmethod
    def get_person_by_id(person_id):
        query = """
        SELECT p.*, pr.name as project_name 
        FROM peoples p 
        LEFT JOIN projects pr ON p.project_id = pr.id 
        WHERE p.id = %s
        """
        return execute_single_query(query, (person_id,))
    
    @staticmethod
    def get_people_by_project(project_id):
        query = """
        SELECT p.*, pr.name as project_name 
        FROM peoples p 
        LEFT JOIN projects pr ON p.project_id = pr.id 
        WHERE p.project_id = %s 
        ORDER BY p.name ASC
        """
        return execute_query(query, (project_id,))
    
    @staticmethod
    def create_person(name, project_id, phone, email):
        query = "INSERT INTO peoples (name, project_id, phone, email) VALUES (%s, %s, %s, %s)"
        return execute_insert_query(query, (name, project_id, phone, email))
    
    @staticmethod
    def update_person(person_id, name, project_id, phone, email):
        query = "UPDATE peoples SET name = %s, project_id = %s, phone = %s, email = %s WHERE id = %s"
        return execute_update_query(query, (name, project_id, phone, email, person_id))
    
    @staticmethod
    def delete_person(person_id):
        query = "DELETE FROM peoples WHERE id = %s"
        return execute_update_query(query, (person_id,))

    @staticmethod
    def get_person_answers_count(person_id):
        """Get the count of answers submitted by a person"""
        query = "SELECT COUNT(*) as count FROM answers WHERE people_id = %s"
        result = execute_single_query(query, (person_id,))
        return result['count'] if result else 0

    @staticmethod
    def delete_person_with_answers(person_id):
        """Delete person and all their associated answers"""
        # First delete all answers
        delete_answers_query = "DELETE FROM answers WHERE people_id = %s"
        execute_update_query(delete_answers_query, (person_id,))

        # Then delete the person
        delete_person_query = "DELETE FROM peoples WHERE id = %s"
        return execute_update_query(delete_person_query, (person_id,))
    
    @staticmethod
    def get_people_count():
        query = "SELECT COUNT(*) as count FROM peoples"
        result = execute_single_query(query)
        return result['count'] if result else 0
    
    @staticmethod
    def email_exists_in_project(email, project_id):
        """Check if email already exists for a specific project"""
        query = "SELECT COUNT(*) as count FROM peoples WHERE email = %s AND project_id = %s"
        result = execute_single_query(query, (email, project_id))
        return result['count'] > 0 if result else False

    @staticmethod
    def get_person_by_email_and_project(email, project_id):
        """Get person by email and project - used to prevent duplicates"""
        query = """
        SELECT p.*, pr.name as project_name
        FROM peoples p
        LEFT JOIN projects pr ON p.project_id = pr.id
        WHERE p.email = %s AND p.project_id = %s
        LIMIT 1
        """
        return execute_single_query(query, (email, project_id))