-- SheListens — migration 015: prevent a Flutterwave payment being reused
-- Neither subscriptions/verify-payment.php nor bookings/verify-video-payment.php
-- checked whether a transaction_id/gateway_ref had already been credited
-- before creating a subscription or booking from it — a verified Flutterwave
-- charge could be replayed (the same successful transaction_id sent again)
-- to get a second subscription or booking for one real payment. Both
-- endpoints now check for this explicitly before writing anything (see
-- lib/payments.php), and this unique index is the backstop underneath
-- that: even if the application check is ever bypassed or has a bug, the
-- database itself refuses a second row with the same gateway_ref.
--
-- NULL gateway_ref values (manual/admin-entered payments with no
-- gateway reference) are unaffected — MySQL's unique index allows
-- multiple NULLs.
--
-- IMPORTANT — run this check first, before this migration:
--   SELECT gateway_ref, COUNT(*) FROM payments
--   WHERE gateway_ref IS NOT NULL GROUP BY gateway_ref HAVING COUNT(*) > 1;
-- If that returns any rows, this ALTER TABLE will fail (a unique index
-- can't be added while duplicates already exist). Don't just delete the
-- duplicate rows without looking at them first — each one may represent
-- a real client who was actually charged twice by this exact bug, and
-- deleting the row doesn't refund them. Review any duplicates manually
-- (check the subscription/booking each one created) before deciding
-- whether to remove, keep, or issue a refund for either side, then run
-- this migration once the table is clean.
--
-- Run via phpMyAdmin or: mysql -u USER -p DBNAME < backend/migrations/015_payment_replay_protection.sql

ALTER TABLE payments ADD UNIQUE INDEX uq_payments_gateway_ref (gateway_ref);