`p_main_category_code` INT, `p_sub_category_code` INT

varchar(20) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci

BEGIN
    DECLARE next_serial INT DEFAULT 1;
    DECLARE submission_code VARCHAR(20);
    DECLARE category_pattern VARCHAR(10);
    
    SET category_pattern = CONCAT(p_main_category_code, '-', p_sub_category_code, '-%');
    
    SELECT COALESCE(MAX(CAST(SUBSTRING_INDEX(Submission_code, '-', -1) AS UNSIGNED)), 0) + 1
    INTO next_serial
    FROM reviewcontent 
    WHERE Submission_code LIKE category_pattern
    AND YEAR(SubmissionDate) = YEAR(NOW());
    
    SET submission_code = CONCAT(p_main_category_code, '-', p_sub_category_code, '-', LPAD(next_serial, 3, '0'));
    
    RETURN submission_code;
END

IN `days_back` INT

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    SELECT 
        'Login Statistics' as report_section,
        COUNT(*) as total_attempts,
        SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) as successful_logins,
        SUM(CASE WHEN status LIKE 'FAILED%' THEN 1 ELSE 0 END) as failed_attempts,
        COUNT(DISTINCT ip_address) as unique_ips
    FROM login_attempts 
    WHERE attempt_time >= DATE_SUB(NOW(), INTERVAL days_back DAY)
    
    UNION ALL
    
    SELECT 
        'Security Events' as report_section,
        COUNT(*) as total_events,
        SUM(CASE WHEN severity = 'HIGH' OR severity = 'CRITICAL' THEN 1 ELSE 0 END) as critical_events,
        SUM(CASE WHEN resolved = 0 THEN 1 ELSE 0 END) as unresolved_events,
        COUNT(DISTINCT source_ip) as unique_source_ips
    FROM security_events 
    WHERE event_time >= DATE_SUB(NOW(), INTERVAL days_back DAY);
END