-- several brands from one billing: own domain, name, theme, sender and company details; clients and products per brand
CREATE TABLE IF NOT EXISTS brands (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  host VARCHAR(190) NOT NULL COMMENT 'domain of its client area, e.g. billing.brand.com',
  app_url VARCHAR(255) NOT NULL,
  theme VARCHAR(64) NOT NULL DEFAULT '',
  mail_from VARCHAR(190) NOT NULL DEFAULT '',
  settings TEXT NULL COMMENT 'JSON: company details and other setting overrides',
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  UNIQUE KEY host (host)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- brand of a client in its own table: clients has FULLTEXT indexes, adding a column would lock it for writes
CREATE TABLE IF NOT EXISTS client_brands (
  client_id INT UNSIGNED NOT NULL PRIMARY KEY,
  brand_id INT UNSIGNED NOT NULL,
  KEY brand (brand_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE products ADD brands VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'brand ids, empty = all', ALGORITHM=INSTANT;