-- ============================================================
-- DEFERRED SQL FIXES - DO NOT EXECUTE WITHOUT REVIEW
-- Event: Mohamed Abdo NYE 2025
-- Generated: 5 January 2026
-- Status: FOR REVIEW ONLY - Apply after client approval
-- ============================================================

-- ============================================================
-- SECTION 1: Previously Identified Corrections
-- ============================================================

-- 1a. Cancel duplicate orders (empty shells)
-- Orders 522, 523 are duplicates of 530 - same timestamp, same customer, 0 line items
UPDATE orders
SET status = 'cancelled',
    payment_status = 'voided',
    admin_notes = CONCAT(COALESCE(admin_notes, ''), '\n[2026-01-05] Audit: Duplicate order - no line items. See Order 530 for valid record.')
WHERE id IN (522, 523);

-- 1b. Fix Order 568 (Arwa Zawawi) - was labeled 'stripe', actually Revolut
UPDATE orders
SET payment_method = 'revolut',
    revolut_payment_id = '6916df3d-525e-a6ac-a649-bcf5907fc4aa',
    admin_notes = CONCAT(COALESCE(admin_notes, ''), '\n[2026-01-05] Audit: Corrected payment method from stripe to revolut. Verified via Revolut API.')
WHERE id = 568;

-- 1c. Fix Order 530 (Talal) - add correct Revolut ID
UPDATE orders
SET revolut_payment_id = '69171609-480e-adb0-8c67-816037b4fa78',
    admin_notes = CONCAT(COALESCE(admin_notes, ''), '\n[2026-01-05] Audit: Added correct Revolut payment ID. Verified via Revolut API.')
WHERE id = 530;


-- ============================================================
-- SECTION 2: Exact Amount+Date Matches (42 orders)
-- These POS orders matched to orphaned Revolut payments
-- ============================================================

-- Order 713 (ahmad ahli, £5,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '69540643-30fd-a044-a25e-652ea3fc233f' WHERE id = 713;

-- Order 723 (Ahmed Alsayadi, £5,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6953e48a-2fe5-a7a4-a1c8-a4f1ef8a8f8a' WHERE id = 723;

-- Order 668 (Sheikh Zayed Al Hamed, £4,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6952af5a-bea3-a0f0-b6ad-e678ef337b0c' WHERE id = 668;

-- Order 697 (charlie farrell, £3,900) matched by amount+date
UPDATE orders SET revolut_payment_id = '6952f1f5-2fff-adb0-8ecd-f83895c35d7b' WHERE id = 697;

-- Order 694 (hamed alghamdi, £3,700) matched by amount+date
UPDATE orders SET revolut_payment_id = '6952e56c-fa81-afb2-a1ae-784991e57395' WHERE id = 694;

-- Order 670 (ahmed saleh, £3,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6952b279-3b07-aca2-926b-5aa48c90d6db' WHERE id = 670;

-- Order 701 (hessa, £3,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6952a592-f13d-a9f4-8c22-d55a02edd7ec' WHERE id = 701;

-- Order 665 (hana ghodran, £2,900) matched by amount+date
UPDATE orders SET revolut_payment_id = '69528997-25ea-a4e4-af5f-639157aa67eb' WHERE id = 665;

-- Order 708 (areej alkhalis, £2,900) matched by amount+date
UPDATE orders SET revolut_payment_id = '69544586-222d-ad81-8829-93fe0fb8ec25' WHERE id = 708;

-- Order 756 (lamia alanhadi, £2,900) matched by amount+date
UPDATE orders SET revolut_payment_id = '6953b846-f033-a28d-913b-e568da50a8ca' WHERE id = 756;

-- Order 664 (Norah Alkuait, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '69530b23-e76c-a0df-b059-c544b3f9b448' WHERE id = 664;

-- Order 669 (Salman Al Saud, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6952d224-c120-a5ec-8f79-8ebf3be4b365' WHERE id = 669;

-- Order 678 (hafid alenizi, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6952b89d-4a9f-a990-86dc-5283e1552df7' WHERE id = 678;

-- Order 689 (Mohammed AlRayyani, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6952a348-3627-a0b5-8eae-27e7218b5dcd' WHERE id = 689;

-- Order 698 (Rashid alhajri, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '69528304-7286-a768-8f68-6c751bbde9cf' WHERE id = 698;

-- Order 704 (mariam omar, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '69542eae-ac79-a4bf-b144-1d1480cf4ead' WHERE id = 704;

-- Order 709 (ahmed sultan, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '69542be4-39e1-a717-aa70-2d61d0b1ab25' WHERE id = 709;

-- Order 716 (Khaled Al anzi, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6954245e-5947-ab6d-a27f-8c7514236443' WHERE id = 716;

-- Order 719 (Albeer Algamdi, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '69542435-b27a-ac3f-80eb-607cd513850d' WHERE id = 719;

-- Order 720 (yasser alsalah, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '695418cf-d9ce-a4ee-9bfc-3220e413dde7' WHERE id = 720;

-- Order 722 (Ibrahim Zaid, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '69540577-47c9-a48e-8f90-ff6c98732fd3' WHERE id = 722;

-- Order 728 (fahad alanazi, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '695400b2-0e03-a128-9dd6-b21e8f681e30' WHERE id = 728;

-- Order 731 (saud al thani, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6953fa24-195d-a715-83d7-c5cfe0fef174' WHERE id = 731;

-- Order 732 (latifaalnaemi, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6953f5dd-12af-a097-bd33-e56e4653886d' WHERE id = 732;

-- Order 733 (saud alqhatani, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6953f5cc-8932-adee-9d7c-d58b919780c2' WHERE id = 733;

-- Order 736 (Abdulqader al-ahmad, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6953ef03-4f69-a54f-9663-b1bcf2966d7b' WHERE id = 736;

-- Order 738 (Khalid Alshahrani, £2,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6953d26f-e708-a41f-a80d-ef3fd2b3a325' WHERE id = 738;

-- Order 730 (abdulla alajmi, £1,850) matched by amount+date
UPDATE orders SET revolut_payment_id = '69544fb6-858d-ab9b-8832-19705406f3e1' WHERE id = 730;

-- Order 725 (nouf alsaud, £1,450) matched by amount+date
UPDATE orders SET revolut_payment_id = '69544866-dd90-a782-9526-8fa36d2c0d27' WHERE id = 725;

-- Order 666 (maryam alsubaie, £1,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '695314cb-8925-a529-8c09-588759a27998' WHERE id = 666;

-- Order 667 (Noor Salem, £1,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6952fcff-a7bd-a7be-be4b-683ec655531c' WHERE id = 667;

-- Order 676 (Fadwa Saad, £1,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6952f6eb-643e-a35f-a0c1-dfce873d86b2' WHERE id = 676;

-- Order 677 (Mohamed Saber, £1,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6952f6d3-fd07-a60e-877a-17416d38157c' WHERE id = 677;

-- Order 693 (narmin eyyubova, £1,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6952e90b-3b6f-aebe-bd8f-70e97e4bd81c' WHERE id = 693;

-- Order 703 (maryam al anazi, £1,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6952e6ab-c7f2-a2d1-a1b0-80add6d273bc' WHERE id = 703;

-- Order 717 (Khaled al zani, £1,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '69545e84-88f5-a2d3-907f-eab5de86f523' WHERE id = 717;

-- Order 734 (Munira Almutib, £1,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '69545a04-fcba-ad96-9f8e-eedbf95e89f0' WHERE id = 734;

-- Order 735 (Hussain Mohammad, £1,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '69545811-df33-afdf-84db-6fe206b06d7f' WHERE id = 735;

-- Order 742 (Nasser Ahmed, £1,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6954450f-c51a-a127-8734-df3f5d6f7ca2' WHERE id = 742;

-- Order 761 (abdullah alenezi, £1,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '69543666-7d2c-ad48-92ae-eaa326f3a11e' WHERE id = 761;

-- Order 762 (Mohammad salman, £1,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '6954346d-21e1-a07b-b714-aeb9c622cb62' WHERE id = 762;

-- Order 763 (sarah ali, £1,000) matched by amount+date
UPDATE orders SET revolut_payment_id = '695429cf-1f89-ac16-be98-83c1c3a4b1ae' WHERE id = 763;


-- ============================================================
-- VERIFICATION QUERIES (Run these BEFORE applying fixes)
-- ============================================================

-- Check current state of orders to be modified
SELECT id, customer_name, total_amount, payment_method, revolut_payment_id, status
FROM orders
WHERE id IN (522, 523, 530, 568, 713, 723, 668, 697, 694, 670, 701, 665, 708, 756,
             664, 669, 678, 689, 698, 704, 709, 716, 719, 720, 722, 728, 731, 732,
             733, 736, 738, 730, 725, 666, 667, 676, 677, 693, 703, 717, 734, 735,
             742, 761, 762, 763)
ORDER BY id;

-- Count of fixes to apply
-- Section 1: 4 orders (522, 523, 530, 568)
-- Section 2: 42 orders (exact matches)
-- Total: 46 orders