-- EA-1 / PR-32: tamper-evident AuditLog via MySQL triggers + sha256 hash chain

ALTER TABLE `AuditLog`
  ADD COLUMN `prevHash` CHAR(64) NULL,
  ADD COLUMN `rowHash`  CHAR(64) NULL;

ALTER TABLE `AuditLog`
  ADD UNIQUE INDEX `AuditLog_prevHash_unique` (`prevHash`);

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_WS('|',
    NEW.prevHash,
    NEW.userId,
    NEW.action,
    NEW.resource,
    IFNULL(NEW.resourceId, ''),
    IFNULL(NEW.details, ''),
    UNIX_TIMESTAMP(NEW.createdAt)
  ), 256);

CREATE TRIGGER `audit_log_no_update`
BEFORE UPDATE ON `AuditLog`
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'AuditLog is append-only (PR-32)';

CREATE TRIGGER `audit_log_no_delete`
BEFORE DELETE ON `AuditLog`
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'AuditLog is append-only (PR-32)';