# Dashboard Queries Inventory

Generated on: 2026-04-14  
Scope: Dashboard controller endpoints, dashboard widgets, and dashboard services.

## Current Dashboard API Flow (from `dashboard.blade.php`)

The dashboard page currently calls these section endpoints directly:

- `dashboard.widgets.kpi-stats`
- `dashboard.widgets.chart-conversion`
- `dashboard.widgets.chart-payment-health`
- `dashboard.widgets.payment-trends`
- `dashboard.widgets.payment-gateway-distribution`
- `dashboard.widgets.chart-timeline`
- `dashboard.widgets.chart-cohort`
- `dashboard.widgets.package-options` (for package dropdown population)
- `dashboard.widgets.export-csv` (export action)

The frontend no longer uses `dashboard.widgets.all-data` in its active section-fetch path.
`buildSectionUrl()` now returns `null` for unknown sections and `fetchSectionData()` short-circuits in that case.
`fetchSectionData()` now sends `cache: 'no-store'` to avoid stale revalidation responses during filter changes.

All dashboard widget APIs now accept filters from either:
- flat query params (`date_range`, `product_plan`, `custom_start_date`, etc.)
- nested payload (`filters[...]`)

Controller-side filter normalization is handled by `DashboardWidgetController::resolveFilters()`.

## Runtime / Performance Notes (current)

- Dashboard cache is intentionally disabled in controller and widget layers for fresh-data debugging.
- `DashboardMetricsService` now executes DB queries directly (no `Cache::remember`) to avoid stale/locked cache behavior.
- Slow-path timing logs (`>= 1200ms`) are now emitted from:
  - `DashboardWidgetController::getKpiStats`
  - `DashboardWidgetController::getChartDataByType`
  - `DashboardMetricsService::getOverallMetrics`
  - `ConversionVsRetentionChart::getData`
  - `SubscriberTimelineChart::getData`
  - `CohortRetentionChart::getData`
- `QueryService::getAppId()` now returns the cached app_id after first resolution within the request.

## 1) `app/Services/DashboardMetricsService.php`

### Query 1: Conversions
```sql
SELECT COUNT(DISTINCT t.sms_cust_id) AS count
FROM tbl_customer_subscriptions t
INNER JOIN customers c ON c.sms_cust_id = t.sms_cust_id
WHERE t.status = 2
  -- current code uses t.status IN (2,7)
  AND c.status = 1
  AND t.created_at >= ?
  AND t.created_at < ?
  AND c.created_at >= ?
  AND c.created_at < ?
  [AND t.app_id = ?]
  [AND TRIM(UPPER(t.currency)) = TRIM(UPPER(?))]
  [AND t.sms_package_id = ?]
  [AND t.gateway_name = ?]
```

### Query 2: Registrations
```sql
SELECT COUNT(*) AS count
FROM customers c
WHERE c.status = 1
  AND c.created_at >= ?
  AND c.created_at < ?
  [AND c.app_id = ?]
```

### Query 3: Active Anytime
```sql
SELECT COUNT(DISTINCT t.sms_cust_id) AS active_cnt
FROM tbl_customer_subscriptions t
WHERE t.status = 2
  AND (t.start_date IS NULL OR t.start_date <= ?)
  AND (t.end_date IS NULL OR t.end_date >= ?)
  [AND t.app_id = ?]
  [AND TRIM(UPPER(t.currency)) = TRIM(UPPER(?))]
  [AND t.sms_package_id = ?]
  [AND t.gateway_name = ?]
```

### Query 4: Churned
```sql
WITH active_anytime AS (
  SELECT DISTINCT s.sms_cust_id
  FROM tbl_customer_subscriptions s
  WHERE s.status = 2
    AND (s.start_date IS NULL OR s.start_date <= ?)
    AND (s.end_date IS NULL OR s.end_date >= ?)
    [AND s.app_id = ?]
    [AND TRIM(UPPER(s.currency)) = TRIM(UPPER(?))]
    [AND s.sms_package_id = ?]
    [AND s.gateway_name = ?]
)
SELECT COUNT(DISTINCT c.sms_cust_id) AS churn_cnt
FROM tbl_customer_subscriptions c
JOIN active_anytime a ON a.sms_cust_id = c.sms_cust_id
WHERE c.status IN (3, 7)
  AND c.end_date BETWEEN ? AND ?
  [AND c.app_id = ?]
  [AND TRIM(UPPER(c.currency)) = TRIM(UPPER(?))]
  [AND c.sms_package_id = ?]
  [AND c.gateway_name = ?]
```

## 2) `app/Filament/Widgets/Dashboard/ConversionVsRetentionChart.php`

### Query 1: Batched conversions and registrations per period
```sql
WITH periods AS (...)
SELECT p.idx,
       COUNT(DISTINCT t.sms_cust_id) AS conversions,
       COUNT(DISTINCT c.sms_cust_id) AS registrations
FROM periods p
JOIN tbl_customer_subscriptions t
  ON t.created_at >= p.start_date
 AND t.created_at < (p.end_date + interval '1 second')
JOIN customers c
  ON c.sms_cust_id = t.sms_cust_id
 AND c.created_at >= p.start_date
 AND c.created_at < (p.end_date + interval '1 second')
 AND c.status = 1
WHERE t.status = 2
  AND t.app_id = ?
  [AND TRIM(UPPER(t.currency)) = TRIM(UPPER(?))]
  [AND t.sms_package_id = ?]
  [AND t.gateway_name = ?]
GROUP BY p.idx
```

### Query 2: Batched active-anytime per period
```sql
WITH periods AS (...)
SELECT p.idx,
       COUNT(DISTINCT t.sms_cust_id) AS active_cnt
FROM periods p
JOIN tbl_customer_subscriptions t
  ON (t.start_date IS NULL OR t.start_date <= p.end_date)
 AND (t.end_date IS NULL OR t.end_date >= p.start_date)
WHERE t.app_id = ?
  AND t.status = 2
  [AND TRIM(UPPER(t.currency)) = TRIM(UPPER(?))]
  [AND t.sms_package_id = ?]
  [AND t.gateway_name = ?]
GROUP BY p.idx
```

### Query 3: Batched churn per period
```sql
WITH periods AS (...)
SELECT p.idx,
       COUNT(DISTINCT c.sms_cust_id) AS churn_cnt
FROM periods p
JOIN tbl_customer_subscriptions c
  ON c.end_date BETWEEN p.start_date AND p.end_date
WHERE c.app_id = ?
  AND c.status IN (3, 7)
  AND EXISTS (
      SELECT 1
      FROM tbl_customer_subscriptions s
      WHERE s.app_id = ?
        AND s.sms_cust_id = c.sms_cust_id
        AND s.status = 2
        AND (s.start_date IS NULL OR s.start_date <= p.end_date)
        AND (s.end_date IS NULL OR s.end_date >= p.start_date)
  )
  [AND TRIM(UPPER(c.currency)) = TRIM(UPPER(?))]
  [AND c.sms_package_id = ?]
  [AND c.gateway_name = ?]
GROUP BY p.idx
```

## 3) `app/Filament/Widgets/Dashboard/SubscriberTimelineChart.php`

### Active query path (`getData`)
```sql
SELECT DATE(created_at) AS day, COUNT(*) AS total
FROM customers
WHERE status = 1
  AND created_at BETWEEN ? AND ?
  [AND app_id = ?]
GROUP BY day
```

```sql
SELECT DATE(s.end_date) AS day, COUNT(DISTINCT s.sms_cust_id) AS total
FROM tbl_customer_subscriptions s
WHERE s.status = 3
  AND s.end_date BETWEEN ? AND ?
  [AND s.app_id = ?]
  [AND TRIM(UPPER(s.currency)) = TRIM(UPPER(?))]
  [AND s.sms_package_id = ?]
GROUP BY day
```

```sql
WITH periods AS (...)
SELECT p.idx, COUNT(DISTINCT s.sms_cust_id) AS active_cnt
FROM periods p
JOIN tbl_customer_subscriptions s
  ON s.start_date <= p.end_date
 AND (s.end_date IS NULL OR s.end_date >= p.start_date)
WHERE s.status IN (2, 7)
  [AND s.app_id = ?]
  [AND TRIM(UPPER(s.currency)) = TRIM(UPPER(?))]
  [AND s.sms_package_id = ?]
GROUP BY p.idx
```

## 4) `app/Filament/Widgets/Dashboard/CohortRetentionChart.php`

### Query 1: Non-PG fallback per period
```sql
SELECT cs.package_title AS package_name, cs.sms_package_id,
       COUNT(DISTINCT cs.sms_cust_id) AS total_subscribers
FROM tbl_customer_subscriptions cs
WHERE cs.status = 2
  AND cs.start_date <= ?
  AND (cs.end_date >= ? OR cs.end_date IS NULL)
  [AND cs.app_id = ?]
  [AND cs.sms_package_id = ?]
  [AND TRIM(UPPER(cs.currency)) = TRIM(UPPER(?))]
  [AND (LOWER(cs.gateway_name) = LOWER(?) OR LOWER(cs.pg_name) = LOWER(?))]
GROUP BY cs.sms_package_id, cs.package_title
```

### Query 2: PG batched cohort load (`loadCohortRowsBatch`)
```sql
WITH periods AS (...)
SELECT p.idx, cs.package_title AS package_name, cs.sms_package_id,
       COUNT(DISTINCT cs.sms_cust_id) AS total_subscribers
FROM periods p
JOIN tbl_customer_subscriptions cs
  ON cs.start_date <= p.period_end
 AND (cs.end_date >= p.period_start OR cs.end_date IS NULL)
WHERE cs.status = 2
  [AND cs.app_id = ?]
  [AND cs.sms_package_id = ?]
  [AND TRIM(UPPER(cs.currency)) = TRIM(UPPER(?))]
  [AND (LOWER(cs.gateway_name) = LOWER(?) OR LOWER(cs.pg_name) = LOWER(?))]
GROUP BY p.idx, cs.sms_package_id, cs.package_title
```

## 5) `app/Filament/Widgets/Dashboard/PaymentHealthChart.php`

### Query 1: Payment health by gateway
```sql
SELECT tbl_order.gateway_name AS payment_gateway,
       CASE
           WHEN tbl_order.status = 2 THEN 'Success'
           WHEN tbl_order.status = 3 THEN 'Cancellation'
           ELSE 'Other'
       END AS status_text,
       COUNT(*) AS total_count
FROM tbl_order
WHERE tbl_order.status IN (2, 3)
  [AND tbl_order.created_at BETWEEN ? AND ?]
  [AND tbl_order.app_id = ?]
  [AND tbl_order.sms_package_id = ?]
  [AND TRIM(UPPER(tbl_order.currency)) = TRIM(UPPER(?))]
GROUP BY tbl_order.gateway_name, status_text
ORDER BY tbl_order.gateway_name ASC, status_text ASC
```

## 6) `app/Filament/Widgets/Dashboard/PaymentFailuresWidget.php`

### Query 1: Failed payments by gateway
```sql
SELECT COALESCE(gateway_name, 'Unknown') AS gateway_name,
       COUNT(*) AS failure_count
FROM tbl_customer_subscriptions
WHERE status IN (3, 7)
  AND created_at >= ?
  [AND tbl_customer_subscriptions.app_id = ?]
GROUP BY gateway_name
ORDER BY failure_count DESC
```

## 7) `app/Filament/Widgets/Dashboard/ChurnedCustomersWidget.php`

### Query 1: Churned customer list
```sql
SELECT c.id, c.user_name, c.status, c.created_at, c.updated_at, c.sms_cust_id,
       cd.id AS detail_id, cd.first_name, cd.last_name
FROM customers c
LEFT JOIN customer_details cd ON cd.customer_id = c.id
WHERE c.status != 1
  AND c.updated_at >= ?
ORDER BY c.updated_at DESC
LIMIT 100
```

### Query 2: Subscription aggregates for selected customers
```sql
SELECT sms_cust_id,
       MAX(CASE WHEN status = 2 THEN created END) AS last_payment_date,
       COUNT(CASE WHEN status = 3 AND created >= ? THEN 1 END) AS failed_count,
       AVG(CASE WHEN status = 2 THEN price END) AS avg_price
FROM tbl_customer_subscriptions
WHERE sms_cust_id IN (...)
  AND (status = 2 OR status = 3)
GROUP BY sms_cust_id
```

## 8) `app/Filament/Widgets/Dashboard/AtRiskCustomersWidget.php`

### Query 1: Active subscribed customers base list (page query)
```sql
SELECT c.id, c.user_name, c.status, c.subscription_status, c.created_at, c.updated_at, c.sms_cust_id,
       cd.id AS detail_id, cd.first_name, cd.last_name
FROM customers c
LEFT JOIN customer_details cd ON cd.customer_id = c.id
WHERE c.subscription_status = 1
  AND c.status = 1
  AND c.sms_cust_id IS NOT NULL
  [AND c.sms_cust_id IN (
      SELECT DISTINCT cs.sms_cust_id
      FROM tbl_customer_subscriptions cs
      LEFT JOIN tbl_packages tp ON tp.sms_package_id = cs.sms_package_id
      WHERE TRIM(UPPER(tp.currency_name)) = TRIM(UPPER(?))
  )]
  [AND c.sms_cust_id IN (
      SELECT DISTINCT sms_cust_id
      FROM tbl_customer_subscriptions
      WHERE sms_package_id = ?
  )]
ORDER BY c.updated_at DESC
LIMIT ? OFFSET ?
```

### Query 1b: Active subscribed customers total count
```sql
SELECT COUNT(*) AS total
FROM customers c
LEFT JOIN customer_details cd ON cd.customer_id = c.id
WHERE c.subscription_status = 1
  AND c.status = 1
  AND c.sms_cust_id IS NOT NULL
  [AND c.sms_cust_id IN (
      SELECT DISTINCT cs.sms_cust_id
      FROM tbl_customer_subscriptions cs
      LEFT JOIN tbl_packages tp ON tp.sms_package_id = cs.sms_package_id
      WHERE TRIM(UPPER(tp.currency_name)) = TRIM(UPPER(?))
  )]
  [AND c.sms_cust_id IN (
      SELECT DISTINCT sms_cust_id
      FROM tbl_customer_subscriptions
      WHERE sms_package_id = ?
  )]
```

### Query 2: Last successful payment per customer
```sql
SELECT sms_cust_id, MAX(created_at) AS last_payment_date
FROM tbl_customer_subscriptions
WHERE sms_cust_id IN (...)
  AND status = 2
  AND created_at <= ?
GROUP BY sms_cust_id
```

### Query 3: Failed payments per customer
```sql
SELECT sms_cust_id, COUNT(*) AS count
FROM tbl_customer_subscriptions
WHERE sms_cust_id IN (...)
  AND status = 3
  AND (start_date IS NULL OR start_date <= ?)
  AND (end_date IS NULL OR end_date >= ?)
GROUP BY sms_cust_id
```

### Query 4: Avg subscription amount per customer
```sql
SELECT cs.sms_cust_id, AVG(tp.price) AS avg_amount
FROM tbl_customer_subscriptions cs
LEFT JOIN tbl_packages tp ON tp.sms_package_id = cs.sms_package_id
WHERE cs.sms_cust_id IN (...)
  AND cs.status = 2
  AND (cs.start_date IS NULL OR cs.start_date <= ?)
  AND (cs.end_date IS NULL OR cs.end_date >= ?)
GROUP BY cs.sms_cust_id
```

### Query 5: Last content watch time
```sql
SELECT user_id, MAX(created) AS last_watched_date
FROM tbl_content_watched
WHERE user_id IN (...)
  AND created <= ?
GROUP BY user_id
```

### Query 6: Current period watch activity
```sql
SELECT user_id, COUNT(*) AS watch_count, AVG(duration) AS avg_duration
FROM tbl_content_watched
WHERE user_id IN (...)
  AND created BETWEEN ? AND ?
GROUP BY user_id
```

### Query 7: Previous period watch activity
```sql
SELECT user_id, COUNT(*) AS watch_count, AVG(duration) AS avg_duration
FROM tbl_content_watched
WHERE user_id IN (...)
  AND created BETWEEN ? AND ?
GROUP BY user_id
```

### Query 8: Currently watching count
```sql
SELECT user_id, COUNT(*) AS watching_count
FROM tbl_content_watched
WHERE user_id IN (...)
  AND status = ?
GROUP BY user_id
```

## 9) `app/Filament/Widgets/Dashboard/KpiCardsWidget.php`

### Active query path
- Uses `DashboardMetricsService::getComparisonMetrics(...)` for:
  - conversions
  - registrations
  - active_anytime
  - churned

### Additional query in `getCurrentChurn(...)`
```sql
SELECT
  COUNT(*) FILTER (
    WHERE EXISTS (
      SELECT 1
      FROM tbl_customer_subscriptions t
      WHERE t.sms_cust_id = c.sms_cust_id
        AND t.status = 2
        AND t.app_id = ?
        AND (t.start_date IS NULL OR t.start_date <= ?)
        AND (t.end_date IS NULL OR t.end_date >= ?)
        [filters]
    )
  ) AS retained_total,
  COUNT(*) FILTER (
    WHERE EXISTS (
      SELECT 1
      FROM tbl_customer_subscriptions t
      WHERE t.sms_cust_id = c.sms_cust_id
        AND t.status IN (3, 7)
        AND t.app_id = ?
        AND (t.start_date IS NULL OR t.start_date <= ?)
        AND (t.end_date IS NULL OR t.end_date >= ?)
        [filters]
    )
  ) AS churned_total
FROM customers c
WHERE c.created_at BETWEEN ? AND ?
  AND c.app_id = ?
```

### Sparkline helper queries
- `getCustomerCountsByDate`: base count + grouped daily counts from `customers`.
- `getSubscriberCountsByDate`: base count + grouped daily distinct subscribers from `tbl_customer_subscriptions`.
- `getRevenueByDate`: grouped SUM(price) by `DATE(created)` from `tbl_customer_subscriptions`.
- `getChurnCountsByDate`: grouped churn count from `customers` by `DATE(updated_at)`.

### Active KPI cards currently returned
- Registrations
- Conversions
- Conversion Rate
- Churn Rate
- Churn Rate Current
- Retention Rate Current

## 10) `app/Http/Controllers/Dashboard/DashboardWidgetController.php`

### Query 1: Payment trends (`getPaymentTrendsData`)
```sql
SELECT o.created_at::date AS payment_date,
       o.sms_package_id,
       COUNT(*) FILTER (WHERE o.status = 2) AS success,
       COUNT(*) FILTER (WHERE o.status = 3) AS cancellation
FROM tbl_order o
WHERE o.status IN (2,3)
  AND o.app_id = ?
  [AND o.created_at BETWEEN ? AND ?]
  [AND o.sms_package_id = ?]
  [AND TRIM(UPPER(o.currency)) = TRIM(UPPER(?))]
GROUP BY payment_date, o.sms_package_id
ORDER BY payment_date ASC
```

### Query 2: Gateway distribution (`getPaymentGatewayDistributionData`)
```sql
SELECT gateway_name, COUNT(*) AS count, COALESCE(SUM(price), 0) AS total_revenue
FROM tbl_customer_subscriptions
WHERE gateway_name IS NOT NULL
  AND gateway_name != ''
  [AND tbl_customer_subscriptions.app_id = ?]
  [AND tbl_customer_subscriptions.created_at BETWEEN ? AND ?]
  [AND tbl_customer_subscriptions.sms_package_id = ?]
  [AND TRIM(UPPER(tbl_customer_subscriptions.currency)) = TRIM(UPPER(?))]
  AND status IN (2,7)
GROUP BY gateway_name
ORDER BY count DESC
```

### Filter handling behavior
- Package filter on `tbl_order` paths uses `o.sms_package_id`.
- Package filter on `tbl_customer_subscriptions` paths uses `tbl_customer_subscriptions.sms_package_id`.
- Package ids are treated as string-compatible values (matches `sms_package_id` usage across tables).

### Query 4: Filter options - countries (`getFilterOptions`)
```sql
SELECT DISTINCT countries.name, countries.currency_code
FROM tbl_customer_subscriptions
JOIN countries ON tbl_customer_subscriptions.currency = countries.currency_code
WHERE tbl_customer_subscriptions.status = 2
  AND countries.name IS NOT NULL
  AND countries.currency_code IS NOT NULL
  AND countries.currency_code != ''
  AND tbl_customer_subscriptions.currency != ''
  [AND tbl_customer_subscriptions.app_id = ?]
ORDER BY countries.name ASC
```

### Query 5: Filter options - packages (`getFilterOptions`)
```sql
SELECT id, title
FROM tbl_packages
WHERE status = 1
  AND publish = 1
  [AND tbl_packages.app_id = ?]
ORDER BY title ASC
```

### Query 6: Region options (`getRegionOptions`)
```sql
SELECT DISTINCT countries.id, countries.name, countries.currency_code
FROM tbl_packages
JOIN countries ON TRIM(UPPER(tbl_packages.currency_code)) = TRIM(UPPER(countries.currency_code))
WHERE tbl_packages.status = 1
  AND tbl_packages.publish = 1
  AND countries.name IS NOT NULL
  AND countries.currency_code IS NOT NULL
  AND countries.currency_code != ''
  AND tbl_packages.currency_code != ''
  [AND tbl_packages.app_id = ?]
ORDER BY countries.name ASC
```

### Query 7: Package options (`getPackageOptions`)
```sql
SELECT DISTINCT tp.sms_package_id AS sms_package_id,
       tp.title AS package_title,
       tp.currency_name AS currency,
       tp.currency_id
FROM tbl_packages tp
WHERE tp.status = 1
  AND tp.publish = 1
  AND tp.title IS NOT NULL
  AND tp.title != ''
  [AND tp.app_id = ?]
ORDER BY tp.title ASC
```

## 11) `app/Http/Controllers/Dashboard/SavedDashboardController.php`

### Query 1: User Admin dashboard table
- Query builder:
```php
DB::table('customers')
  ->leftJoin('customer_detail', 'customers.id', '=', 'customer_detail.customer_id')
  ->select(..., DB::raw('COALESCE(TRIM(CONCAT(...)), customers.user_name) as full_name'))
  ->orderBy('customers.created_at', 'desc')
  ->cursor();
```

## 12) `app/Http/Controllers/Dashboard/QueryService.php`

### Query 1: Region → currency resolution
```php
Country::where('name', $regionName)->first();
```

### Query 2: Common filter clause
```sql
TRIM(UPPER(currency)) = TRIM(UPPER(?))
```

### Query 3: Base subscription query
```php
CustomerSubscription::whereIn('status', $statuses);
```

### Query 4: Base subscription query with date range
```php
...->whereBetween('created_at', [$startDate, $endDate]);
```

## 13) Snapshot-related dashboard queries (service-level, currently bypassed in active widget controller flow)

### `app/Services/DashboardSnapshotService.php`
- `DashboardSnapshot::query()->where('app_id', ...)->where('filter_hash', ...)->where('expires_at', '>', now())->first();`
- `DashboardSnapshot::query()->where('app_id', ...)->where('filter_hash', ...)->first();`
- `DashboardSnapshot::query()->updateOrCreate([...], [...]);`
- `DashboardSnapshot::query()->where('app_id', ...)->where('filter_hash', ...)->firstOrFail();`

### `app/Services/DashboardSnapshotPayloadBuilder.php`
- No direct DB queries in this class.
- It triggers widget methods that execute the queries listed above.

---

## Notes
- Brackets like `[AND ...]` mean optional filters appended based on selected dashboard filters.
- This inventory reflects active query paths (legacy query methods were removed).
- Dashboard widget/controller caching is currently disabled in code paths used by the dashboard page (temporary debugging state), so responses are recomputed per request.
- Snapshot reads/writes still exist in `DashboardSnapshotService`, but active `DashboardWidgetController` widget endpoints are currently running without snapshot short-circuiting.
- Functional indexes for normalized currency lookups were added in migration `2026_04_13_000003_add_dashboard_functional_indexes.php`.
- Slow-path timing debug logging is enabled for key dashboard endpoints/widgets during current performance investigation.