CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_audit_log`(IN `start_date` DATE, IN `end_date` DATE, IN `user_filter` INT, IN `event_filter` VARCHAR(20))
BEGIN
-- User activity / audit log: who created, edited or deleted what, and when —
-- the record and the fields changed (newest first, up to 2000 rows).
SELECT DATE_FORMAT(a.created_at, '%d %b, %Y %H:%i') AS `Date`, COALESCE(u.name, 'System') AS `User`,
       CASE a.event WHEN 'deleted' THEN '<span style="color:#b42318;font-weight:600">Deleted</span>' WHEN 'updated' THEN 'Edited' ELSE 'Created' END AS `Action`,
       CONCAT(SUBSTRING_INDEX(a.auditable_type, '\\', -1), ' #', a.auditable_id) AS `Record`,
       LEFT(CASE a.event WHEN 'created' THEN '' ELSE COALESCE(REPLACE(REPLACE(REPLACE(a.old_values, '{', ''), '}', ''), '"', ''), '') END, 220) AS `Before`,
       LEFT(CASE a.event WHEN 'deleted' THEN '' ELSE COALESCE(REPLACE(REPLACE(REPLACE(a.new_values, '{', ''), '}', ''), '"', ''), '') END, 220) AS `After`,
       COALESCE(a.ip_address, '') AS `IP`
FROM audits a
LEFT JOIN users u ON u.id = a.user_id
WHERE DATE(a.created_at) BETWEEN start_date AND end_date
  AND (user_filter = 0 OR a.user_id = user_filter)
  AND (event_filter = '0' OR event_filter = '' OR a.event = CONVERT(event_filter USING utf8mb4) COLLATE utf8mb4_unicode_ci)
ORDER BY a.id DESC
LIMIT 2000;
END
