-- AUDIT-2 #6 (2026-05-24): replace the AuditLog BEFORE INSERT trigger
-- to use a length-prefixed canonicalisation, defeating the theoretical
-- CONCAT_WS('|', ...) collision where two semantically-different field
-- sets produce the same canonical string.
--
-- Listed in audit-2-followups.md as "very low (no realistic exploit)" —
-- the trigger + row-level locks already make a forgery near-impossible.
-- This is defence in depth.
--
-- Scheme:
--   rowHash = SHA2(CONCAT('v2:',
--                         LPAD(LENGTH(prevHash), 8, '0'), prevHash,
--                         LPAD(LENGTH(userId),  8, '0'), userId,
--                         ...
--                        ), 256)
--
-- The `v2:` sentinel makes the canonical strings of the old and new
-- schemes non-overlapping, so AuditService.verifyChain (which tries
-- both v2 and v1 per row) can never accidentally accept a v1 hash on
-- v2-canonicalised input or vice versa.
--
-- Existing rows keep their v1 hashes — verifyChain falls back to v1
-- when v2 doesn't match, so the chain stays continuous across the
-- migration boundary.

DROP TRIGGER IF EXISTS `audit_log_chain_insert`;

CREATE TRIGGER `audit_log_chain_insert`
BEFORE INSERT ON `AuditLog`
FOR EACH ROW
SET
  NEW.createdAt = CURRENT_TIMESTAMP(6),
  NEW.prevHash = IFNULL(
    (SELECT rowHash FROM `AuditLog` WHERE rowHash IS NOT NULL ORDER BY id DESC LIMIT 1),
    REPEAT('0', 64)
  ),
  NEW.rowHash = SHA2(CONCAT(
    'v2:',
    LPAD(LENGTH(NEW.prevHash), 8, '0'), NEW.prevHash,
    LPAD(LENGTH(CAST(NEW.userId AS CHAR)), 8, '0'), CAST(NEW.userId AS CHAR),
    LPAD(LENGTH(NEW.action), 8, '0'), NEW.action,
    LPAD(LENGTH(NEW.resource), 8, '0'), NEW.resource,
    LPAD(LENGTH(IFNULL(NEW.resourceId, '')), 8, '0'), IFNULL(NEW.resourceId, ''),
    LPAD(LENGTH(IFNULL(NEW.details, '')), 8, '0'), IFNULL(NEW.details, ''),
    LPAD(LENGTH(CAST(UNIX_TIMESTAMP(NEW.createdAt) AS CHAR)), 8, '0'), CAST(UNIX_TIMESTAMP(NEW.createdAt) AS CHAR)
  ), 256);