-- promo / coupon codes for orders
CREATE TABLE promotions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(64) NOT NULL,
  type VARCHAR(8) NOT NULL DEFAULT 'percent' COMMENT 'percent|fixed',
  value DECIMAL(10,2) NOT NULL,
  recurring TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = also on renewals (the service keeps the lower price)',
  products VARCHAR(500) NOT NULL DEFAULT '' COMMENT 'product ids, empty = all',
  cycles VARCHAR(200) NOT NULL DEFAULT '' COMMENT 'billing cycles, empty = all',
  starts_at DATE NULL,
  expires_at DATE NULL,
  max_uses INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 = unlimited',
  uses INT UNSIGNED NOT NULL DEFAULT 0,
  once_per_client TINYINT(1) NOT NULL DEFAULT 1,
  new_clients_only TINYINT(1) NOT NULL DEFAULT 0,
  notes VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  UNIQUE KEY code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE services ADD promo_id INT UNSIGNED NULL;
ALTER TABLE orders ADD promo_code VARCHAR(64) NOT NULL DEFAULT '';