-- Self-Service Now Milestone 4C: exactly-once HitPay loyalty earning.
-- Target: MySQL 8 / kiosk_db.
-- Run once from phpMyAdmin after backing up kiosk_db.
--
-- This migration has already been applied to the current sandbox database.
-- Existing loyalty ledger rows remain NULL and are not retroactively linked.

USE `kiosk_db`;

ALTER TABLE `loyalty_points_ledger`
  ADD COLUMN `hitpay_payment_request_id` bigint UNSIGNED DEFAULT NULL
    AFTER `invoice_no`,
  ADD UNIQUE KEY `uq_loyalty_points_hitpay_payment`
    (`hitpay_payment_request_id`),
  ADD CONSTRAINT `fk_loyalty_points_hitpay_payment`
    FOREIGN KEY (`hitpay_payment_request_id`)
    REFERENCES `hitpay_payment_requests` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;
