-- Core: settings, admins, clients, users, tickets, audit. Ids of clients, tickets and departments
-- are kept identical to WHMCS on import, so old links and references keep working.

CREATE TABLE settings (
  k VARCHAR(64) NOT NULL PRIMARY KEY,
  v MEDIUMTEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE admin_roles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64) NOT NULL,
  perms TEXT NOT NULL COMMENT 'JSON list of permission keys, ["*"] = everything'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE admins (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  role_id INT UNSIGNED NOT NULL DEFAULT 1,
  username VARCHAR(64) NOT NULL,
  email VARCHAR(190) NOT NULL DEFAULT '',
  firstname VARCHAR(100) NOT NULL DEFAULT '',
  lastname VARCHAR(100) NOT NULL DEFAULT '',
  password VARCHAR(255) NOT NULL DEFAULT '',
  totp_secret VARCHAR(64) NOT NULL DEFAULT '',
  totp_last BIGINT NOT NULL DEFAULT 0 COMMENT 'last accepted TOTP time step (replay protection)',
  signature TEXT NULL,
  departments VARCHAR(500) NOT NULL DEFAULT '' COMMENT 'comma separated department ids',
  disabled TINYINT(1) NOT NULL DEFAULT 0,
  vault_pub TEXT NULL COMMENT 'P-256 public key, SPKI base64',
  vault_priv_wrapped TEXT NULL COMMENT 'private key wrapped in the browser with the vault passphrase',
  last_login_at DATETIME NULL,
  last_login_ip VARCHAR(64) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE clients (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(100) NOT NULL DEFAULT '',
  lastname VARCHAR(100) NOT NULL DEFAULT '',
  companyname VARCHAR(190) NOT NULL DEFAULT '',
  email VARCHAR(190) NOT NULL DEFAULT '',
  address1 VARCHAR(190) NOT NULL DEFAULT '',
  address2 VARCHAR(190) NOT NULL DEFAULT '',
  city VARCHAR(100) NOT NULL DEFAULT '',
  state VARCHAR(100) NOT NULL DEFAULT '',
  postcode VARCHAR(32) NOT NULL DEFAULT '',
  country CHAR(2) NOT NULL DEFAULT '',
  phonenumber VARCHAR(64) NOT NULL DEFAULT '',
  tax_id VARCHAR(128) NOT NULL DEFAULT '',
  currency_id INT UNSIGNED NOT NULL DEFAULT 1,
  credit DECIMAL(16,2) NOT NULL DEFAULT 0,
  status VARCHAR(16) NOT NULL DEFAULT 'Active',
  language VARCHAR(32) NOT NULL DEFAULT '',
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY email (email),
  KEY status (status),
  FULLTEXT KEY ft_client (firstname, lastname, companyname, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Login identities (WHMCS 8 "users"); one user can manage several client accounts.
CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL,
  password VARCHAR(255) NOT NULL DEFAULT '',
  firstname VARCHAR(100) NOT NULL DEFAULT '',
  lastname VARCHAR(100) NOT NULL DEFAULT '',
  totp_secret VARCHAR(64) NOT NULL DEFAULT '',
  totp_last BIGINT NOT NULL DEFAULT 0,
  reset_token_hash CHAR(64) NOT NULL DEFAULT '',
  reset_expires_at DATETIME NULL,
  email_verified_at DATETIME NULL,
  last_login_at DATETIME NULL,
  last_login_ip VARCHAR(64) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_clients (
  user_id INT UNSIGNED NOT NULL,
  client_id INT UNSIGNED NOT NULL,
  owner TINYINT(1) NOT NULL DEFAULT 0,
  permissions VARCHAR(500) NOT NULL DEFAULT '',
  PRIMARY KEY (user_id, client_id),
  KEY client_id (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ticket_departments (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(500) NOT NULL DEFAULT '',
  email VARCHAR(190) NOT NULL DEFAULT '',
  color CHAR(7) NOT NULL DEFAULT '#64748b',
  hidden TINYINT(1) NOT NULL DEFAULT 0,
  clients_only TINYINT(1) NOT NULL DEFAULT 1,
  sort INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ticket_statuses (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(64) NOT NULL,
  color CHAR(7) NOT NULL DEFAULT '#64748b',
  sort INT NOT NULL DEFAULT 0,
  show_active TINYINT(1) NOT NULL DEFAULT 1,
  show_awaiting TINYINT(1) NOT NULL DEFAULT 0,
  auto_close TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY title (title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tickets (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tid VARCHAR(32) NOT NULL COMMENT 'public ticket number',
  access_key VARCHAR(32) NOT NULL COMMENT 'WHMCS "c": guest access key',
  department_id INT UNSIGNED NOT NULL,
  client_id INT UNSIGNED NULL,
  user_id INT UNSIGNED NULL,
  name VARCHAR(190) NOT NULL DEFAULT '' COMMENT 'requester name when not a client',
  email VARCHAR(190) NOT NULL DEFAULT '',
  cc VARCHAR(500) NOT NULL DEFAULT '',
  subject VARCHAR(250) NOT NULL,
  status VARCHAR(64) NOT NULL,
  priority VARCHAR(16) NOT NULL DEFAULT 'Medium',
  service_id INT UNSIGNED NULL,
  assigned_admin_id INT UNSIGNED NULL,
  flagged TINYINT(1) NOT NULL DEFAULT 0,
  admin_unread TINYINT(1) NOT NULL DEFAULT 1,
  client_unread TINYINT(1) NOT NULL DEFAULT 0,
  merged_into INT UNSIGNED NULL,
  last_reply_at DATETIME NOT NULL,
  last_reply_by VARCHAR(8) NOT NULL DEFAULT 'client' COMMENT 'client|admin',
  ip VARCHAR(64) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY tid (tid),
  KEY status_last (status, last_reply_at),
  KEY dept_status (department_id, status),
  KEY client_id (client_id, last_reply_at),
  KEY email (email),
  KEY assigned (assigned_admin_id, status),
  FULLTEXT KEY ft_subject (subject)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- All messages of a ticket, including the opening one.
CREATE TABLE ticket_replies (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NULL,
  admin_id INT UNSIGNED NULL,
  author_name VARCHAR(190) NOT NULL DEFAULT '',
  author_email VARCHAR(190) NOT NULL DEFAULT '',
  message MEDIUMTEXT NOT NULL,
  rating TINYINT NOT NULL DEFAULT 0,
  whmcs_id INT UNSIGNED NULL COMMENT 'tblticketreplies.id; 0 = opening message from tbltickets',
  created_at DATETIME NOT NULL,
  KEY ticket (ticket_id, id),
  UNIQUE KEY whmcs (whmcs_id, ticket_id),
  FULLTEXT KEY ft_message (message)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ticket_notes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT UNSIGNED NOT NULL,
  admin_id INT UNSIGNED NULL,
  author_name VARCHAR(190) NOT NULL DEFAULT '',
  message MEDIUMTEXT NOT NULL,
  whmcs_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  KEY ticket (ticket_id),
  UNIQUE KEY whmcs (whmcs_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ticket_attachments (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT UNSIGNED NOT NULL,
  reply_id INT UNSIGNED NULL,
  filename VARCHAR(190) NOT NULL,
  stored_name VARCHAR(190) NOT NULL,
  size INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  KEY ticket (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ticket_log (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT UNSIGNED NOT NULL,
  admin_id INT UNSIGNED NULL,
  action VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY ticket (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  actor_type VARCHAR(8) NOT NULL COMMENT 'admin|user|system',
  actor_id INT UNSIGNED NULL,
  action VARCHAR(64) NOT NULL,
  details VARCHAR(1000) NOT NULL DEFAULT '',
  ip VARCHAR(64) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  KEY actor (actor_type, actor_id, id),
  KEY action (action, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rate limiting (login attempts and similar).
CREATE TABLE throttle (
  k VARCHAR(190) NOT NULL PRIMARY KEY,
  hits INT UNSIGNED NOT NULL DEFAULT 0,
  reset_at INT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO admin_roles (id, name, perms) VALUES (1, 'Full Administrator', '["*"]'), (2, 'Support', '["tickets","clients.view"]');

INSERT INTO ticket_statuses (title, color, sort, show_active, show_awaiting, auto_close) VALUES
 ('Open', '#16a34a', 1, 1, 1, 0),
 ('Answered', '#2563eb', 2, 1, 0, 1),
 ('Customer-Reply', '#ea580c', 3, 1, 1, 0),
 ('On Hold', '#7c3aed', 4, 1, 0, 0),
 ('In Progress', '#0891b2', 5, 1, 0, 0),
 ('Closed', '#64748b', 6, 0, 0, 0);