-- New columns on billing_communications
ALTER TABLE billing_communications
  ADD COLUMN email_message_id VARCHAR(255) DEFAULT NULL AFTER email_error,
  ADD COLUMN email_retry_count TINYINT DEFAULT 0 AFTER email_message_id,
  ADD COLUMN sent_by INT DEFAULT NULL AFTER created_at,
  ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

-- Centralized audit log
CREATE TABLE IF NOT EXISTS audit_log (
  id          INT NOT NULL AUTO_INCREMENT,
  entity_type VARCHAR(50) NOT NULL,
  entity_id   INT NOT NULL,
  action      VARCHAR(50) NOT NULL,
  actor_id    INT DEFAULT NULL,
  payload     JSON DEFAULT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_entity (entity_type, entity_id),
  INDEX idx_actor (actor_id),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
