-- Roles with fine-grained permissions, and the root password vault.

UPDATE admin_roles SET perms = '["*"]' WHERE id = 1;
UPDATE admin_roles SET name = 'Support', perms = '["tickets.view","tickets.reply","tickets.notes","tickets.manage","clients.view","vault.reveal"]' WHERE id = 2;
INSERT INTO admin_roles (id, name, perms) VALUES
 (3, 'Billing', '["tickets.view","tickets.reply","tickets.notes","clients.view","clients.edit","billing.view","billing.manage"]'),
 (4, 'Developer', '["tickets.view","tickets.reply","tickets.notes","tickets.manage","vault.reveal"]');

-- A secret sent by a customer for one ticket. The plaintext is never stored: only per-agent envelopes.
CREATE TABLE vault_secrets (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT UNSIGNED NOT NULL,
  client_id INT UNSIGNED NULL,
  user_id INT UNSIGNED NULL,
  label VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'not secret, e.g. "Server access"',
  created_at DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  KEY ticket (ticket_id),
  KEY expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The secret encrypted for one agent: ephemeral ECDH P-256 public key, AES-256-GCM iv and ciphertext+tag.
CREATE TABLE vault_envelopes (
  secret_id INT UNSIGNED NOT NULL,
  admin_id INT UNSIGNED NOT NULL,
  epk VARCHAR(100) NOT NULL,
  iv VARCHAR(32) NOT NULL,
  ct TEXT NOT NULL,
  PRIMARY KEY (secret_id, admin_id),
  KEY admin (admin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Every time an agent fetched a secret; shown to the customer on the ticket.
CREATE TABLE vault_access (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  secret_id INT UNSIGNED NOT NULL,
  ticket_id INT UNSIGNED NOT NULL,
  admin_id INT UNSIGNED NOT NULL,
  ip VARCHAR(64) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  KEY secret (secret_id),
  KEY ticket (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;