-- ====================================================================
-- Stripe payments (2026-09-25)
--   • payment_checkouts — one row per online-payment attempt. It saves the
--     checkout details (address, email, cart, amount) when the payment is
--     started, so the order can still be created from the Stripe webhook
--     when the shopper's browser never comes back after paying. `status`
--     is also the lock that makes sure one payment creates exactly one
--     order, even if the browser and the webhook arrive at the same time.
-- Plain statements only, safe to run more than once.
-- ====================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `payment_checkouts` (
  `id` VARCHAR(36) NOT NULL,
  `provider` VARCHAR(20) NOT NULL COMMENT 'stripe',
  `provider_ref` VARCHAR(255) NOT NULL COMMENT 'Stripe PaymentIntent id',
  `cart_id` VARCHAR(36) NOT NULL,
  `user_id` VARCHAR(36) NULL DEFAULT NULL COMMENT 'Logged-in customer at checkout; NULL for a guest',
  `guest_session_id` VARCHAR(255) NULL DEFAULT NULL,
  `details` LONGTEXT NOT NULL COMMENT 'Checkout form (JSON)',
  `amount` DECIMAL(12,2) NOT NULL,
  `currency` VARCHAR(3) NOT NULL,
  `status` ENUM('open', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'open',
  `order_id` VARCHAR(36) NULL DEFAULT NULL,
  `error` VARCHAR(500) NULL DEFAULT NULL,
  `ip_address` VARCHAR(45) NULL DEFAULT NULL,
  `user_agent` TEXT NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_provider_ref` (`provider`, `provider_ref`),
  KEY `idx_order` (`order_id`),
  KEY `idx_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
