OpenAI Usage Report - Diagnostics & Fix

1. Testing Database Connection...
✅ Database connected

2. Checking if sb_openai_usage table exists...
✅ Table exists

3. Checking table structure...
✅ Table structure:
   - id (int(11))
   - conversation_id (int(11))
   - user_id (int(11))
   - model (varchar(100))
   - prompt_tokens (int(11))
   - completion_tokens (int(11))
   - total_tokens (int(11))
   - cost (decimal(10,6))
   - created_at (timestamp)

4. Checking for existing data...
   Total records: 30302

5. Sample raw database records...
   Record 1: {"id":"30302","conversation_id":"0","user_id":"0","model":"text-embedding-3-small","prompt_tokens":"59176","completion_tokens":"0","total_tokens":"59176","cost":"0.147940","created_at":"2026-04-26 11:08:04"}
   Record 2: {"id":"30301","conversation_id":"0","user_id":"0","model":"text-embedding-3-small","prompt_tokens":"32302","completion_tokens":"0","total_tokens":"32302","cost":"0.080755","created_at":"2026-04-26 11:07:59"}
   Record 3: {"id":"30300","conversation_id":"0","user_id":"0","model":"text-embedding-3-small","prompt_tokens":"54639","completion_tokens":"0","total_tokens":"54639","cost":"0.136598","created_at":"2026-04-26 11:07:56"}
✅ Data exists in table

7. Testing query functions...
   Date range: 2026-05-06 to 2026-06-04

   a) Testing sb_openai_get_daily_usage()...
      Type: array
      ❌ No data returned or error
      Value: []

   b) Testing sb_openai_get_usage()...
      Type: array
      ❌ No data returned or error
      Value: []

   c) Testing raw SQL queries...
      Query: SELECT DATE_FORMAT(created_at, '%Y-%m-%d') as period,
               COUNT(*) as request_count,
               SUM(total_tokens) as total_tokens,
               SUM(cost) as total_cost,
               GROUP_CONCAT(DISTINCT model) as models
        FROM sb_openai_usage
        WHERE created_at >= '2026-05-06 00:00:00' AND created_at <= '2026-06-04 23:59:59'
        GROUP BY period
        ORDER BY period ASC
        LIMIT 3

      Type: array
      Count: 0

8. Testing date format handling...
   Test 1: 2026-02-10 to 2026-02-10 - ✅ 3 records
   Test 2: 2026-05-06 to 2026-06-04 - ❌ No data
   Test 3: 10/02/2026 to 10/02/2026 - ❌ No data

============================================================
SUMMARY:
============================================================
✅ SUCCESS! Database has 30302 OpenAI usage records

Next steps:
1. Go to: http://localhost/Chatbot/admin.php?report=openai-usage
2. Select date range: 2026-05-06 to 2026-06-04
3. You should now see data in the report

If still seeing 'No data found':
1. Clear browser cache (Ctrl+Shift+Delete)
2. Hard reload (Ctrl+F5)
3. Check browser console (F12) for errors