-- Apply after step 44 before deploying checkout reservation code.
-- Payment attempts survive active-session cleanup. These tables never issue refunds.
CREATE TABLE IF NOT EXISTS sessionStripeCheckouts (
  id char(32) NOT NULL,
  vendorId varchar(50) NOT NULL,
  sessionId int UNSIGNED NOT NULL,
  customerId int NOT NULL,
  customerUserId bigint UNSIGNED DEFAULT NULL,
  paymentIntent varchar(255) DEFAULT NULL,
  amountMinor bigint UNSIGNED NOT NULL,
  currency char(3) NOT NULL DEFAULT 'myr',
  selectionSnapshot longtext DEFAULT NULL,
  state enum('preparing','pending','succeeded','canceled') NOT NULL,
  createdAt int UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_checkout_intent (vendorId, paymentIntent),
  KEY idx_checkout_session (vendorId, sessionId, state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS sessionStripeCheckoutItems (
  checkoutId char(32) NOT NULL,
  sessionItemId int NOT NULL,
  quantity int UNSIGNED NOT NULL,
  PRIMARY KEY (checkoutId, sessionItemId),
  CONSTRAINT fk_checkout_item_checkout FOREIGN KEY (checkoutId) REFERENCES sessionStripeCheckouts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS stripePaymentSettlements (
  vendorId varchar(50) NOT NULL,
  paymentIntent varchar(255) NOT NULL,
  sessionId int UNSIGNED NOT NULL,
  sessionPaymentId int UNSIGNED DEFAULT NULL,
  amountMinor bigint UNSIGNED NOT NULL,
  currency char(3) NOT NULL DEFAULT 'myr',
  state enum('mapped','unmapped','legacy_recorded') NOT NULL,
  recordedAt int UNSIGNED NOT NULL,
  PRIMARY KEY (vendorId, paymentIntent)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Keep in-flight legacy intents reserved. Their stored selection is read lazily
-- until fulfillment rows need redistribution. Missing selections reserve the bill.
INSERT IGNORE INTO sessionStripeCheckouts
  (id, vendorId, sessionId, customerId, customerUserId, paymentIntent, amountMinor, currency, selectionSnapshot, state, createdAt)
SELECT MD5(CONCAT(sp.vendorId, ':', sp.paymentIntent)), sp.vendorId, sp.sessionId,
       COALESCE(sp.customerId, 0), cp.customerUserId, sp.paymentIntent,
       ROUND(sp.amount * 100), 'myr', sp.selectedItems, 'pending', UNIX_TIMESTAMP()
FROM stripePayments sp
JOIN sessions s ON s.id = sp.sessionId AND s.vendorId = sp.vendorId
LEFT JOIN customerStripePayments cp ON cp.vendorId = sp.vendorId AND cp.paymentIntent = sp.paymentIntent
WHERE sp.status NOT IN ('succeeded', 'succeeded_unmapped', 'canceled');

-- Already processed legacy callbacks must not add another session payment after
-- deployment or receipt cleanup. No payment amount/ownership is inferred here.
INSERT IGNORE INTO stripePaymentSettlements
  (vendorId, paymentIntent, sessionId, sessionPaymentId, amountMinor, state, recordedAt)
SELECT vendorId, paymentIntent, COALESCE(sessionId, 0), NULL,
       ROUND(COALESCE(amountReceived, amount, 0) * 100),
       CASE WHEN status='succeeded_unmapped' THEN 'unmapped' ELSE 'legacy_recorded' END, UNIX_TIMESTAMP()
FROM stripePayments WHERE status IN ('succeeded', 'succeeded_unmapped');

-- Rerun this step after API cutover to account for old workers completing during
-- migration. Captures require a durable replay record before releasing a hold.
UPDATE sessionStripeCheckouts checkout JOIN stripePayments sp
  ON sp.vendorId=checkout.vendorId AND sp.paymentIntent=checkout.paymentIntent
LEFT JOIN stripePaymentSettlements settled
  ON settled.vendorId=sp.vendorId AND settled.paymentIntent=sp.paymentIntent
SET checkout.state=CASE WHEN sp.status='canceled' THEN 'canceled' ELSE 'succeeded' END
WHERE checkout.state IN ('preparing','pending')
  AND (sp.status='canceled' OR (sp.status IN ('succeeded','succeeded_unmapped') AND settled.paymentIntent IS NOT NULL));