-- configurable options like WHMCS: groups of options linked to products, each option has choices (suboptions)
-- with a price per currency and billing cycle; the choices of a service are in service_config
CREATE TABLE config_groups (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  description VARCHAR(500) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE config_group_products (
  group_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (group_id, product_id),
  KEY product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE config_options (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  group_id INT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  type TINYINT NOT NULL DEFAULT 1 COMMENT '1 dropdown, 2 radio, 3 yes/no, 4 quantity',
  qty_min INT NOT NULL DEFAULT 0,
  qty_max INT NOT NULL DEFAULT 0 COMMENT '0 = no maximum',
  hidden TINYINT(1) NOT NULL DEFAULT 0,
  sort INT NOT NULL DEFAULT 0,
  KEY grp (group_id, sort)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE config_suboptions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  option_id INT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL DEFAULT '',
  hidden TINYINT(1) NOT NULL DEFAULT 0,
  sort INT NOT NULL DEFAULT 0,
  KEY opt (option_id, sort)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE config_prices (
  suboption_id INT UNSIGNED NOT NULL,
  currency_id INT UNSIGNED NOT NULL,
  cycle VARCHAR(16) NOT NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  setup DECIMAL(12,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (suboption_id, currency_id, cycle)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE service_config (
  service_id INT UNSIGNED NOT NULL,
  option_id INT UNSIGNED NOT NULL,
  suboption_id INT UNSIGNED NOT NULL,
  qty INT NOT NULL DEFAULT 0,
  PRIMARY KEY (service_id, option_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;