# Dashboard Page ES Queries (v1)

Source of truth:
- Service: `app/Services/Elasticsearch/DashboardPageElasticsearchService.php`
- Controller: `app/Http/Controllers/Dashboard/DashboardWidgetController.php`
- Index: `customer_subscription_v1`

## ES-backed service methods
- `fetchRegistrationCount(...)`
- `fetchConversionCount(...)`
- `fetchActiveAnytimeCount(...)`
- `fetchActiveSubscribersAtDate(...)`
- `fetchChurnedCount(...)`
- `fetchPaymentHealth(...)`
- `fetchPaymentTrends(...)`
- `fetchPaymentGatewayDistribution(...)`
- `fetchPaymentFailures(...)`
- `fetchChurnedCustomers(...)`
- `fetchTopSubscriptionPackageTitles(...)`
- `fetchSubscriptionSeriesByPackageTitle(...)`
- `fetchDateHistogramCardinality(...)`

## API endpoints
- `GET /admin/api/dashboard/widgets/kpi-stats`
- `GET /admin/api/dashboard/widgets/charts/conversion`
- `GET /admin/api/dashboard/widgets/charts/payment-health`
- `GET /admin/api/dashboard/widgets/charts/timeline`
- `GET /admin/api/dashboard/widgets/charts/cohort`
- `GET /admin/api/dashboard/widgets/payment-trends`
- `GET /admin/api/dashboard/widgets/payment-gateway-distribution`
- `GET /admin/api/dashboard/widgets/payment-failures`
- `GET /admin/api/dashboard/widgets/churned-customers`
- `GET /admin/api/dashboard/widgets/at-risk-customers`
- `GET /admin/api/dashboard/widgets/all-data`

## Notes
- Date filtering must use service-compatible date formats for mapped date fields.
- Package labels should be resolved via package title helpers, not raw package id fallback unless title is missing.
- Aggregation-only dashboard ES calls now automatically set `track_total_hits=false` at execution time when request body has `size: 0` and `aggs`.
- Timeline chart ES mode now computes **Active Subscribers** using stock semantics:
  - `active(t) = active_at_range_start + starts(t) - ended_before_or_on(t-1)`
  - Implemented with:
    - point-in-time base count via `fetchActiveSubscribersAtDate(...)`
    - daily `subscription_start_date` histogram
    - daily `subscription_end_date` histogram
- Timeline no longer falls back to the expensive SQL timeline path when ES active series appears low; it logs and keeps ES response.
- Payment trends histogram now uses adaptive granularity:
  - `day` for ranges `<= 90` days
  - `week` for ranges `91..730` days
  - `month` for ranges `> 730` days
- ES query profiling logs are emitted from `DashboardElasticsearchService::query(...)`:
  - `dashboard.es_query`
  - `dashboard.es_query_slow`
  - `dashboard.es_query_failed`
  - slow threshold env: `DASHBOARD_ES_SLOW_MS` (default: `1200` ms)