# Dashboard Queries (Updated)

This document captures the current KPI query logic and logging added during recent dashboard updates.

## File References
- `app/Filament/Widgets/Dashboard/KpiCardsWidget.php`
- `app/Http/Controllers/Dashboard/DashboardWidgetController.php`

## Current KPI Cards Enabled
- Registrations
- Conversions
- Conversion Rate
- Churn Rate
- Churn Rate Current
- Retention Rate Current

## Shared Inputs
All KPI methods use:
- `app_id` (tenant scoped)
- current period: `startDate` to `endDate`
- previous period: `previousStartDate` to `previousEndDate`
- optional filters: `package_id`, `gateway` (currency intentionally commented in conversion query)

Date behavior:
- Registration/conversion current period uses `created_at >= start` and `< end + 1 second`
- Churn/current-retention methods use date windows based on period strings passed into helper methods

## KPI Query Logic

### 1) Registrations
Method: `getRegistrationCount()`

Query shape:
- table: `customers`
- filters:
  - `status = 1`
  - `created_at >= start`
  - `created_at < end + 1 second`
  - `app_id = ?` when available

### 2) Conversions
Method: `getConversionCount()`

Implemented via CTE:
- `filtered_subs` from `tbl_customer_subscriptions`
  - `status = 2`
  - `created_at >= start`
  - `created_at < end + 1 second`
  - `app_id = ?`
  - optional `sms_package_id`, `gateway_name`
  - grouped by `sms_cust_id`
- joined to `customers c` on `sms_cust_id`
  - `c.status = 1`
  - `c.created_at >= start`
  - `c.created_at < end + 1 second`
- final: `COUNT(*)`

### 3) Conversion Rate
Computed in PHP:
- `conversionRate = (conversions / registrations) * 100` (0 when registrations is 0)
- `previousConversionRate` same formula using previous period values
- `% change` computed by `calculatePercentageChange(previousConversionRate, conversionRate)`

### 4) Churn Rate
Two helper methods:
- `getActiveAnytimeCount()`
- `getChurnedCount()`

`getActiveAnytimeCount()`:
- table: `tbl_customer_subscriptions t`
- `status = 2`
- overlap conditions:
  - `(start_date IS NULL OR start_date <= period_end)`
  - `(end_date IS NULL OR end_date >= period_start)`
- optional `app_id`, package, gateway
- `COUNT(DISTINCT t.sms_cust_id)`

`getChurnedCount()` (JOIN-based, replaced EXISTS-based):
- `tbl_customer_subscriptions c`
- `JOIN tbl_customer_subscriptions s ON s.sms_cust_id = c.sms_cust_id`
- join side filters:
  - `s.status = 2`
  - `s.start_date <= period_end`
  - `s.end_date >= period_start`
  - `s.app_id = ?` when available
- churn side filters:
  - `c.status IN (3, 7)`
  - `c.end_date BETWEEN period_start AND period_end`
  - `c.app_id = ?` when available
- optional package/gateway filters applied to both `c` and `s`
- final: `COUNT(DISTINCT c.sms_cust_id)`

Churn rate formula:
- `classicChurnRate = (churned / active_anytime) * 100` (0 when active_anytime = 0)

### 5) Churn Rate Current + Retention Rate Current
Method: `getCurrentChurn()`

Current implementation uses two explicit SQL blocks:

A) `retained_total`
- customer cohort subquery (`customers` in current period by `app_id`)
- joined with `tbl_customer_subscriptions t`
- filters:
  - `t.app_id = ?`
  - `t.status = 2`
  - `t.start_date <= end`
  - `(t.end_date IS NULL OR t.end_date >= start)`
  - optional extra filters (`package_id`, `gateway`) via `filterSQL`
- grouped by `t.sms_cust_id`
- outer `COUNT(*)`

B) `churned_total`
- same customer cohort subquery
- joined with `tbl_customer_subscriptions t`
- filters:
  - `t.app_id = ?`
  - `t.status IN (3, 7)`
  - `t.start_date <= end`
  - `t.end_date BETWEEN start AND end`
  - optional extra filters (`package_id`, `gateway`) via `filterSQL`
- grouped by `t.sms_cust_id`
- outer `COUNT(*)`

Current-period formulas:
- `totalCurrent = retained_total + churned_total`
- `currentChurnedRate = (churned_total / totalCurrent) * 100` (0 when totalCurrent = 0)
- `retentionRateCurrent = (retained_total / totalCurrent) * 100` (0 when totalCurrent = 0)

## Change/Trend Calculation
Method: `calculatePercentageChange($oldValue, $newValue)`
- if old = 0:
  - return `100` when new > 0
  - else return `0`
- else: `((new - old) / old) * 100`

## Logging Added
Method: `logQuery($label, $sql, $bindings)` logs:
- `sql`
- `bindings`
- `interpolated_sql`

### Log Keys
- `KpiCardsWidget::getConversionCount SQL`
- `KpiCardsWidget::getActiveAnytimeCount SQL`
- `KpiCardsWidget::getChurnedCount SQL`
- `KpiCardsWidget::getCurrentChurnRetained SQL`
- `KpiCardsWidget::getCurrentChurnChurned SQL`
- `KpiCardsWidget::RetentionRateCurrent data`

### Where to read logs
- `storage/logs/laravel.log`

## Important Controller Filter Fix
In `DashboardWidgetController::resolveFilters()`:
- `app_id` is now accepted from request filters
- supports both numeric and encrypted URL-safe app_id
- encrypted value is normalized using `AppIdEncryptionService::decryptFromUrl()`

This prevents DB-vs-UI mismatch caused by tenant scope differences.

## Notes
- Widget-level cache is currently disabled in `KpiCardsWidget` (`$cachedStats = null`).
- `Conversion vs Retention` chart is separate from KPI cards and has its own endpoint/query flow.

## ConversionVsRetentionChart Queries
File: `app/Filament/Widgets/Dashboard/ConversionVsRetentionChart.php`  
Method: `calculateAllPeriodsBatch(array $periods, array $baseQueryFilters): array`

### Dynamic filter fragments
- Conversion query (`t` alias):
  - currency: `AND TRIM(UPPER(t.currency)) = TRIM(UPPER(?))`
  - package: `AND t.sms_package_id = ?`
  - gateway: `AND t.gateway_name = ?`
- Churn query (`c` alias):
  - currently not applied in SQL text
- Active query (`t` alias):
  - currently not applied in SQL text

### Query 1: Registrations by month
```sql
SELECT DATE_TRUNC('month', c.created_at) AS period,
       COUNT(c.sms_cust_id) AS registrations
FROM customers c
WHERE c.status = 1
  AND c.app_id = ?
  AND c.created_at BETWEEN ? AND ?
GROUP BY period
ORDER BY period
```
Bindings:
- `[$appId, $startDateStr, $endDateStr]`

### Query 2: Conversions by month
```sql
SELECT DATE_TRUNC('month', t.created_at) AS period,
       COUNT(DISTINCT t.sms_cust_id) AS conversions
FROM tbl_customer_subscriptions t
JOIN customers c ON c.sms_cust_id = t.sms_cust_id
WHERE c.app_id = ?
  AND t.status IN (2, 7)
  AND t.created_at BETWEEN ? AND ?
  AND c.created_at BETWEEN ? AND ?
  {$convFilterSql}
GROUP BY period
ORDER BY period
```
Bindings:
- `array_merge([$appId, $startDateStr, $endDateStr, $startDateStr, $endDateStr], $convFilterBindings)`

### Query 3: Churned by month
```sql
SELECT DATE_TRUNC('month', c.end_date) AS period,
       COUNT(DISTINCT c.sms_cust_id) AS churned
FROM tbl_customer_subscriptions c
WHERE c.status IN (3, 7)
  AND c.app_id = ?
  AND c.created_at BETWEEN ? AND ?
  AND c.end_date BETWEEN ? AND ?
  AND NOT EXISTS (
        SELECT 1
        FROM tbl_customer_subscriptions s
        WHERE s.sms_cust_id = c.sms_cust_id
          AND s.app_id = ?
          AND s.status = 2
          AND s.created_at BETWEEN ? AND ?
          AND s.start_date <= ?
          AND (s.end_date IS NULL OR s.end_date >= ?)
  )
GROUP BY period
ORDER BY period
```
Bindings:
- `[$appId, $startDateOnly, $endDateOnly, $startDateOnly, $endDateOnly, $appId, $startDateOnly, $endDateOnly, $endDateOnly, $startDateOnly]`

### Query 4: Active users by month
```sql
WITH months AS (
    SELECT generate_series(
        DATE_TRUNC('month', CAST(? AS date)),
        DATE_TRUNC('month', CAST(? AS date)),
        interval '1 month'
    ) AS month_start
),
user_groups AS (
    SELECT m.month_start,
           c.sms_cust_id
    FROM months m
    JOIN tbl_customer_subscriptions c
      ON c.app_id = ?
     AND c.start_date >= m.month_start
     AND c.created_at BETWEEN ? AND ?
    GROUP BY m.month_start, c.sms_cust_id
)
SELECT month_start AS period,
       COUNT(sms_cust_id) AS active_users
FROM user_groups
GROUP BY month_start
ORDER BY month_start
```
Bindings:
- `[$startDateOnly, $endDateOnly, $appId, $startDateOnly, $endDateOnly]`

### Per-period formulas
- `conversionRate = regCount > 0 ? round((convCount / regCount) * 100, 2) : 0.0`
- `churnRate = activeCount > 0 ? round((churnCount * 100.0) / activeCount, 2) : 0.0`
- `conversions[index] = max(0.0, min(100.0, conversionRate))`
- `retentions[index] = max(0.0, min(100.0, round(100.0 - churnRate, 2)))`