-- OPSİYONEL — Elle çalıştırın; run-database-migrations.sh zincirine EKLENMEZ.
-- Amaç: Test vitrin verisini sil (ürün, kategori, menü, sipariş, müşteri, rezervasyon, mesaj).
-- KORUNUR: admin_users, site_settings, yasal site_pages (gövde), şema.
-- ÖNCE YEDEK: ./scripts/backup-database.sh
-- Onay: FAUNAMIX_PURGE_CONFIRM=1 ./scripts/run-vitrin-test-data-purge.sh

-- ========== 1) ÖNİZLEME — demo izleri ==========
SELECT id, slug, title, LEFT(description, 80) AS description_preview
FROM products
WHERE is_active = 1
  AND (
    LOWER(title) REGEXP 'test|demo|deneme|veysel|lorem'
    OR LOWER(COALESCE(description, '')) REGEXP 'test tr|demo|deneme|lorem'
    OR LOWER(slug) REGEXP 'test|demo|veyse|veysel'
  )
ORDER BY id;

SELECT COUNT(*) AS active_products FROM products WHERE COALESCE(is_active, 1) = 1;
SELECT COUNT(*) AS orders FROM ecommerce_orders;
SELECT COUNT(*) AS nav_items FROM nav_menu_items;
SELECT COUNT(*) AS footer_items FROM footer_menu_items;

-- ========== 2) SİPARİŞ / ÖDEME (sandbox reset mantığı) ==========
DROP TEMPORARY TABLE IF EXISTS _vitrin_orders_to_purge;
CREATE TEMPORARY TABLE _vitrin_orders_to_purge (id BIGINT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO _vitrin_orders_to_purge (id) SELECT id FROM ecommerce_orders;

UPDATE products p
INNER JOIN (
  SELECT oi.product_id, SUM(oi.quantity) AS qty_add
  FROM ecommerce_order_items oi
  INNER JOIN _vitrin_orders_to_purge 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 _vitrin_orders_to_purge 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';

DELETE FROM customer_return_requests
WHERE order_id IN (SELECT id FROM _vitrin_orders_to_purge);

DELETE FROM ecommerce_refunds
WHERE order_id IN (SELECT id FROM _vitrin_orders_to_purge);

DELETE FROM ecommerce_shipments
WHERE order_id IN (SELECT id FROM _vitrin_orders_to_purge);

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

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

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

DELETE FROM ecommerce_checkout_idempotency;

DROP TEMPORARY TABLE IF EXISTS _vitrin_orders_to_purge;

-- ========== 3) SEPET / MÜŞTERİ ==========
DELETE FROM shop_cart_lines;
DELETE FROM customer_wishlist;
DELETE FROM shop_customer_addresses;
DELETE FROM shop_password_resets;
DELETE FROM shop_sessions;
DELETE FROM shop_customers;

-- ========== 4) REZERVASYON / İLETİŞİM / İÇERİK REVİZYON ==========
DELETE FROM reservation_requests;
DELETE FROM contact_submissions;

DELETE FROM content_revisions
WHERE entity_type IN ('product', 'product_category', 'nav_menu_item', 'footer_menu_item');

-- ========== 5) ÜRÜN BAĞLANTILARI ==========
DELETE FROM product_reviews;
DELETE FROM product_discount_offers;
DELETE FROM product_facet_map;
DELETE FROM product_variants;
DELETE FROM product_variant_options;

DELETE FROM ecommerce_stock_ledger WHERE product_id > 0;

-- ========== 6) ROTALAR (ürün/kategori/demo slug) ==========
DELETE FROM routes
WHERE type IN ('product', 'category')
   OR path REGEXP '^/(veysel|veyse|test|demo)'
   OR path REGEXP '/(veysel|veyse|test|demo)'
   OR redirect_to REGEXP '(veysel|veyse|test|demo)';

-- ========== 7) ÜRÜN + KATEGORİ ==========
DELETE FROM products;
DELETE FROM product_categories;

-- ========== 8) MENÜLER ==========
DELETE FROM footer_menu_items;
DELETE FROM nav_menu_items;

-- ========== 9) DOĞRULAMA ==========
SELECT COUNT(*) AS remaining_products FROM products;
SELECT COUNT(*) AS remaining_categories FROM product_categories;
SELECT COUNT(*) AS remaining_orders FROM ecommerce_orders;
SELECT COUNT(*) AS remaining_nav FROM nav_menu_items;
SELECT COUNT(*) AS remaining_footer FROM footer_menu_items;

-- Sonrası: ./scripts/run-vitrin-production-seed.sh (aynı oturumda) + route sync.
