-- ---------------------------------------------------------------------------
-- wallet_transactions: one row per reference (double-refund backstop)
-- ---------------------------------------------------------------------------
-- The refund idempotency guard in helpers/refund_service.php checks "does a
-- REFUND-{id} transaction already exist?" before crediting. That check is
-- application-level (check-then-insert) and, under a concurrent/duplicated
-- cancel, two callers can both pass it and both credit the customer — a refund
-- larger than what was paid. cancel.php now gates the refund on an atomic
-- status transition, and THIS unique index is the database-level safety net:
-- a second REFUND-{id} (or any duplicate reference) is rejected outright, and
-- refund_service.php treats that rejection as "already refunded".
--
-- Safe to add: every reference this app writes is unique by construction —
--   REFUND-<requestId>, REFUND-CLAWBACK-<requestId>  (helpers/refund_service.php)
--   RC-<uuid>   (api/payment-receipts.php  — driver payout)
--   WD-<uuid>   (api/wallet/withdraw.php   — withdrawal)
--   ADJ-<uuid>  (api/admin/wallets.php     — admin adjustment)
-- so nothing legitimate collides.
--
-- PREREQUISITE: the table must contain no pre-existing duplicate references
-- (past double-refunds would have left duplicate REFUND-<id> rows). Check with
-- the SELECT below first; it must return zero rows before the ALTER succeeds.
--
--   SELECT reference, COUNT(*) c FROM wallet_transactions
--   GROUP BY reference HAVING c > 1;
--
-- Run once against the live database.
-- ---------------------------------------------------------------------------

ALTER TABLE `wallet_transactions`
  ADD UNIQUE KEY `uniq_reference` (`reference`);

-- If your MySQL/MariaDB rejects the above for index-key length (older servers
-- without a large index prefix, on utf8mb4), use a prefix index instead — every
-- reference is far shorter than 191 chars, so this still enforces full uniqueness:
--
--   ALTER TABLE `wallet_transactions`
--     ADD UNIQUE KEY `uniq_reference` (`reference`(191));