-- upgrade / downgrade of a service to another product or billing cycle, with prorated price
ALTER TABLE products ADD upgrades VARCHAR(500) NOT NULL DEFAULT '' COMMENT 'product ids clients may switch to';
CREATE TABLE service_upgrades (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  service_id INT UNSIGNED NOT NULL,
  old_product_id INT UNSIGNED NOT NULL,
  old_cycle VARCHAR(16) NOT NULL,
  new_product_id INT UNSIGNED NOT NULL,
  new_cycle VARCHAR(16) NOT NULL,
  new_price DECIMAL(16,2) NOT NULL,
  new_next_due DATE NULL,
  amount DECIMAL(16,2) NOT NULL COMMENT 'charged (>0) or credited (<0)',
  invoice_id INT UNSIGNED NULL,
  status VARCHAR(10) NOT NULL DEFAULT 'pending' COMMENT 'pending|done|cancelled',
  created_by VARCHAR(16) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  done_at DATETIME NULL,
  KEY service (service_id),
  KEY invoice (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;