-- affiliate program (like WHMCS), e-mail contacts of clients, WHMCS ticket log ids
CREATE TABLE affiliates (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  visitors INT UNSIGNED NOT NULL DEFAULT 0,
  pay_type VARCHAR(12) NOT NULL DEFAULT '' COMMENT '"" = default %, percentage, fixed',
  pay_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  onetime TINYINT(1) NOT NULL DEFAULT 0,
  balance DECIMAL(12,2) NOT NULL DEFAULT 0,
  withdrawn DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  UNIQUE KEY client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE affiliate_accounts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  affiliate_id INT UNSIGNED NOT NULL,
  service_id INT UNSIGNED NOT NULL,
  last_paid DATE NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY service (service_id),
  KEY affiliate (affiliate_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE affiliate_history (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  affiliate_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NULL,
  invoice_id INT UNSIGNED NULL,
  description VARCHAR(255) NOT NULL DEFAULT '',
  amount DECIMAL(12,2) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY affiliate (affiliate_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE affiliate_pending (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  account_id INT UNSIGNED NOT NULL,
  invoice_id INT UNSIGNED NULL,
  amount DECIMAL(12,2) NOT NULL,
  clearing_date DATE NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY one (account_id, invoice_id),
  KEY clearing (clearing_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE affiliate_withdrawals (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  affiliate_id INT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  note VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  KEY affiliate (affiliate_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE products ADD affiliate_pay_type VARCHAR(12) NOT NULL DEFAULT '', ADD affiliate_pay_amount DECIMAL(10,2) NOT NULL DEFAULT 0, ADD affiliate_onetime TINYINT(1) NOT NULL DEFAULT 0;
CREATE TABLE client_contacts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL DEFAULT '',
  email VARCHAR(190) NOT NULL,
  email_prefs VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'JSON general/invoice/support/product',
  created_at DATETIME NOT NULL,
  KEY client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE ticket_log ADD whmcs_id INT UNSIGNED NULL, ADD UNIQUE KEY whmcs (whmcs_id);