-- OPSİYONEL — Elle çalıştırın; run-database-migrations.sh zincirine EKLENMEZ.
-- Amaç: Sandbox / UAT — TÜM sipariş kayıtlarını silip ödeme testine sıfırdan başlamak.
-- ÜRETİMDE GERÇEK SİPARİŞ VARSA KULLANMAYIN. Önce yedek veya önizleme SELECT'lerini çalıştırın.
--
-- iyzico sandbox panelindeki işlem geçmişi bu SQL ile silinmez (iyzico tarafında kalır).
-- Kapsam: ecommerce_orders tablosundaki TÜM siparişler (tarih / müşteri filtresi yok).

-- ========== 1) ÖNİZLEME (silmeden önce) ==========
SELECT status, payment_status, COUNT(*) AS cnt, SUM(total_try) AS sum_try
FROM ecommerce_orders
GROUP BY status, payment_status
ORDER BY status, payment_status;

SELECT id, order_number, status, payment_status, total_try, customer_name, created_at
FROM ecommerce_orders
ORDER BY created_at DESC;

-- ========== 2) SİLİNECEK SİPARİŞ ID'LERİ (geçici tablo) ==========
DROP TEMPORARY TABLE IF EXISTS _sandbox_orders_to_reset;
CREATE TEMPORARY TABLE _sandbox_orders_to_reset (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY
);

INSERT INTO _sandbox_orders_to_reset (id)
SELECT id FROM ecommerce_orders;

SELECT COUNT(*) AS orders_to_delete FROM _sandbox_orders_to_reset;

-- ========== 3) ÖDENEN SİPARİŞLERDE STOK GERİ YÜKLE (sale ledger varsa) ==========
UPDATE products p
INNER JOIN (
  SELECT oi.product_id, SUM(oi.quantity) AS qty_add
  FROM ecommerce_order_items oi
  INNER JOIN _sandbox_orders_to_reset t ON t.id = oi.order_id
  INNER JOIN ecommerce_orders o ON o.id = t.id
  INNER JOIN ecommerce_stock_ledger l ON l.order_id = o.id AND l.reason = 'sale' AND l.qty_delta < 0
  WHERE o.status = 'paid'
    AND (oi.variant_id IS NULL OR oi.variant_id = 0)
  GROUP BY oi.product_id
) s ON s.product_id = p.id
SET p.stock_quantity = p.stock_quantity + s.qty_add;

UPDATE product_variants v
INNER JOIN ecommerce_order_items oi ON oi.variant_id = v.id AND oi.variant_id > 0
INNER JOIN _sandbox_orders_to_reset t ON t.id = oi.order_id
INNER JOIN ecommerce_orders o ON o.id = t.id
INNER JOIN ecommerce_stock_ledger l ON l.order_id = o.id AND l.reason = 'sale' AND l.qty_delta < 0
SET v.stock_quantity = v.stock_quantity + oi.quantity
WHERE o.status = 'paid';

-- ========== 4) BAĞLI KAYITLAR + SİPARİŞLER ==========
DELETE FROM customer_return_requests
WHERE order_id IN (SELECT id FROM _sandbox_orders_to_reset);

DELETE FROM ecommerce_stock_ledger
WHERE order_id IN (SELECT id FROM _sandbox_orders_to_reset);

DELETE FROM ecommerce_checkout_idempotency
WHERE order_id IN (SELECT id FROM _sandbox_orders_to_reset);

DELETE o
FROM ecommerce_orders o
INNER JOIN _sandbox_orders_to_reset t ON t.id = o.id;

-- Sipariş dışı kalan checkout idempotency anahtarları
DELETE FROM ecommerce_checkout_idempotency;

-- ========== 5) SEPET (temiz checkout için) ==========
DELETE FROM shop_cart_lines;

UPDATE shop_customers
SET cart_abandon_reminder_sent_at = NULL
WHERE cart_abandon_reminder_sent_at IS NOT NULL;

-- ========== 6) DOĞRULAMA ==========
SELECT COUNT(*) AS remaining_orders_total FROM ecommerce_orders;

DROP TEMPORARY TABLE IF EXISTS _sandbox_orders_to_reset;

-- Sonrası: Admin «Rotaları senkronize et» (route cache) + tarayıcıda gizli sekme ile test.
