-- Faunamix production readiness DB audit (read-only). PII minimal.
SELECT DATABASE() AS active_db;

-- schema_migrations tablosu bu DB'de yoksa migration takibi deploy/bootstrap log ile doğrulanır.

SELECT COUNT(*) AS pending_payment_old
FROM ecommerce_orders
WHERE status = 'pending_payment'
  AND created_at < (UTC_TIMESTAMP() - INTERVAL 7 DAY);

SELECT COUNT(*) AS paid_without_captured_payment
FROM ecommerce_orders o
WHERE o.status IN ('paid', 'preparing', 'shipped', 'delivered')
  AND NOT EXISTS (
    SELECT 1 FROM ecommerce_payments p
    WHERE p.order_id = o.id AND p.status = 'captured'
  );

SELECT COUNT(*) AS inventory_reconcile_notes
FROM ecommerce_order_events
WHERE note LIKE '%inventory_reconcile%';

SELECT COUNT(*) AS paid_missing_sale_ledger
FROM ecommerce_orders o
WHERE o.status IN ('paid', 'preparing', 'shipped', 'delivered')
  AND NOT EXISTS (
    SELECT 1 FROM ecommerce_stock_ledger l
    WHERE l.order_id = o.id AND l.reason = 'sale'
  );

SELECT c.code, c.uses_count, c.max_uses,
       (SELECT COUNT(*) FROM ecommerce_orders o WHERE o.coupon_code = c.code AND o.status IN ('paid','preparing','shipped','delivered')) AS paid_with_code
FROM ecommerce_coupons c
WHERE c.uses_count > 0 OR c.max_uses IS NOT NULL
LIMIT 20;

SELECT COUNT(*) AS idempotency_total FROM ecommerce_checkout_idempotency;
SELECT COUNT(*) AS idempotency_expired FROM ecommerce_checkout_idempotency WHERE expires_at < UTC_TIMESTAMP();

SELECT COUNT(*) AS duplicate_idempotency_keys
FROM (
  SELECT idempotency_key, COUNT(*) c
  FROM ecommerce_payments
  GROUP BY idempotency_key
  HAVING c > 1
) t;
