# Dashboard Calculations Verification Guide

## Overview
This document explains how to verify that all dashboard calculations are working correctly for both Admin and Organizer dashboards after integrating Pesapal payments.

## What to Check

### 1. Admin Dashboard

#### Transaction Count
- **Location**: Dashboard card showing "Total Transaction"
- **Query**: `Transaction::count()`
- **Should show**: All transactions including Pesapal
- **Verify**: Count matches total transactions in database

#### Booking Count  
- **Location**: Dashboard card showing "Total Event Bookings"
- **Query**: `Booking::count()`
- **Should show**: All bookings including Pesapal
- **Verify**: Count matches total bookings in database

#### Lifetime Earnings
- **Location**: Dashboard card showing "Life Time Earning"
- **Source**: `Earning` model (`total_revenue` field)
- **Updated in**: PesapalController callback (line 562)
- **Formula**: `total_revenue = total_revenue + price + tax`
- **Verify**: Value increases with each Pesapal payment

#### Total Profit
- **Location**: Dashboard card showing "Total Profit"
- **Source**: `Earning` model (`total_earning` field)
- **Updated in**: PesapalController callback (lines 564-567)
- **Formula**: 
  - If organizer exists: `total_earning = total_earning + tax + commission`
  - If no organizer: `total_earning = total_earning + price + tax`
- **Verify**: Value increases correctly with each payment

#### Monthly Profit Chart
- **Location**: Monthly Profit page
- **Query**: Uses `transactions` table with `payment_status = 1`
- **Calculations**:
  - Organizer income: `sum(commission)` where `organizer_id != null`
  - Tax: `sum(tax)` 
  - Admin profit: `sum(grand_total)` where `organizer_id = null`
- **Verify**: Pesapal transactions appear in monthly breakdown

### 2. Organizer Dashboard

#### Balance
- **Location**: Dashboard card showing "My Balance"
- **Source**: `Organizer` model (`amount` field)
- **Updated in**: `storeOrganizer()` function called from PesapalController (line 596)
- **Formula**: `amount = amount + (price - commission)`
- **Verify**: Balance increases correctly after each booking

#### Transaction Count
- **Location**: Dashboard card showing "Total Transcation"
- **Query**: `Transaction::where('organizer_id', organizer_id)->count()`
- **Should show**: All transactions for this organizer including Pesapal
- **Verify**: Count matches organizer's transactions

#### Booking Count
- **Location**: Dashboard card showing "Total Event Bookings"
- **Query**: `Booking::where('organizer_id', organizer_id)->count()`
- **Should show**: All bookings for this organizer including Pesapal
- **Verify**: Count matches organizer's bookings

#### Monthly Income Chart
- **Location**: Dashboard chart showing "Event Booking Monthly Income"
- **Query**: Uses `bookings` table with `paymentStatus = 'completed'`
- **Calculation**: `sum(price)` grouped by month
- **Verify**: Pesapal bookings appear in monthly income chart

### 3. Database Verification Queries

Run these SQL queries to verify data integrity:

```sql
-- Check Pesapal bookings have transactions
SELECT 
    b.id AS booking_id,
    b.booking_id AS booking_reference,
    b.paymentMethod,
    b.paymentStatus,
    t.id AS transaction_id,
    t.payment_status AS transaction_payment_status
FROM bookings b
LEFT JOIN transactions t ON t.booking_id = b.id AND t.transcation_type = 1
WHERE b.paymentMethod = 'Pesapal'
    AND (b.paymentStatus = 'completed' OR b.paymentStatus = '1')
ORDER BY b.created_at DESC;

-- Check transaction payment_status values
SELECT payment_status, COUNT(*) as count
FROM transactions
WHERE payment_method = 'Pesapal'
GROUP BY payment_status;

-- Verify earnings calculation
SELECT 
    SUM(price + tax) as expected_revenue,
    (SELECT total_revenue FROM earnings LIMIT 1) as actual_revenue
FROM bookings
WHERE paymentStatus = 'completed' OR paymentStatus = '1';

-- Verify organizer balances
SELECT 
    o.id AS organizer_id,
    o.amount AS current_balance,
    SUM(b.price - b.commission) AS expected_balance
FROM organizers o
LEFT JOIN bookings b ON b.organizer_id = o.id 
    AND (b.paymentStatus = 'completed' OR b.paymentStatus = '1')
GROUP BY o.id, o.amount;
```

## Common Issues and Fixes

### Issue 1: Transactions Not Showing
**Symptom**: Dashboard shows fewer transactions than bookings
**Cause**: Missing transactions for some bookings
**Fix**: Run `php fix_missing_transactions.php`

### Issue 2: Earnings Not Updating
**Symptom**: Lifetime earnings don't increase after payment
**Cause**: Earning model not updated in callback
**Fix**: Check PesapalController callback lines 559-568

### Issue 3: Organizer Balance Not Updating
**Symptom**: Organizer balance doesn't increase
**Cause**: `storeOrganizer()` not called
**Fix**: Check PesapalController callback line 596

### Issue 4: Monthly Charts Not Showing Data
**Symptom**: Monthly profit/income charts are empty
**Cause**: Transaction `payment_status` format mismatch
**Fix**: Ensure transactions have `payment_status = '1'` or `payment_status = 1`

## Verification Script

Run the verification script to check all calculations:

```bash
php verify_dashboard_calculations.php
```

This script will:
1. Check transaction counts
2. Check booking counts
3. Verify bookings have transactions
4. Check earning calculations
5. Verify organizer balances
6. Check monthly profit calculations
7. Verify Pesapal-specific data

## Testing Checklist

- [ ] Make a test Pesapal payment
- [ ] Verify transaction is created in database
- [ ] Check admin dashboard shows correct transaction count
- [ ] Check admin dashboard shows correct booking count
- [ ] Verify lifetime earnings increased
- [ ] Verify total profit increased
- [ ] Check organizer dashboard shows correct balance
- [ ] Check organizer dashboard shows correct transaction count
- [ ] Check organizer dashboard shows correct booking count
- [ ] Verify monthly charts show the new payment
- [ ] Run verification script and check for warnings

## Notes

- All queries use `payment_status = 1` or `paymentStatus = 'completed'` to filter completed payments
- MySQL handles type coercion between integer `1` and string `'1'` automatically
- Transactions are created with `payment_status` from `$booking->paymentStatus` (set to `1` in callback)
- Bookings are created with `paymentStatus = 'completed'` (string)
- Both formats are handled correctly by the queries