CREATE TABLE IF NOT EXISTS automation_rules (
  id CHAR(26) PRIMARY KEY,
  account_id CHAR(26) NOT NULL,
  channel_id CHAR(26) NOT NULL,
  name VARCHAR(190) NOT NULL,
  match_type ENUM('any','exact','contains','starts_with') NOT NULL DEFAULT 'contains',
  match_value VARCHAR(500) NULL,
  response_type VARCHAR(40) NOT NULL DEFAULT 'text',
  response_payload_json LONGTEXT NOT NULL,
  priority INT NOT NULL DEFAULT 100,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_automation_channel_enabled (channel_id, enabled, priority),
  KEY idx_automation_account (account_id),
  CONSTRAINT fk_automation_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
  CONSTRAINT fk_automation_channel FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;