-- EA-1 / PR-32: tamper-evident AuditLog via MySQL triggers + sha256 hash chain
-- =========================================================
-- Adds prevHash + rowHash columns (set by trigger), a unique constraint on
-- prevHash (so concurrent-insert races fail the second with P2002 which the
-- application retries), and three triggers:
--   - BEFORE INSERT: forces createdAt = NOW(6), reads previous row's rowHash,
--     computes new row's prevHash + rowHash
--   - BEFORE UPDATE: SIGNAL 45000 — AuditLog is append-only
--   - BEFORE DELETE: SIGNAL 45000 — AuditLog is append-only
--
-- NOT backfilled: pre-existing rows keep NULL chain columns. They are
-- excluded from AuditService.verifyChain() but still cannot be UPDATEd
-- or DELETEd. The chain starts from the first INSERT after this migration.
-- See docs/runbooks/audit-log-tamper-evidence.md for retroactive-backfill
-- procedure (maintenance window required to temporarily drop the triggers).

-- ============================================================
-- UP
-- ============================================================

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)';

-- ============================================================
-- DOWN
-- ============================================================

DROP TRIGGER IF EXISTS `audit_log_no_delete`;
DROP TRIGGER IF EXISTS `audit_log_no_update`;
DROP TRIGGER IF EXISTS `audit_log_chain_insert`;
ALTER TABLE `AuditLog` DROP INDEX `AuditLog_prevHash_unique`;
ALTER TABLE `AuditLog` DROP COLUMN `rowHash`;
ALTER TABLE `AuditLog` DROP COLUMN `prevHash`;