-- ============================================================================
--  المرحلة 0 — تحقق للقراءة فقط قبل إصلاح أسعار الليرة/الدولار
--  Phase 0 - READ-ONLY verification, run on PRODUCTION before the migration.
--
--  لا يوجد في هذا الملف أي INSERT / UPDATE / DELETE / DDL.
--  This file contains NO writes of any kind. Safe to run on a live database.
--
--  الاستعمال / Usage:
--    mysql -u USER -p DBNAME < database/scripts/phase0_verify_syp_priced_orders.sql
--
--  الخلفية: بين 2025-07 و 2026-01-22 كانت الأسعار تُخزَّن بالليرة السورية
--  القديمة في أعمدة يفترض أن تكون بالدولار، فيضربها العرض بسعر الصرف مرة ثانية.
-- ============================================================================

SET @RATE      = 11500;         -- سعر الصرف المستنتج (ل.س قديمة/دولار)
SET @CUTOFF    = '2026-02-01';  -- كل ما بعده سليم
SET @THRESHOLD = 1000;          -- فوقه = المبلغ بالليرة قطعاً

-- ---------------------------------------------------------------------------
-- 1) الطلبات المتأثرة — يجب ألا يظهر أي طلب بتاريخ 2026-01-23 أو بعده
-- ---------------------------------------------------------------------------
SELECT '=== 1) affected orders ===' AS section;

SELECT
    o.id,
    o.code,
    o.created_at,
    o.grand_total,
    o.coupon_discount,
    o.payment_status,
    o.delivery_status,
    (SELECT ROUND(SUM(od.price + od.tax + od.shipping_cost), 2)
       FROM order_details od WHERE od.order_id = o.id)     AS details_sum,
    CASE WHEN o.grand_total >= @THRESHOLD THEN 'YES' ELSE 'no' END  AS gt_is_syp,
    CASE WHEN EXISTS (SELECT 1 FROM order_details od
                      WHERE od.order_id = o.id AND od.price >= @THRESHOLD)
         THEN 'YES' ELSE 'no' END                          AS details_are_syp,
    ROUND(CASE WHEN o.grand_total >= @THRESHOLD
               THEN o.grand_total / @RATE ELSE o.grand_total END, 2) AS gt_after_fix
FROM orders o
WHERE o.created_at < @CUTOFF
  AND ( o.grand_total >= @THRESHOLD
        OR EXISTS (SELECT 1 FROM order_details od
                   WHERE od.order_id = o.id AND od.price >= @THRESHOLD) )
ORDER BY o.id;

-- ---------------------------------------------------------------------------
-- 2) استخراج سعر الصرف من الطلبات "نصف السليمة"
--    (الإجمالي بالدولار والتفاصيل بالليرة) — يجب أن تدور النتائج حول 11,500
-- ---------------------------------------------------------------------------
SELECT '=== 2) implied exchange rate (expect ~11500) ===' AS section;

SELECT
    o.id,
    o.code,
    o.created_at,
    o.grand_total                                              AS usd_total,
    ROUND(SUM(od.price + od.tax + od.shipping_cost), 2)        AS syp_sum,
    ROUND(SUM(od.price + od.tax + od.shipping_cost)
          / NULLIF(o.grand_total, 0), 0)                       AS implied_rate
FROM orders o
JOIN order_details od ON od.order_id = o.id
WHERE o.created_at < @CUTOFF
  AND o.grand_total <  @THRESHOLD            -- الإجمالي بالدولار
  AND od.price      >= @THRESHOLD            -- التفاصيل بالليرة
GROUP BY o.id
ORDER BY o.id;

-- ---------------------------------------------------------------------------
-- 3) نفس الاستخراج من combined_orders التي إجمالي طلباتها بالدولار
-- ---------------------------------------------------------------------------
SELECT '=== 3) implied rate from combined_orders (expect ~11500) ===' AS section;

SELECT
    co.id                                       AS combined_id,
    co.created_at,
    co.grand_total                              AS co_syp_total,
    ROUND(SUM(o.grand_total), 2)                AS orders_usd_total,
    ROUND(co.grand_total / NULLIF(SUM(o.grand_total), 0), 0) AS implied_rate
FROM combined_orders co
JOIN orders o ON o.combined_order_id = co.id
WHERE co.created_at < @CUTOFF
  AND ABS(co.grand_total) >= @THRESHOLD
GROUP BY co.id
HAVING SUM(o.grand_total) < @THRESHOLD          -- الطلبات نفسها بالدولار
ORDER BY co.id;

-- ---------------------------------------------------------------------------
-- 4) combined_orders المتأثرة والمرتبطة بطلبات فعلية (هذه فقط ستُصحَّح)
-- ---------------------------------------------------------------------------
SELECT '=== 4) affected combined_orders WITH orders attached ===' AS section;

SELECT
    co.id                                   AS combined_id,
    co.created_at,
    co.grand_total,
    co.coupon_discount,
    co.shipping_cost,
    GROUP_CONCAT(o.id ORDER BY o.id)        AS order_ids,
    ROUND(co.grand_total / @RATE, 2)        AS gt_after_fix
FROM combined_orders co
JOIN orders o ON o.combined_order_id = co.id
WHERE co.created_at < @CUTOFF
  AND ABS(co.grand_total) >= @THRESHOLD
GROUP BY co.id
ORDER BY co.id;

-- ---------------------------------------------------------------------------
-- 5) combined_orders يتيمة (بلا صفوف orders) — لن تُمسّ، للعلم فقط
--    هذه سلال متروكة لا تظهر في أي تقرير ولا في أي شاشة.
-- ---------------------------------------------------------------------------
SELECT '=== 5) orphan combined_orders (NOT touched, informational) ===' AS section;

SELECT
    COUNT(*)            AS orphan_rows,
    MIN(co.created_at)  AS first_seen,
    MAX(co.created_at)  AS last_seen,
    ROUND(SUM(co.grand_total), 2) AS syp_sum
FROM combined_orders co
WHERE ABS(co.grand_total) >= @THRESHOLD
  AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.combined_order_id = co.id);

-- ---------------------------------------------------------------------------
-- 6) إثبات أن ما بعد نقطة القطع نظيف — يجب أن يكون العدد صفراً
-- ---------------------------------------------------------------------------
SELECT '=== 6) contamination after cutoff (MUST be 0) ===' AS section;

SELECT COUNT(*) AS orders_after_cutoff_over_5000
FROM orders o
WHERE o.created_at >= @CUTOFF
  AND ( o.grand_total >= 5000
        OR EXISTS (SELECT 1 FROM order_details od
                   WHERE od.order_id = o.id AND od.price >= 5000) );

-- المبالغ بين 1000 و 5000 بعد نقطة القطع هي منتجات غالية حقيقية (آيفون، أجهزة).
-- راجعها بالعين للتأكد:
SELECT o.id, o.code, o.created_at, o.grand_total
FROM orders o
WHERE o.created_at >= @CUTOFF AND o.grand_total >= @THRESHOLD
ORDER BY o.grand_total DESC;

-- ---------------------------------------------------------------------------
-- 7) خط الأساس — سجّل هذه الأرقام قبل التنفيذ وقارنها بعده.
--    كل شهر من 2026-02 فما بعد يجب أن يبقى مطابقاً حرفياً.
-- ---------------------------------------------------------------------------
SELECT '=== 7) monthly baseline - keep this output! ===' AS section;

SELECT
    DATE_FORMAT(o.created_at, '%Y-%m')  AS month,
    COUNT(*)                            AS orders_count,
    ROUND(SUM(o.grand_total), 2)        AS grand_total_sum,
    ROUND(MAX(o.grand_total), 2)        AS max_order
FROM orders o
GROUP BY month
ORDER BY month;

-- ---------------------------------------------------------------------------
-- 8) فحص التبعيات — يجب أن تكون صفراً
-- ---------------------------------------------------------------------------
SELECT '=== 8) downstream dependencies (expect 0) ===' AS section;

SELECT 'commission_histories' AS tbl, COUNT(*) AS rows_for_affected_orders
FROM commission_histories ch
WHERE ch.order_id IN (
    SELECT o.id FROM orders o
    WHERE o.created_at < @CUTOFF
      AND ( o.grand_total >= @THRESHOLD
            OR EXISTS (SELECT 1 FROM order_details od
                       WHERE od.order_id = o.id AND od.price >= @THRESHOLD) )
);

-- إن وُجدت هذه الجداول في الإنتاج (غير موجودة محلياً) فافحصها يدوياً:
--   refund_requests.order_id , club_points.order_id
SHOW TABLES LIKE '%refund%';
SHOW TABLES LIKE '%club%';

SELECT '=== END - no data was modified ===' AS section;