USE myarrival;

ALTER TABLE payments
  ADD COLUMN stripe_checkout_session_id VARCHAR(255) NULL AFTER provider_reference,
  ADD COLUMN stripe_payment_intent_id VARCHAR(255) NULL AFTER stripe_checkout_session_id;

CREATE UNIQUE INDEX uq_payments_stripe_session ON payments(stripe_checkout_session_id);
CREATE INDEX idx_payments_provider_reference ON payments(provider, provider_reference);

ALTER TABLE orders
  ADD COLUMN stripe_checkout_session_id VARCHAR(255) NULL AFTER currency;
CREATE UNIQUE INDEX uq_orders_stripe_session ON orders(stripe_checkout_session_id);

CREATE TABLE stripe_webhook_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  stripe_event_id VARCHAR(255) NOT NULL,
  event_type VARCHAR(120) NOT NULL,
  processed_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_stripe_event (stripe_event_id),
  INDEX idx_stripe_event_type (event_type)
) ENGINE=InnoDB;