-- AI assistant for the support desk (Settings → AI assistant): work queue, suggestions for staff, usage log
CREATE TABLE ai_jobs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT UNSIGNED NOT NULL,
  reply_id INT UNSIGNED NULL COMMENT 'the client message to work on',
  first TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = a new ticket (triage)',
  status VARCHAR(10) NOT NULL DEFAULT 'new' COMMENT 'new, done, skipped, failed',
  note VARCHAR(255) NOT NULL DEFAULT '',
  run_after DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  done_at DATETIME NULL,
  KEY queue (status, run_after),
  KEY ticket (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE ai_suggestions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT UNSIGNED NOT NULL,
  reply_id INT UNSIGNED NULL,
  kind VARCHAR(20) NOT NULL COMMENT 'reply, summary, triage, translation',
  content MEDIUMTEXT NOT NULL,
  data TEXT NULL COMMENT 'JSON: confidence, reason, department, priority, flags …',
  status VARCHAR(10) NOT NULL DEFAULT 'new' COMMENT 'new, posted, used, dismissed',
  admin_id INT UNSIGNED NULL COMMENT 'staff member who asked for it (NULL = automatic)',
  created_at DATETIME NOT NULL,
  KEY ticket (ticket_id, kind)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE ai_log (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT UNSIGNED NULL,
  admin_id INT UNSIGNED NULL,
  task VARCHAR(20) NOT NULL,
  provider VARCHAR(20) NOT NULL,
  model VARCHAR(100) NOT NULL,
  tokens_in INT UNSIGNED NOT NULL DEFAULT 0,
  tokens_out INT UNSIGNED NOT NULL DEFAULT 0,
  cost DECIMAL(12,6) NOT NULL DEFAULT 0,
  ms INT UNSIGNED NOT NULL DEFAULT 0,
  ok TINYINT(1) NOT NULL DEFAULT 1,
  error VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  KEY created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE ticket_replies ADD by_ai TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'written and sent by the AI assistant' AFTER by_staff;