-- CWP billing module on the servers (CWP sync API v1, /api/v1/cwp/): pairing, the server's account snapshot, events.
ALTER TABLE servers
  ADD sync_token_hash CHAR(64) NOT NULL DEFAULT '' COMMENT 'sha256 of the token the CWP module uses',
  ADD sync_token_old_hash CHAR(64) NOT NULL DEFAULT '' COMMENT 'previous token, valid until sync_token_old_until (rotation)',
  ADD sync_token_old_until DATETIME NULL,
  ADD pair_token_hash CHAR(64) NOT NULL DEFAULT '' COMMENT 'one-time pairing token from the admin',
  ADD pair_token_until DATETIME NULL,
  ADD sync_info TEXT NULL COMMENT 'JSON: CWP version, load, free disk ... of the last report',
  ADD last_report_at DATETIME NULL,
  ADD last_report_id VARCHAR(64) NOT NULL DEFAULT '';

-- the accounts the server reported last (full snapshot per report)
CREATE TABLE server_accounts (
  server_id INT UNSIGNED NOT NULL,
  username VARCHAR(32) NOT NULL,
  domain VARCHAR(190) NOT NULL DEFAULT '',
  package VARCHAR(100) NOT NULL DEFAULT '',
  status VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'active|suspended',
  ip VARCHAR(64) NOT NULL DEFAULT '',
  dedicated_ip TINYINT(1) NOT NULL DEFAULT 0,
  reseller VARCHAR(32) NOT NULL DEFAULT '',
  email VARCHAR(190) NOT NULL DEFAULT '',
  setup_date VARCHAR(32) NOT NULL DEFAULT '',
  backup TINYINT(1) NOT NULL DEFAULT 0,
  disk_used_mb DECIMAL(14,2) NOT NULL DEFAULT 0,
  bw_used_mb DECIMAL(14,2) NOT NULL DEFAULT 0,
  inodes INT UNSIGNED NOT NULL DEFAULT 0,
  reported_at DATETIME NOT NULL,
  PRIMARY KEY (server_id, username),
  KEY domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- changes made on the server itself (CWP hooks), logged and shown as drift; never applied to billing data
CREATE TABLE server_events (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  server_id INT UNSIGNED NOT NULL,
  event_id VARCHAR(64) NOT NULL,
  type VARCHAR(32) NOT NULL,
  username VARCHAR(32) NOT NULL DEFAULT '',
  domain VARCHAR(190) NOT NULL DEFAULT '',
  service_id INT UNSIGNED NULL,
  by_user VARCHAR(64) NOT NULL DEFAULT '',
  event_time DATETIME NULL,
  received_at DATETIME NOT NULL,
  UNIQUE KEY event (server_id, event_id),
  KEY service (service_id),
  KEY server_time (server_id, received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;