-- product addons (extra IP, backups …) ordered for a service and renewed with it
CREATE TABLE product_addons (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  description VARCHAR(1000) NOT NULL DEFAULT '',
  products VARCHAR(500) NOT NULL DEFAULT '' COMMENT 'product ids it can be added to, empty = all',
  hidden TINYINT(1) NOT NULL DEFAULT 0,
  sort INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE addon_prices (
  addon_id INT UNSIGNED NOT NULL,
  currency_id INT UNSIGNED NOT NULL,
  cycle VARCHAR(16) NOT NULL,
  price DECIMAL(16,2) NOT NULL,
  setup_fee DECIMAL(16,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (addon_id, currency_id, cycle)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE service_addons (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  service_id INT UNSIGNED NOT NULL,
  addon_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  status VARCHAR(12) NOT NULL DEFAULT 'Pending' COMMENT 'Pending|Active|Cancelled',
  billing_cycle VARCHAR(16) NOT NULL,
  amount DECIMAL(16,2) NOT NULL DEFAULT 0,
  next_due_date DATE NULL,
  reg_date DATE NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY service (service_id),
  KEY due (status, next_due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE invoice_items ADD addon_id INT UNSIGNED NULL AFTER service_id, ADD KEY addon (addon_id, period_start);