-- ============================================
-- Check Amount Storage Format
-- Date: January 4, 2025
-- Purpose: Verify if amounts are stored as Rial or Toman
-- ============================================

-- Step 1: Check sample amounts
SELECT 
    '=== SAMPLE AMOUNTS ===' as section,
    d.id as deal_id,
    d.didar_deal_id,
    d.title,
    d.payable_amount as deal_payable,
    t.id as trans_id,
    t.amount as trans_amount,
    CASE 
        WHEN t.amount = d.payable_amount THEN '✅ EXACT MATCH'
        WHEN t.amount = d.payable_amount * 10 THEN '⚠️ Transaction is 10x (Rial vs Toman)'
        WHEN t.amount = d.payable_amount / 10 THEN '❌ Transaction is 1/10 (ZERO LOST)'
        WHEN ABS(t.amount - d.payable_amount) < 1000 THEN '⚠️ SMALL DIFFERENCE'
        ELSE '❌ MISMATCH'
    END as amount_status
FROM deals d
INNER JOIN transactions t ON d.id = t.deal_id
WHERE d.didar_deal_id LIKE 'local_%'
  AND t.is_first_payment = 1
  AND t.status = 'confirmed'
  AND t.payment_date >= '2025-12-22'
  AND t.payment_date <= '2026-01-20'
ORDER BY d.register_time DESC
LIMIT 20;

-- Step 2: Check if formatToman is dividing correctly
SELECT 
    '=== FORMAT TOMAN CHECK ===' as section,
    'Raw Amount (from DB)' as raw_amount,
    'After /10 (formatToman)' as after_format,
    'Expected (if Toman in DB)' as expected_if_toman,
    'Expected (if Rial in DB)' as expected_if_rial
FROM (
    SELECT 
        SUM(COALESCE(t.amount, 0)) as raw_amount,
        SUM(COALESCE(t.amount, 0)) / 10 as after_format,
        SUM(COALESCE(t.amount, 0)) as expected_if_toman,
        SUM(COALESCE(t.amount, 0)) / 10 as expected_if_rial
    FROM deals d
    INNER JOIN transactions t ON d.id = t.deal_id
    WHERE t.is_first_payment = 1
      AND t.status = 'confirmed'
      AND t.payment_date >= '2025-12-22'
      AND t.payment_date <= '2026-01-20'
) calc;

-- Step 3: Check actual vs displayed
SELECT 
    '=== ACTUAL VS DISPLAYED ===' as section,
    'What DB Returns' as db_amount,
    'What formatToman Shows' as displayed_amount,
    'What Should Show (if Toman in DB)' as should_show_toman,
    'What Should Show (if Rial in DB)' as should_show_rial
FROM (
    SELECT 
        SUM(COALESCE(t.amount, 0)) as db_amount,
        SUM(COALESCE(t.amount, 0)) / 10 as displayed_amount,
        SUM(COALESCE(t.amount, 0)) as should_show_toman,
        SUM(COALESCE(t.amount, 0)) / 10 as should_show_rial
    FROM deals d
    INNER JOIN transactions t ON d.id = t.deal_id
    WHERE t.is_first_payment = 1
      AND t.status = 'confirmed'
      AND t.payment_date >= '2025-12-22'
      AND t.payment_date <= '2026-01-20'
) calc;