-- Daily automation counters (dashboard "Automation overview") and indexes for the reports.
CREATE TABLE automation_log (
  day DATE NOT NULL,
  metric VARCHAR(40) NOT NULL,
  count INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (day, metric)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE transactions ADD KEY created (created_at);
ALTER TABLE ticket_replies ADD KEY created_admin (created_at, admin_id);
ALTER TABLE tickets ADD KEY created (created_at);

-- gateway log: incoming notifications (in) and our API calls to the gateways (out)
ALTER TABLE gateway_log ADD kind VARCHAR(4) NOT NULL DEFAULT 'in' AFTER gateway, ADD KEY created (created_at);