-- Gateway subscriptions (PayPal I-…, Skrill, Stripe) per service, and saved payment methods (tokens only).
ALTER TABLE services ADD subscription_id VARCHAR(64) NOT NULL DEFAULT '' AFTER billing_cycle, ADD KEY subscription (subscription_id);
ALTER TABLE invoices ADD autocharge_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, ADD autocharge_at DATETIME NULL;

CREATE TABLE client_paymethods (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  gateway VARCHAR(32) NOT NULL,
  customer_ref VARCHAR(100) NOT NULL COMMENT 'e.g. Stripe cus_…',
  method_ref VARCHAR(100) NOT NULL COMMENT 'e.g. Stripe pm_…',
  brand VARCHAR(32) NOT NULL DEFAULT '',
  last4 CHAR(4) NOT NULL DEFAULT '',
  exp VARCHAR(7) NOT NULL DEFAULT '',
  is_default TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  KEY client (client_id, gateway),
  UNIQUE KEY method (gateway, method_ref)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;