-- Self-Service Now Milestone 4B: future mobile-order member attribution.
-- Target: MySQL 8 / kiosk_db.
-- Run once from phpMyAdmin after backing up kiosk_db.
--
-- Existing payment requests and kitchen orders remain NULL. This migration
-- does not award points, redeem points, or claim historical guest orders.

USE `kiosk_db`;

ALTER TABLE `hitpay_payment_requests`
  ADD COLUMN `loyalty_member_id` int DEFAULT NULL
    AFTER `source_session_token`,
  ADD KEY `idx_hitpay_payment_member` (`loyalty_member_id`),
  ADD CONSTRAINT `fk_hitpay_payment_member`
    FOREIGN KEY (`loyalty_member_id`)
    REFERENCES `loyalty_members` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

ALTER TABLE `kitchen_orders`
  ADD COLUMN `loyalty_member_id` int DEFAULT NULL
    AFTER `payment_request_id`,
  ADD KEY `idx_kitchen_order_member` (`loyalty_member_id`),
  ADD CONSTRAINT `fk_kitchen_order_member`
    FOREIGN KEY (`loyalty_member_id`)
    REFERENCES `loyalty_members` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE;
