DELIMITER $$

CREATE PROCEDURE `sp_get_group_audit_logs`(
    IN p_search_term VARCHAR(100),      -- 검색어 (NULL 가능)
    IN p_start_date DATE,             -- 시작일 (NULL 가능)
    IN p_end_date DATE,               -- 종료일 (NULL 가능)
    IN p_page_num INT,                -- 페이지 번호 (기본값 1)
    IN p_page_size INT                 -- 페이지 크기 (기본값 20)
)
  COMMENT '그룹(부서) 관련 감사 로그 통합 조회 (검색, 기간, 페이징)'
BEGIN
    DECLARE v_offset INT;
    SET @v_search_like = NULL;

    -- 입력값 기본 처리
    IF p_search_term IS NOT NULL AND p_search_term != '' THEN
        SET @v_search_like = CONCAT('%', p_search_term, '%');
    END IF;
    IF p_page_num IS NULL OR p_page_num < 1 THEN SET p_page_num = 1; END IF;
    IF p_page_size IS NULL OR p_page_size < 1 THEN SET p_page_size = 20; END IF;
    SET v_offset = (p_page_num - 1) * p_page_size;

    -- 날짜 범위 조건 문자열 동적 생성
    SET @v_date_condition = '';
    IF p_start_date IS NOT NULL THEN
        SET @v_date_condition = CONCAT('AND l.log_date >= DATE(''', p_start_date, ''') ');
    END IF;
    IF p_end_date IS NOT NULL THEN
        SET @v_date_condition = CONCAT(@v_date_condition, 'AND l.log_date < DATE_ADD(DATE(''', p_end_date, '''), INTERVAL 1 DAY) ');
    END IF;

    -- 검색 조건 문자열 동적 생성 (통합 결과셋 대상)
    SET @v_search_condition = '';
    IF @v_search_like IS NOT NULL THEN
        SET @v_search_condition = CONCAT(
            'WHERE (',
            '  actor_info LIKE @v_search_like ',          -- 작업자 정보
            '  OR CAST(target_id AS CHAR) LIKE @v_search_like ', -- 대상 그룹 ID
            '  OR target_info_at_log LIKE @v_search_like ', -- 대상 그룹 정보 (이름)
            '  OR details LIKE @v_search_like',           -- 상세 내용
            ') '
        );
    ELSE
        SET @v_search_condition = 'WHERE 1=1 ';
    END IF;

    -- 최종 동적 SQL 구성
    SET @v_sql = CONCAT('
        SELECT SQL_CALC_FOUND_ROWS * FROM (
            -- 그룹 추가 로그
            SELECT
                \'ADD\' AS log_type, l.log_id, l.log_date,
                COALESCE(au.username, l.actor_description, \'System/Unknown\') AS actor_info,
                l.group_id AS target_id,
                l.group_name AS target_info_at_log, -- 추가 시점 그룹 이름
                CONCAT(\'그룹 추가: \', l.group_name, IF(l.manager_user_id IS NULL, \'\', CONCAT(\' (매니저 ID: \', l.manager_user_id, \')\'))) AS details
            FROM log_add_group l LEFT JOIN auth_user au ON l.admin_user_id = au.id
            WHERE 1=1 ', @v_date_condition, '

            UNION ALL

            -- 그룹 수정 로그
            SELECT
                \'UPDATE\' AS log_type, l.log_id, l.log_date,
                COALESCE(au.username, l.actor_description, \'System/Unknown\') AS actor_info,
                l.group_id AS target_id,
                l.new_group_name AS target_info_at_log, -- 수정 후 그룹 이름
                CONCAT(\'그룹 수정: \', l.new_group_name, \'. \',
                    IF(l.old_group_name <=> l.new_group_name, \'\', CONCAT(\'이름 변경: \', COALESCE(l.old_group_name, \'없음\'), \'->\', l.new_group_name, \'. \')),
                    IF(l.old_manager_user_id <=> l.new_manager_user_id, \'\', CONCAT(\'매니저 변경: \', COALESCE(l.old_manager_user_id, \'미지정\'), \'->\', COALESCE(l.new_manager_user_id, \'미지정\'), \'. \'))
                ) AS details
            FROM log_update_group l LEFT JOIN auth_user au ON l.admin_user_id = au.id
            WHERE 1=1 ', @v_date_condition, '

            UNION ALL

            -- 그룹 삭제 로그
            SELECT
                \'DELETE\' AS log_type, l.log_id, l.log_date,
                COALESCE(au.username, l.actor_description, \'System/Unknown\') AS actor_info,
                l.group_id AS target_id,
                l.group_name AS target_info_at_log, -- 삭제 시점 그룹 이름
                CONCAT(\'그룹 삭제: \', l.group_name) AS details
            FROM log_delete_group l LEFT JOIN auth_user au ON l.admin_user_id = au.id
            WHERE 1=1 ', @v_date_condition, '
        ) AS combined_logs
        ', @v_search_condition, '
        ORDER BY log_date DESC
        LIMIT ? OFFSET ?
    ');

    -- SQL 문 실행
    PREPARE stmt FROM @v_sql;
    EXECUTE stmt USING p_page_size, v_offset;

    -- 전체 결과 수 계산 값 반환
    SELECT FOUND_ROWS() AS total_count;

    DEALLOCATE PREPARE stmt;
END
$$

DELIMITER ;