-- order funnel / abandoned carts: a visit of a product's order page, closed when the client orders it
CREATE TABLE carts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NULL,
  visitor CHAR(40) NOT NULL DEFAULT '' COMMENT 'hash of the session for guests',
  product_id INT UNSIGNED NOT NULL,
  cycle VARCHAR(16) NOT NULL DEFAULT '',
  order_id INT UNSIGNED NULL,
  ip VARCHAR(64) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  reminded_at DATETIME NULL,
  KEY client (client_id, product_id),
  KEY visitor (visitor, product_id),
  KEY open (order_id, updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO settings (k, v) VALUES ('cart_reminder_enabled', '0'), ('cart_reminder_hours', '2');