# KPI Cards App_ID Filtering Audit

## Summary
All 6 KPI cards are properly using `session app_id` filtering. Below is a detailed breakdown:

---

## ✅ 1. Registrations Card

**Query Location:** Line 71 (`KpiCardsWidget.php`)

**Query Type:** Eloquent Model Query
```php
$registrationsQuery = Customer::where('status', 1);
```

**App_ID Filtering:** ✅ **AUTOMATIC via AppIdScope**
- `Customer` model has `AppIdScope` applied (line 21 in `Customer.php`)
- AppIdScope automatically filters by `session('app_id')` or `Auth::user()->app_id`
- **Status: SECURE** ✅

---

## ✅ 2. Conversions Card

**Query Location:** Line 128 (`KpiCardsWidget.php`)

**Query Type:** Eloquent Query Builder with JOIN
```php
$combinedConversionsQuery = CustomerSubscription::join('customers as c', ...)
    ->where('tbl_customer_subscriptions.status', 2);
```

**App_ID Filtering:** ✅ **EXPLICIT + AUTOMATIC**
- **Explicit filter:** Line 134-136
  ```php
  if ($appId !== null) {
      $combinedConversionsQuery->where('tbl_customer_subscriptions.app_id', $appId);
  }
  ```
- `CustomerSubscription` model also has `AppIdScope` applied (line 20 in `CustomerSubscription.php`)
- **Status: SECURE** ✅ (Double protection: explicit + scope)

---

## ✅ 3. Retained Subscribers Card

**Query Location:** Lines 293-331 (Current Period), Lines 337-375 (Previous Period)

**Query Type:** Raw SQL Query
```php
$currentPeriodSQL = "
    WITH latest_subscription AS (
        SELECT DISTINCT ON (subscriber_id) ...
        FROM tbl_customer_subscriptions
        WHERE app_id = ?  -- ✅ CTE filter
        ...
    )
    SELECT COUNT(DISTINCT t.subscriber_id) as count
    FROM tbl_customer_subscriptions t
    ...
    WHERE t.app_id = ?  -- ✅ Main query filter
    ...
";
```

**App_ID Filtering:** ✅ **EXPLICIT in Raw SQL**
- CTE filter: Line 304 (`WHERE app_id = ?`)
- Main query filter: Line 311 (`WHERE t.app_id = ?`)
- Bindings: Lines 323-324 (`[$appId], [$appId]`)
- **Status: SECURE** ✅

---

## ✅ 4. Churn Rate Card

**Query Location:** 
- Active at Start: Lines 401-421
- Ended in Period: Lines 425-445
- Previous Period Active at Start: Lines 453-472
- Previous Period Ended: Lines 474-492

**Query Type:** Raw SQL Queries
```php
// Active at Start
$activeAtStartSQL = "
    SELECT COUNT(DISTINCT subscriber_id) as count
    FROM tbl_customer_subscriptions t
    ...
    WHERE t.app_id = ?  -- ✅ Filter applied
    ...
";

// Ended in Period
$endedInPeriodSQL = "
    SELECT COUNT(DISTINCT subscriber_id) as count
    FROM tbl_customer_subscriptions t
    ...
    WHERE t.app_id = ?  -- ✅ Filter applied
    ...
";
```

**App_ID Filtering:** ✅ **EXPLICIT in Raw SQL**
- All 4 queries have `WHERE t.app_id = ?` filters
- Bindings include `$appId` parameter
- **Status: SECURE** ✅

---

## ✅ 5. Conversion Rate Card

**Query Location:** Line 232 (Calculated metric)

**Calculation:** 
```php
$conversionRate = $totalActiveUsers > 0 
    ? round(($conversions / $totalActiveUsers) * 100, 2) 
    : 0.0;
```

**App_ID Filtering:** ✅ **INHERITED from dependencies**
- Depends on:
  - `$conversions` → Uses Conversions query (✅ has app_id filter)
  - `$totalActiveUsers` → Uses Registrations query (✅ has AppIdScope)
- **Status: SECURE** ✅ (Both dependencies are filtered)

---

## ✅ 6. Retention Rate Card

**Query Location:** 
- Active at End: Lines 502-522
- Active at Start: Lines 401-421 (reused)
- Previous Period Active at End: Lines 526-546

**Query Type:** Raw SQL Queries
```php
// Active at End
$activeAtEndSQL = "
    SELECT COUNT(DISTINCT subscriber_id) as count
    FROM tbl_customer_subscriptions t
    ...
    WHERE t.app_id = ?  -- ✅ Filter applied
    ...
";

// Previous Active at End
$previousActiveAtEndSQL = "
    SELECT COUNT(DISTINCT subscriber_id) as count
    FROM tbl_customer_subscriptions t
    ...
    WHERE t.app_id = ?  -- ✅ Filter applied
    ...
";
```

**App_ID Filtering:** ✅ **EXPLICIT in Raw SQL**
- All queries have `WHERE t.app_id = ?` filters
- Bindings include `$appId` parameter
- **Status: SECURE** ✅

---

## 📊 Chart Helper Methods (Used by KPI Cards)

### `getCustomerCountsByDate()` (Line 1325)
- Uses `Customer::where()` → ✅ **AppIdScope applied**
- **Status: SECURE** ✅

### `getSubscriberCountsByDate()` (Line 1358)
- Uses `CustomerSubscription::where()` → ✅ **AppIdScope applied**
- **Status: SECURE** ✅

### `getRevenueByDate()` (Line 1429)
- Uses `CustomerSubscription::selectRaw()` → ✅ **AppIdScope applied**
- **Status: SECURE** ✅

### `getChurnCountsByDate()` (Line 1484)
- Uses `Customer::selectRaw()` → ✅ **AppIdScope applied**
- **Status: SECURE** ✅

---

## 🔒 App_ID Source

All queries use `app_id` from:
```php
$appId = $queryService->getAppId();  // Line 40, 257
```

Which retrieves from:
- `QueryService::getAppId()` → `QueryHelper::getAppId()`
- `QueryHelper::getAppId()` → `session('app_id')` or `Auth::user()->app_id`

---

## ✅ Final Verdict

**ALL 6 KPI CARDS ARE SECURE** ✅

1. ✅ **Registrations** - AppIdScope (automatic)
2. ✅ **Conversions** - Explicit + AppIdScope (double protection)
3. ✅ **Retained Subscribers** - Explicit in raw SQL (2 filters: CTE + main)
4. ✅ **Churn Rate** - Explicit in raw SQL (4 queries, all filtered)
5. ✅ **Conversion Rate** - Inherited from filtered dependencies
6. ✅ **Retention Rate** - Explicit in raw SQL (2 queries, all filtered)

**All chart helper methods also use AppIdScope** ✅

---

## 📝 Notes

- Eloquent queries (`Customer::`, `CustomerSubscription::`) automatically filter via `AppIdScope`
- Raw SQL queries (`DB::selectOne()`) explicitly include `WHERE app_id = ?` filters
- All `app_id` values come from `session('app_id')` or `Auth::user()->app_id`
- No queries are missing `app_id` filtering