# Analytics System Implementation Guide

## ✅ What I've Implemented

### Backend
1. **Created `/api/includes/functionsAnalytics.php`** - Complete analytics functions
2. **Updated `/api/api.php`** - Added analytics endpoint and include statement
3. **Role-based access control** - Admins see all venues, Owners see only their venues

### Frontend
1. **Updated `/src/lib/api.ts`** - Added TypeScript types and `fetchAnalytics()` function
2. **Updated `/src/app/dashboard/analytics/page.tsx`** - Complete analytics dashboard with real API integration

## ⚠️ Important: Booking Status Workflow

**Analytics only count COMPLETED bookings!**

Your booking workflow should be:
1. **Pending** → User places booking, awaiting confirmation
2. **Booked** → Booking confirmed, but not yet completed (can still be cancelled)
3. **Completed** → Booking has been fulfilled (user showed up and played)
4. **Cancelled** → Booking was cancelled

**Only bookings with `status = 'completed'` contribute to revenue analytics.**

This means you need a way to mark bookings as "completed" after they happen. You can either:
- Manually update bookings to "completed" after the booking time has passed
- Create a cron job to auto-complete bookings after their scheduled time
- Add a "Mark as Completed" button in your dashboard

## 📋 What You Need to Do

### 1. Database Indexes (OPTIONAL but RECOMMENDED for Performance)

Run these SQL commands in your database to improve query performance:

```sql
-- Performance-critical indexes
CREATE INDEX idx_bookings_status_date ON pitches_bookings(status, date);
CREATE INDEX idx_bookings_venue_status ON pitches_bookings(pitch_id, status, date);
CREATE INDEX idx_pitches_venue ON pitches(venue_id);
CREATE INDEX idx_venues_staff_user ON venues_staff(user_id);
CREATE INDEX idx_bookings_timestamp ON pitches_bookings(timestamp);
CREATE INDEX idx_bookings_analytics ON pitches_bookings(status, date, pitch_id);
```

### 2. Test the Analytics System

#### For Venue Owners:
1. Log into the dashboard as a venue owner
2. Navigate to `/dashboard/analytics`
3. You should see:
   - Total Revenue, Net Earnings, Commission, Total Bookings
   - Popular Time Slots
   - Occupancy Rate (circular progress)
   - Most Booked Pitches
   - Date range filters (Today, Last 7 Days, Last 30 Days)

#### For Admins:
1. Log into the dashboard as an admin
2. Navigate to `/dashboard/analytics`
3. You should see everything above PLUS:
   - **Revenue by Venue** section showing all venues with commission breakdown

### 3. API Endpoint

The analytics endpoint is now available at:
```
POST /api/analytics/fetch
```

**Request Body:**
```json
{
  "date_range": "last_30_days",  // Options: "today", "last_7_days", "last_30_days", "custom"
  "venue_id": 1,                  // Optional: filter by specific venue (admin only)
  "start_date": "2026-04-01",     // Optional: for custom date range
  "end_date": "2026-04-30"        // Optional: for custom date range
}
```

**Response:**
```json
{
  "success": true,
  "response": "Analytics retrieved successfully",
  "data": {
    "overview": {
      "total_bookings": 523,
      "total_revenue": 18920.00,
      "commission": 1892.00,
      "net_earnings": 17028.00
    },
    "venue_breakdown": [...],  // Admin only, null for owners
    "popular_time_slots": [...],
    "popular_pitches": [...],
    "occupancy_rate": {
      "booked_slots": 523,
      "total_available_slots": 1080,
      "occupancy_percentage": 48.43
    },
    "role": "owner"  // or "admin"
  }
}
```

## 🎯 Key Features

### Role-Based Access
- **Admin (role_id = 4)**: Can see all venues and filter by specific venue
- **Owner (role_id = 2)**: Can only see their assigned venues

### Metrics Provided
1. **Overview**
   - Total Revenue (from **completed** bookings only)
   - Commission (10% of revenue)
   - Net Earnings (revenue - commission)
   - Total Bookings (count of **completed** bookings only)

2. **Popular Time Slots**
   - Top 10 most booked time slots
   - Booking count and revenue per slot

3. **Popular Pitches**
   - Top 10 most booked pitches
   - Venue name, pitch type, booking count, revenue

4. **Occupancy Rate**
   - Booked slots vs total available slots
   - Percentage calculation (assumes 12 slots per day per pitch)

5. **Venue Breakdown (Admin Only)**
   - Revenue per venue
   - Commission owed per venue
   - Booking count per venue

### Date Filters
- **Today**: Current date only
- **Last 7 Days**: Past week
- **Last 30 Days**: Past month (default)
- **Custom Range**: Select custom start and end dates with date pickers ✨

## 🔒 Security & Data Integrity

- ✅ Role validation on every request
- ✅ Venue ownership verification for owners
- ✅ SQL injection prevention using `mysqli_real_escape_string`
- ✅ **Only completed bookings (`status = 'completed'`) are included in analytics**
  - Bookings with `status = 'booked'` are NOT counted (they can still be cancelled)
  - Bookings with `status = 'pending'` are NOT counted
  - Bookings with `status = 'cancelled'` are NOT counted
  - **Only finalized, completed bookings contribute to revenue metrics**

## 🚀 Performance Optimization (Future)

### Caching Strategy
Consider implementing Redis/Memcached caching:
```php
// Cache analytics for 10 minutes
$cache_key = "analytics_{$user_id}_{$date_range}_{$venue_id}";
$cached = getCachedData($cache_key);
if($cached !== false){
    return $cached;
}
// ... fetch data ...
setCachedData($cache_key, $data, 600);
```

### Precomputed Commission Table
For large-scale deployments, consider creating a `venue_monthly_commissions` table with triggers to precompute monthly data. See the design document for SQL schema.

## 📊 Testing Checklist

- [ ] Create some completed bookings in the database
- [ ] Log in as venue owner and view analytics
- [ ] Log in as admin and view analytics
- [ ] Test date range filters (Today, Last 7 Days, Last 30 Days)
- [ ] Verify commission calculation (should be 10% of revenue)
- [ ] Verify occupancy rate calculation
- [ ] Check that owners only see their venues
- [ ] Check that admins see all venues in "Revenue by Venue" section
- [ ] Test with no data (should show "No data available")

## 🐛 Troubleshooting

### "No data available" everywhere
- Make sure you have bookings with `status = 'completed'` in your database
- **Important**: Bookings with `status = 'booked'` or `'pending'` will NOT appear in analytics
- Check that the completed bookings are within your selected date range
- Verify venue ownership is correctly set up

### "Access denied" error
- Check user role_id (must be 2 for owner or 4 for admin)
- For owners, verify they have venues assigned in `venues_staff` table

### Performance issues
- Run the recommended database indexes
- Consider implementing caching for frequently accessed data
- Check slow query log for optimization opportunities

## 📝 Notes

- The system uses **10% commission** as the platform fee
- Occupancy rate assumes **12 available slots per day per pitch** (8am-8pm)
- All revenue calculations use `TIMESTAMPDIFF` to calculate booking duration from time slots
- The frontend automatically refreshes when date range changes

## 🎉 You're Done!

The analytics system is now fully functional. Navigate to `/dashboard/analytics` in your dashboard to see it in action!