-- Optional but recommended defense-in-depth. Requires the TRIGGER privilege (use the migration/admin DB user,
-- not the runtime app user). Each trigger body is a single statement so this file imports without DELIMITER.

-- Audit records are append-only.
CREATE TRIGGER trg_audit_no_update BEFORE UPDATE ON audit_logs FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit_logs is append-only';
CREATE TRIGGER trg_audit_no_delete BEFORE DELETE ON audit_logs FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit_logs is append-only';

-- Financial records can never be deleted (retain / anonymise instead).
CREATE TRIGGER trg_orders_no_delete BEFORE DELETE ON payment_orders FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'financial records cannot be deleted';
CREATE TRIGGER trg_ptx_no_delete BEFORE DELETE ON payment_transactions FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'financial records cannot be deleted';
CREATE TRIGGER trg_tokens_no_delete BEFORE DELETE ON serial_tokens FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'financial records cannot be deleted';
CREATE TRIGGER trg_funding_no_delete BEFORE DELETE ON funding_transactions FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'financial records cannot be deleted';
CREATE TRIGGER trg_attempts_no_delete BEFORE DELETE ON funding_attempts FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'financial records cannot be deleted';

-- A funding transaction's amount and recipient are immutable, and a succeeded transaction can never change status.
CREATE TRIGGER trg_funding_immutable BEFORE UPDATE ON funding_transactions FOR EACH ROW
  SET NEW.amount_usd = OLD.amount_usd,
      NEW.serial_token_id = OLD.serial_token_id,
      NEW.visitor_id = OLD.visitor_id,
      NEW.recipient_ref = OLD.recipient_ref,
      NEW.status = IF(OLD.status = 'succeeded', 'succeeded', NEW.status);

-- The price / amount / address quoted to the visitor can never be rewritten after the order exists.
CREATE TRIGGER trg_orders_price_immutable BEFORE UPDATE ON payment_orders FOR EACH ROW
  SET NEW.usd_price = OLD.usd_price,
      NEW.funding_amount_usd = OLD.funding_amount_usd,
      NEW.expected_amount = OLD.expected_amount,
      NEW.receiving_address = OLD.receiving_address;

-- An issued token's authorized amount and hash never change.
CREATE TRIGGER trg_tokens_immutable BEFORE UPDATE ON serial_tokens FOR EACH ROW
  SET NEW.funding_amount_usd = OLD.funding_amount_usd,
      NEW.token_hash = OLD.token_hash,
      NEW.package_id = OLD.package_id;