-- Apply after step 43, before deploying the corresponding API code.
-- Historical ownership is independent of the active participant/rejoin link.
CREATE TABLE customerDineInVisits (
  id bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  customerUserId bigint UNSIGNED NOT NULL,
  vendorId varchar(50) NOT NULL,
  sessionId int UNSIGNED NOT NULL,
  receiptCode varchar(50) DEFAULT NULL,
  startedTime int UNSIGNED NOT NULL,
  endedTime int UNSIGNED DEFAULT NULL,
  tableLabel varchar(50) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_customer_visit (customerUserId, vendorId, sessionId),
  KEY idx_customer_visit_history (customerUserId, endedTime),
  KEY idx_customer_visit_session (vendorId, sessionId),
  CONSTRAINT fk_customer_visit_user FOREIGN KEY (customerUserId) REFERENCES customerUsers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

ALTER TABLE sessionItems ADD COLUMN customerUserId bigint UNSIGNED DEFAULT NULL,
  ADD CONSTRAINT fk_session_item_customer FOREIGN KEY (customerUserId) REFERENCES customerUsers(id) ON DELETE SET NULL;
ALTER TABLE receiptItems ADD COLUMN customerUserId bigint UNSIGNED DEFAULT NULL,
  ADD KEY idx_receipt_item_customer (customerUserId, vendorId, receiptCode),
  ADD CONSTRAINT fk_receipt_item_customer FOREIGN KEY (customerUserId) REFERENCES customerUsers(id) ON DELETE SET NULL;

CREATE TABLE customerFavoritePreferences (
  customerUserId bigint UNSIGNED NOT NULL,
  vendorId varchar(50) NOT NULL,
  menuItemId int UNSIGNED NOT NULL,
  enabled tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (customerUserId, vendorId, menuItemId),
  CONSTRAINT fk_favorite_preference_user FOREIGN KEY (customerUserId) REFERENCES customerUsers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- One immutable payer association per intent, including anonymous intents.
-- No historical spend is inferred from order ownership or table participation.
CREATE TABLE customerStripePayments (
  vendorId varchar(50) NOT NULL,
  paymentIntent varchar(255) NOT NULL,
  customerUserId bigint UNSIGNED DEFAULT NULL,
  sourceApp enum('portal','dine-in') NOT NULL,
  sourceId varchar(50) NOT NULL,
  currency char(3) NOT NULL,
  capturedAmountMinor bigint UNSIGNED NOT NULL DEFAULT 0,
  refundedAmountMinor bigint UNSIGNED NOT NULL DEFAULT 0,
  capturedAt int UNSIGNED DEFAULT NULL,
  PRIMARY KEY (vendorId, paymentIntent),
  KEY idx_customer_stripe_spend (customerUserId, vendorId, currency),
  CONSTRAINT fk_customer_stripe_user FOREIGN KEY (customerUserId) REFERENCES customerUsers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Preserve the currently open, verifiably linked visits when upgrading.
INSERT INTO customerDineInVisits (customerUserId, vendorId, sessionId, startedTime, tableLabel)
SELECT DISTINCT sc.customerUserId, sc.vendorId, sc.sessionId, st.startedTime, st.label
FROM sessionCustomers sc
JOIN sessions_tables st ON st.id = sc.sessionId AND st.vendorId = sc.vendorId
WHERE sc.customerUserId IS NOT NULL;

UPDATE sessionItems si
JOIN sessionCustomers sc ON sc.id = si.customerId AND sc.sessionId = si.sessionId AND sc.vendorId = si.vendorId
SET si.customerUserId = sc.customerUserId
WHERE sc.customerUserId IS NOT NULL;