# 📄 System-Wide Data, Query & Field Mapping Documentation

> **Last Updated:** 2026-04-27 | **Platform:** SMSV2 Admin  
> **Data Sources:** PostgreSQL (primary), Elasticsearch (analytics), Laravel Cache (perf layer)

---

## Table of Contents

1. [Architecture Overview](#architecture-overview)
2. [Dashboard](#1-dashboard)
3. [Subscriber 360](#2-subscriber-360)
4. [Billing & Invoices](#3-billing--invoices)
5. [Reports](#4-reports)
6. [Support Desk](#5-support-desk)
7. [Back Office](#6-back-office)
8. [Products & Packages](#7-products--packages)
9. [Dunning Management](#8-dunning-management)
10. [Master Field Inventory](#9-master-field-inventory)
11. [Master Query Repository](#10-master-query-repository)

---

## Architecture Overview

```mermaid
graph TD
    FE["Frontend (Blade + JS)"] --> API["API Controllers"]
    API --> ES["Elasticsearch Services"]
    API --> PG["PostgreSQL (DB::select / Eloquent)"]
    API --> CACHE["Laravel Cache"]
    ES -->|fallback| PG
    CACHE -->|miss| ES
    CACHE -->|miss| PG
```

**Key Patterns:**
- **Dual-source strategy:** ES first → PostgreSQL fallback
- **Tenant isolation:** All queries scoped by `app_id`
- **Security:** `app_id` encrypted in URLs via `AppIdEncryptionService`
- **Auth:** Session-based (web) + Bearer token (API/AJAX)
- **Caching:** Laravel Cache (300s default) + CDN headers

---

## 1. Dashboard

**Route:** `/admin/dashboard`  
**Controller:** [DashboardWidgetController.php](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/Dashboard/DashboardWidgetController.php)  
**ES Service:** `DashboardPageElasticsearchService`, `DashboardElasticsearchService`

### 1.1 Global Filters

| Filter | Parameter | Type | Source |
|--------|-----------|------|--------|
| Date Range | `start_date`, `end_date` | date | Request |
| Region/Currency | `currency` | string | `countries.currency_code` via `tbl_customer_subscriptions` |
| Package/Plan | `package_id` / `sms_package_id` | int | `tbl_packages.sms_package_id` |
| App ID | `app_id` | encrypted int | Session / URL |

### 1.2 KPI Stats Widget

**Endpoint:** `GET /admin/api/dashboard/widgets/kpi-stats`  
**Data Source:** ES → PostgreSQL fallback

| UI Field | Backend Key | DB Table | DB Column | Type |
|----------|-------------|----------|-----------|------|
| Total Subscribers | `total_subscribers` | `tbl_customer_subscriptions` | `COUNT(DISTINCT subscriber_id)` | int |
| Active Subscribers | `active_subscribers` | `tbl_customer_subscriptions` | `COUNT WHERE status=2` | int |
| Total Revenue | `total_revenue` | `tbl_customer_subscriptions` | `SUM(price)` | float |
| Avg Revenue/User | `arpu` | computed | `total_revenue / active_subscribers` | float |
| Churn Rate | `churn_rate` | computed | % cancelled vs total | float |
| New Subscribers | `new_subscribers` | `tbl_customer_subscriptions` | `COUNT WHERE created_at in range` | int |

**Filters Applied:** `app_id`, `start_date/end_date`, `sms_package_id`, `currency`

### 1.3 Payment Trends Chart (Series)

**Endpoint:** `GET /admin/api/dashboard/widgets/chart/payment-trends`  
**Data Source:** ES → PostgreSQL fallback

```sql
SELECT o.created_at::date AS payment_date,
       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
```

| UI Field | Key | Type |
|----------|-----|------|
| Date Labels | `labels` | string[] (M d, Y) |
| Success Count | `success` | int[] |
| Cancellation Count | `cancellation` | int[] |

### 1.4 Payment Gateway Distribution

**Endpoint:** `GET /admin/api/dashboard/widgets/chart/gateway-distribution`  
**Data Source:** ES → PostgreSQL fallback, **Cached 300s**

```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 status IN (2,7)
  [AND app_id = ?] [AND created_at BETWEEN ? AND ?]
  [AND sms_package_id = ?] [AND TRIM(UPPER(currency)) = TRIM(UPPER(?))]
GROUP BY gateway_name ORDER BY count DESC
```

| UI Field | Key | Type |
|----------|-----|------|
| Gateway Label | `label` | string |
| Revenue % | `percent` | float |
| Revenue Total | `total` | float |

### 1.5 Other Dashboard Widgets

| Widget | Endpoint Suffix | Data Source | Key Fields |
|--------|----------------|-------------|------------|
| Cohort Retention | `chart/cohort` | ES/DB | `labels`, `datasets` |
| Conversion vs Retention | `chart/conversion` | ES/DB | `labels`, `datasets` |
| Payment Health | `chart/payment_health` | ES/DB | `labels`, `datasets` |
| Subscriber Timeline | `chart/timeline` | ES/DB | `labels`, `datasets` |
| Churned Customers | `churned-customers` | `ChurnedCustomersWidget` | `customers[]` |
| At-Risk Customers | `at-risk-customers` | `AtRiskCustomersWidget` | `customers[]`, `total` |
| Payment Failures | `payment-failures` | `PaymentFailuresWidget` | `failures[]` |

### 1.6 Filter Options Endpoints

| Endpoint | Returns | DB Query |
|----------|---------|----------|
| `GET .../filter-options` | `regions{}`, `packages{}` | `tbl_customer_subscriptions → countries`, `tbl_packages` |
| `GET .../region-options` | `regions{}`, `region_currency_ids{}` | `tbl_packages → countries` |
| `GET .../package-options` | `packages{}`, `package_list[]` | `tbl_packages` or `tbl_order` (PRODUCT model) |

---

## 2. Subscriber 360

**Route:** `/admin/subscriber360`  
**Controller:** [Subscriber360ApiController.php](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/Subscriber360/Subscriber360ApiController.php)  
**ES Service:** `Subscriber360ElasticsearchService`

### 2.1 Filters

| Filter | Parameter | Type | Notes |
|--------|-----------|------|-------|
| Search | `search[value]` | string | Name, email, phone, sms_cust_id |
| Date Range | `date_from`, `date_to` | date | Scopes to subscription created_at |
| Status | `status` | int | 2=Active, 7=Renewed, etc. |
| App ID | `app_id` | encrypted int | Tenant isolation |

### 2.2 Subscribers Table (DataTables)

**Endpoint:** `GET /admin/api/subscriber360/get-listing`

| UI Column | Backend Key | DB Source | Notes |
|-----------|-------------|-----------|-------|
| Customer ID | `sms_cust_id` | `customers.sms_cust_id` | |
| Name | `name` | `customers.first_name + last_name` | Computed |
| Email | `email` | `customers.email_id` | |
| Phone | `phone` | `customers.phone_number` | |
| Status | `status` | `tbl_customer_subscriptions.status` | Mapped to label |
| Package | `package_name` | `tbl_customer_subscriptions.package_name` | |
| Start Date | `start_date` | `tbl_customer_subscriptions.start_date` | |
| End Date | `end_date` | `tbl_customer_subscriptions.end_date` | |
| Price | `price` | `tbl_customer_subscriptions.price` | |
| Gateway | `pg_name` | `tbl_customer_subscriptions.gateway_name` | |

### 2.3 Customer Detail

**Endpoint:** `GET /admin/api/subscriber360/customer/{id}`

Returns: Customer profile, subscription history, order history, device info, activity timeline.

**Tables Joined:** `customers`, `tbl_customer_subscriptions`, `tbl_order`, `customer_devices`

### 2.4 Charts

| Chart | Endpoint | Fields |
|-------|----------|--------|
| Subscription Timeline | `charts/timeline` | `labels`, `new_subscribers`, `churned` |
| Revenue Trend | `charts/revenue` | `labels`, `revenue[]` |
| Status Distribution | `charts/status` | `labels`, `counts[]` |

---

## 3. Billing & Invoices

**Route:** `/admin/billing`  
**Controller:** [BillingWidgetController.php](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/Billing/BillingWidgetController.php)  
**ES Service:** `BillingElasticsearchService`

### 3.1 Filters

| Filter | Parameter | Type |
|--------|-----------|------|
| Date Range | `date_from`, `date_to` | date |
| Payment Gateway | `payment_type` | string |
| Status | `status` | int |
| Package | `package_id` | int |
| App ID | `app_id` | encrypted int |

### 3.2 Billing KPIs

**Endpoint:** `GET /admin/api/billing/dashboard-detail`

| KPI | Key | Source |
|-----|-----|--------|
| Total Revenue | `totalRevenue` | `SUM(tbl_customer_subscriptions.price) WHERE status IN (2,7)` |
| Total Transactions | `totalTransactions` | `COUNT(tbl_customer_subscriptions)` |
| Avg Transaction Value | `avgTransactionValue` | `AVG(price)` |
| Success Rate | `successRate` | `success / total * 100` |
| Failed Payments | `failedPayments` | `COUNT WHERE status=3` |
| Refunds | `totalRefunds` | `COUNT WHERE status=5` |

### 3.3 Invoices Table (DataTables)

**Endpoint:** `GET /admin/api/billing/invoices/get-listing`

| UI Column | Backend Key | DB Table.Column |
|-----------|-------------|-----------------|
| Invoice # | `invoice_number` | Computed: `INV-{sms_order_id}` |
| Customer | `customer_name` | `customers.first_name` |
| Email | `email` | `customers.email_id` |
| Amount | `amount` | `tbl_customer_subscriptions.price` |
| Currency | `currency` | `tbl_customer_subscriptions.currency` |
| Status | `status` | `tbl_customer_subscriptions.status` (mapped) |
| Gateway | `gateway` | `tbl_customer_subscriptions.gateway_name` |
| Transaction ID | `transaction_id` | `tbl_customer_subscriptions.trans_id` |
| Package | `package_name` | `tbl_customer_subscriptions.package_name` |
| Date | `created_at` | `tbl_customer_subscriptions.created_at` |

### 3.4 Gateway Distribution Chart

Same structure as Dashboard gateway distribution but scoped to billing filters.

---

## 4. Reports

**Route:** `/admin/reports`  
**Controller:** [ReportController.php](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/Reports/ReportController.php)  
**Data Source:** PostgreSQL only (no ES)

### 4.1 Global Report Filters

| Filter | Parameter | Type | Options |
|--------|-----------|------|---------|
| Date Range | `start_date`, `end_date` | date | Default: last 14 days |
| Payment Type | `payment_type` | string | Gateway names + 'all' |
| Package | `package_id` | int/string | Package IDs + 'all' |
| Subscription Date Field | `subscription_date_field` | string | `start_date`, `end_date`, `purchase_date` |
| Subscription Date Op | `subscription_date_operator` | string | `equal`, `greater`, `less`, `between` |
| Subscription Status | `subscription_status` | string | `2` (active), `7` (renewed) |
| Coupon Code | `coupon_code` | string | Specific coupon code |
| Converted Only | `converted_only` | bool | `true/false` |
| Limit | `limit` | int | Default 200, max 1000, 'all' for no limit |

### 4.2 Report Types & Fields

#### Subscription Report (`subscription_report`)

| Column Label | Key | Source |
|-------------|-----|--------|
| Package | `item_name` | `tbl_customer_subscriptions.package_name` |
| First Name | `first_name` | `customers.first_name` |
| Last Name | `last_name` | `customers.last_name` |
| Email | `email` | `customers.email_id` |
| Contact | `contact_no` | `customers.contact_no` |
| Start Date | `start_date` | `tbl_customer_subscriptions.start_date` |
| End Date | `end_date` | `tbl_customer_subscriptions.end_date` |
| Payment Status | `payment_status` | Computed CASE on `status` |
| State | `state` | `customers.state` |
| Country | `country` | `customers.country` |
| PG Name | `pg_name` | `tbl_customer_subscriptions.gateway_name` |
| PG Trans ID | `trans_id` | `tbl_customer_subscriptions.trans_id` |
| Discount | `discount_amount` | `tbl_order.coupon_amount` |
| Coupon Code | `coupon_code` | `tbl_order.coupon_code` |
| Trans Country | `b_country` | `tbl_order.country` |
| Username | `username` | `customers.username` |
| Created | `created` | `tbl_customer_subscriptions.created_at` |

**Query:** `tbl_customer_subscriptions → tbl_order → customers` (3-way JOIN)

#### Payment Report (`payment_report`)

| Column Label | Key | Source |
|-------------|-----|--------|
| Subscriber ID | `subscriber_id` | `tbl_customer_subscriptions.subscriber_id` |
| Order ID | `order_id` | `tbl_customer_subscriptions.order_id` |
| Package ID | `package_id` | `tbl_customer_subscriptions.sms_package_id` |
| Package Title | `package_title` | `tbl_customer_subscriptions.package_name` |
| Price | `price` | `tbl_customer_subscriptions.price` |
| Currency | `currency` | `tbl_customer_subscriptions.currency` |
| Billing State | `b_state` | `tbl_order.state` |
| PG Name | `pg_name` | `tbl_customer_subscriptions.gateway_name` |
| PG Trans ID | `PG_transaction_id` | `tbl_customer_subscriptions.trans_id` |
| Device | [device](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/Subscriber360/Subscriber360ApiController.php#1988-2102) | `CASE on tbl_order.platform` |
| Platform | `platform` | `CASE on tbl_customer_subscriptions.order_platform` |
| Start Date | `start_date` | `tbl_customer_subscriptions.start_date` |
| End Date | `end_date` | `tbl_customer_subscriptions.end_date` |
| Created | `created` | `tbl_customer_subscriptions.created_at` |

**Query:** `tbl_customer_subscriptions → tbl_order` (status IN 2,7)

#### Payment Failed Report (`payment_failed_report`)

| Column Label | Key | Source |
|-------------|-----|--------|
| Customer ID | `customer_id` | `customers.id` |
| Customer Name | `customer_name` | `CONCAT(first_name, last_name)` |
| Email | `email` | `customers.email_id` |
| Phone | `phone` | `customers.contact_no` |
| Payment Status | `payment_status` | CASE on `tbl_order.status` |
| Transaction ID | `transaction_id` | `tbl_order.transaction_id` |
| Package Name | `package_name` | `COALESCE(tbl_order.package_name, tbl_packages.title)` |
| Created | `created` | `tbl_order.created_at` |

**Query:** `tbl_order → tbl_packages → customers` (status=3)

#### Coupon Usage Report (`coupon_usage_report`)

| Column Label | Key | Source |
|-------------|-----|--------|
| User ID | `user_id` | `tbl_customer_subscriptions.sms_cust_id` |
| Email | `email` | `customers.email_id` |
| Contact | `contact` | `customers.phone_number` |
| First Name | `first_name` | `customers.first_name` |
| Platform | `platform` | `customer_devices.platform` |
| Coupon Code | `coupon_code` | `tbl_order.coupon_code` |
| Status | `status` | `tbl_customer_subscriptions.status` |
| Price | `price` | `tbl_customer_subscriptions.price` |
| Purchase Date | `purchase_date` | `tbl_order.created_at` |

**Query:** `tbl_order → tbl_customer_subscriptions → customers ← customer_devices`

#### Order Conversion Report (`order_conversion_report`)

| Column Label | Key | Source |
|-------------|-----|--------|
| Order ID | `sms_order_id` | `tbl_order.sms_order_id` |
| User ID | `user_id` | `tbl_order.sms_cust_id` |
| Email | `email` | `customers.email_id` |
| Contact | `contact` | `customers.phone_number` |
| Name | `first_name` | `customers.first_name` |
| Platform | `platform` | `customer_devices.platform` (LATERAL) |
| Price | `price` | `tbl_customer_subscriptions.price` |
| Purchased Date | `purchase_date` | `tbl_order.created_at` |
| Converted | `converted_label` | `Yes` when any subscription of the order has `status IN (2, 3, 7)` |

**Query:** One row per order via `DISTINCT ON (o.sms_order_id)` (paid subscription row preferred) + `LEFT JOIN LATERAL` for latest device

#### Total Plan Subscriber (`total_plan_subscriber`)

| Column | Key | Source |
|--------|-----|--------|
| Plan Name | `plan_name` | `COALESCE(cs.package_name, p.title)` |
| Total Subscribers | `total_subscribers` | `COUNT(DISTINCT subscriber_id)` |

#### Sourcewise Plan Subscribers (`sourcewise_plan_subscribers`)

Plan Name + Total Subscribers + Source (Web/Android/iOS/Unknown via CASE)

#### Expiry Count (`expiry_count`)

Plan Name + Source + Expiry Date + Expiring Count

#### User-wise Plan Expiry (`userwise_plan_expiry`)

Plan Name + Source + First Name + Email + Contact + Expiry Date + Expiring Count

---

## 5. Support Desk

**Route:** `/admin/support`  
**Controller:** [SupportWidgetController.php](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/Support/SupportWidgetController.php)  
**Data Source:** PostgreSQL only

### 5.1 Filters

| Filter | Parameter | Type | Options |
|--------|-----------|------|---------|
| Tab | `tab` | string | `unresolved`, `resolved`, `refunds`, `coupons`, `vouchers` |
| Date Range | `date_from`, `date_to` | date | |
| Search | `search[value]` | string | id, subject, ticket_id, sms_cust_id, assigned_name |
| App ID | `app_id` | encrypted int | |

### 5.2 Tickets Table (DataTables)

**Endpoint:** `GET /admin/api/support/tickets/get-listing`  
**Table:** `support_tickets`

| UI Column | Backend Key | DB Column | Type |
|-----------|-------------|-----------|------|
| ID | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326) | `support_tickets.id` | int |
| Ticket ID | `ticket_id` | `support_tickets.ticket_id` | string |
| Subject | `subject` | `support_tickets.subject` | string |
| Category | `category` | `support_tickets.category` | enum: BILLING, TECHNICAL, ACCOUNT, PRODUCT, REFUND, COUPON, VOUCHER, OTHER |
| Priority | `priority` | `support_tickets.priority` | enum: LOW, MEDIUM, HIGH, URGENT |
| Status | `status` | `support_tickets.status` | enum: OPEN, IN_PROGRESS, ESCALATED, RESOLVED, CLOSED |
| Created At | `created_dt` | `support_tickets.created_at` | datetime |
| Assigned To | `assigned_to` | `support_tickets.assigned_name` | string |
| Description | `description` | `support_tickets.description` | text |

**Tab Filters:**
- `unresolved` → `status IN ('OPEN', 'IN_PROGRESS', 'ESCALATED')`
- `resolved` → `status IN ('RESOLVED', 'CLOSED')`
- `refunds` → `category = 'REFUND'`
- `coupons` → `category = 'COUPON'`
- `vouchers` → `category = 'VOUCHER'`

### 5.3 KPIs

**Endpoint:** `GET /admin/api/support/dashboard-detail`

| KPI | Source Query |
|-----|-------------|
| Total Tickets | `COUNT(support_tickets) WHERE app_id = ?` |
| Open Tickets | `COUNT WHERE status = 'OPEN'` |
| In Progress | `COUNT WHERE status = 'IN_PROGRESS'` |
| Resolved | `COUNT WHERE status IN ('RESOLVED','CLOSED')` |
| Escalated | `COUNT WHERE status = 'ESCALATED'` |

### 5.4 Related Tables

| Table | Usage |
|-------|-------|
| `support_tickets` | Primary tickets storage |
| `ticket_history` | Status change audit trail (`ticket_id`, `old_status`, `new_status`, `changed_by`, `note`) |
| `customers` | Customer search for ticket creation |
| `client_users` | Assignment targets |

---

## 6. Back Office

**Route:** `/admin/backoffice`  
**Controller:** [BackOfficeWidgetController.php](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php)  
**ES Service:** `BackOfficeCouponAnalyticsService`

### 6.1 Email Templates Table

**Endpoint:** `GET /admin/api/back-office/email-templates/get-listing`  
**Table:** `email_sms_templates` (WHERE `type = 'email'`)

| UI Column | Backend Key | DB Column |
|-----------|-------------|-----------|
| Template Name | `template_name` | `template_name` / `name` |
| Subject | `subject` | `subject` / `email_subject` |
| Trigger | `trigger` | `trigger_type` |
| Status | `status` | `status` (1=Active, 3=Draft, 0=Inactive) |
| Priority | `priority` | `priority` |
| Updated At | `updated_at` | `updated_at` |

**Filter Chips:** `all`, `active` (status=1), `draft` (status=3), `archived` (status=0), `high-priority`  
**Search Columns:** `template_name`, `name`, `subject`, `email_subject`, `trigger_type`, `status`

### 6.2 SMS Templates Table

**Endpoint:** `GET /admin/api/back-office/sms-templates/get-listing`  
**Table:** `email_sms_templates` (WHERE `type = 'sms'`)

| UI Column | Backend Key | DB Column |
|-----------|-------------|-----------|
| Template Name | `template_name` | `template_name` / `name` |
| Message | `message` | `message` / `sms_message` / `content` (preview 50 chars) |
| Trigger | `trigger` | `trigger_type` |
| Status | `status` | `status` (1=Active, 3=Draft, 0=Inactive) |
| Updated At | `updated_at` | `updated_at` |

### 6.3 Coupons Table

**Endpoint:** `GET /admin/api/back-office/coupons/get-listing`  
**Table:** `coupans`

| UI Column | Backend Key | DB Column |
|-----------|-------------|-----------|
| Code | `code` | `coupans.code` |
| Name | `name` | `coupans.name` |
| Discount Type | `discount_type` | `coupans.discount_type` |
| Discount Value | `reedmed_reward` | `coupans.discount_val` / `reedmed_reward` |
| Status | `status` | `coupans.status` (1=Active, 2=Expired, 0=Inactive) |
| Start Date | `start_date` | `coupans.start_date` |
| End Date | `end_date` | `coupans.end_date` |

**Filter Chips:** `all`, `active`, `upcoming`, `inactive`, `expired`, `high-usage`  
**Search Columns:** `code`, `name`, `description`, `coupon_type`, `discount_type`, `status`

**Discount Mode Mapping**
- `1` => `FLAT`
- `2` => `PERCENTAGE`
- `3` => `FULL_AMOUNT`
- `4` => `FREE_DAYS`

**Discount Type Semantics**
- `FLAT` => Fixed amount discount for Coupon
- `PERCENTAGE` => Percentage discount for Coupon
- `FULL_AMOUNT` => Voucher provides full package amount
- `FREE_DAYS` => Voucher provides free subscription days

### 6.4 Vouchers Table

**Endpoint:** `GET /admin/api/back-office/vouchers/get-listing`  
**Table:** `vouchers`

| UI Column | Backend Key | DB Column |
|-----------|-------------|-----------|
| Code | `code` | `vouchers.voucher_code` |
| Type | `name` | `vouchers.voucher_type` |
| Value | `value` | `vouchers.amount` |
| Status | `status` | `vouchers.status` (active/expired/redeemed/inactive) |
| Redeemed At | `redeemed_at` | `vouchers.redeemed_at` |
| Expires At | `expires_at` | `vouchers.valid_until` |

**Filter Chips:** `all`, `active`, `redeemed`, `expired`, `unused`, `inactive`  
**Search Columns:** `voucher_code`, `voucher_type`, `notes`, `status`

### 6.5 Coupon Analytics

**Endpoint:** `GET /admin/api/back-office/coupon-analytics/global`  
**Data Source:** ES (`BackOfficeCouponAnalyticsService`) → PostgreSQL fallback

---

## 7. Products & Packages

**Route:** `/admin/products-packages`  
**Controller:** [ProductAdminWidgetController.php](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/ProductAdmin/ProductAdminWidgetController.php)  
**ES Service:** `ProductAdminElasticsearchService`

### 7.1 Products Table (DataTables)

**Endpoint:** `GET /admin/api/products-packages/products/datatables`  
**Tables:** `products → service_types`, `product_category_types`

| UI Column | Backend Key | DB Source |
|-----------|-------------|----------|
| Name | `name` | `products.name` |
| SKU | `sku` | `products.sku` |
| Category | `product_category` | `product_category_types.name` |
| Service Type | `service_type` | `service_types.name` |
| Created At | `created_at` | `products.created_at` |

**Search Fields:** `name`, `sku`, `description`, `product_category`

### 7.2 Packages Table (DataTables)

**Endpoint:** `GET /admin/api/products-packages/packages/datatables`  
**Table:** `tbl_packages`

| UI Column | Backend Key | DB Column |
|-----------|-------------|-----------|
| Title | `title` | `tbl_packages.title` |
| Period | `period` | `tbl_packages.period` |
| Price | `subscription_price` | Latest from `tbl_customer_subscriptions.price` |
| Start Date | `start_date` | `tbl_packages.start_date` |
| End Date | `end_date` | `tbl_packages.end_date` |
| Status | `status` | `tbl_packages.status` + `publish` |

**Search Fields:** `title`, `description`

### 7.3 Usage Dashboard Metrics

**Endpoint:** `GET /admin/api/products-packages/usage-dashboard`

| Metric | Key | Source |
|--------|-----|--------|
| Total Product Activations | `total_product_activations` | Computed from subscriptions |
| Top Product | `top_product.name`, `top_product.activations` | Most activated product |
| Avg Activation/User | `avg_activation_per_user` | Computed |
| Conversion Rate | `conversion_rate` | Computed |

### 7.4 Top Packages Performance

**Endpoint:** `GET /admin/api/products-packages/data`  
**Data Source:** ES (`ProductAdminElasticsearchService`) → PostgreSQL fallback

---

## 8. Dunning Management

**Route:** `/admin/dunning`  
**Controller:** [DunningWidgetController.php](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/Billing/DunningWidgetController.php)  
**Data Source:** PostgreSQL (Eloquent via `DunningCampaign` model)

### 8.1 Filters

| Filter | Parameter | Type |
|--------|-----------|------|
| Status | `status` | int (1=active, 2=paused) |
| Campaign Type | `campaign_type` | string |
| Notification Channel | `notification_channel` | string |
| Date Range | `date_from`, `date_to` | date |
| Search | `search[value]` | string (campaign name) |

### 8.2 KPIs

**Endpoint:** `GET /admin/api/dunning/dashboard-detail`

| KPI | Key | Source |
|-----|-----|--------|
| Active Campaigns | `activeCampaigns` | `COUNT(dunning_campaigns WHERE status=1)` |
| Failed Payments | `failedPayments` | `COUNT(tbl_customer_subscriptions WHERE status=8)` |
| Suspended Accounts | `suspendedAccounts` | `COUNT(tbl_customer_subscriptions WHERE status=9)` |
| Recovery Rate | `recoveryRate` | `AVG(dunning_campaigns.recovery_rate)` |
| Total At Risk | `totalAtRisk` | `failed + suspended` |

### 8.3 Campaigns Table (DataTables)

**Endpoint:** `GET /admin/api/dunning/campaigns/get-listing`  
**Table:** `dunning_campaigns`

| UI Column | Backend Key | DB Column |
|-----------|-------------|-----------|
| Name | `name` | `dunning_campaigns.name` |
| Type | `type` | `dunning_campaigns.type` |
| Status | `status` | `dunning_campaigns.status` |
| Grace Period | `grace_period_days` | `dunning_campaigns.grace_period_days` |
| Retry Attempts | `retry_attempts` | `dunning_campaigns.retry_attempts` |
| Notification Channel | `notification_channel` | `dunning_campaigns.notification_channel` |
| Template Count | `template_count` | Computed from pivot (campaign_templates) |
| Customers Affected | `customers_affected` | `dunning_campaigns.customers_affected` |
| Recovery Rate | `recovery_rate` | `dunning_campaigns.recovery_rate` |
| Date Range | `start_date`, `end_date` | `dunning_campaigns.start_date/end_date` |

### 8.4 Campaign Details

**Endpoint:** `GET /admin/api/dunning/campaign/{id}/details`

Returns campaign data + associated templates with stage/delay configuration.

**Tables:** `dunning_campaigns`, `email_sms_templates` (via pivot `campaign_templates`)

---

## 9. Master Field Inventory

### Primary Database Tables

| Table | Key Columns | Used By |
|-------|-------------|---------|
| `tbl_customer_subscriptions` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `sms_cust_id`, `sms_order_id`, `sms_package_id`, `package_name`, `price`, `currency`, `status`, `gateway_name`, `trans_id`, `start_date`, `end_date`, `created_at`, `app_id`, `order_platform` | Dashboard, Billing, Reports, Sub360, Dunning |
| `tbl_order` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `sms_order_id`, `sms_cust_id`, `sms_package_id`, `package_name`, `status`, `price`, `currency`, `platform`, `transaction_id`, `coupon_code`, `coupon_amount`, `country`, `created_at`, `app_id` | Dashboard, Reports, Billing |
| `customers` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `sms_cust_id`, `first_name`, `last_name`, `email_id`, `phone_number`, `username`, `state`, `country`, `app_id`, `created_at` | Sub360, Reports, Support, Billing |
| `tbl_packages` | `sms_package_id`, `title`, `description`, `period`, `status`, `publish`, `currency_code`, `currency_name`, `currency_id`, `app_id`, `start_date`, `end_date`, `created_at` | Dashboard, Reports, Products |
| `support_tickets` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `ticket_id`, `app_id`, `sms_cust_id`, `subject`, `description`, `category`, `priority`, `status`, `assigned_to`, `assigned_name`, `resolved_at`, `resolution_note`, `created_at`, `updated_at` | Support |
| `ticket_history` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `ticket_id`, `old_status`, `new_status`, `changed_by`, `changed_by_type`, `note`, `created_at` | Support |
| `email_sms_templates` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `template_name`, `name`, `subject`, `email_subject`, `message`, `sms_message`, `content`, `type`, `trigger_type`, `status`, `priority`, `app_id`, `updated_at` | BackOffice, Dunning |
| `coupans` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `code`, `name`, `description`, `discount_type`, `discount_val`, `reedmed_reward`, `status`, `start_date`, `end_date`, `app_id` | BackOffice, Reports |
| `vouchers` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `voucher_code`, `voucher_type`, `amount`, `status`, `redeemed_at`, `valid_until`, `usage_count`, `notes`, `app_id` | BackOffice |
| `products` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `name`, `sku`, `description`, `service_type`, `product_category`, `is_active`, `app_id`, `created_at` | Products |
| `dunning_campaigns` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `name`, `type`, `status`, `grace_period_days`, `retry_attempts`, `notification_channel`, `customers_affected`, `recovery_rate`, `start_date`, `end_date`, `app_id`, `total_notifications_sent`, `total_payments_recovered`, `recovered_amount` | Dunning |
| `countries` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `name`, `currency_code` | Dashboard (filter options) |
| `customer_devices` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `sms_cust_id`, `platform`, `created_at` | Reports, Sub360 |
| `client_users` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `name`, `app_id`, `status`, `role_id`, `permission_type` | Auth, Support |
| `personal_access_tokens` | [id](file:///home/areeb/Downloads/SMSV2/sms_admin/app/Http/Controllers/BackOffice/BackOfficeWidgetController.php#1275-1326), `tokenable_type`, `tokenable_id`, `token` | Auth (Sanctum) |

### Status Code Reference

| Table | Code | Meaning |
|-------|------|---------|
| `tbl_customer_subscriptions` | 2 | Active/Success |
| `tbl_customer_subscriptions` | 3 | Cancelled |
| `tbl_customer_subscriptions` | 5 | Refunded |
| `tbl_customer_subscriptions` | 7 | Renewed |
| `tbl_customer_subscriptions` | 8 | Failed |
| `tbl_customer_subscriptions` | 9 | Suspended |
| `tbl_order` | 2 | Success |
| `tbl_order` | 3 | Failed/Cancelled |
| `tbl_packages` | 1 | Active |
| `coupans` | 1 | Active |
| `coupans` | 2 | Expired |

---

## 10. Master Query Repository

### Elasticsearch Services

| Service | Index Pattern | Used By |
|---------|--------------|---------|
| `DashboardElasticsearchService` | `subscriptions-*` | Dashboard KPIs |
| `DashboardPageElasticsearchService` | `subscriptions-*`, `orders-*` | Dashboard charts |
| `Subscriber360ElasticsearchService` | `customers-*` | Sub360 listing |
| `BillingElasticsearchService` | `subscriptions-*` | Billing KPIs/charts |
| `ProductAdminElasticsearchService` | `subscriptions-*` | Top packages |
| `BackOfficeCouponAnalyticsService` | `orders-*` | Coupon analytics |

### Caching Strategy

| Module | Cache Key Pattern | TTL |
|--------|------------------|-----|
| Dashboard Widgets | `dashboard_{widget}_{appKey}_{filtersHash}` | 120-300s |
| Filter Options | `dashboard_filter_options_{appId}_{modelType}` | 300s |
| Region Options | `dashboard_region_options_{appId}` | 300s |
| Package Options | `dashboard_package_options_{appId}_{model}_{region}` | 300s |
| Gateway Distribution | `dashboard_gateway_data_{appKey}_{filtersHash}` | 300s |

### Common Query Patterns

1. **Tenant Scoping:** All queries include `WHERE app_id = ?`
2. **Date Ranging:** `WHERE created_at BETWEEN ? AND ?` (start_date → end_date)
3. **Status Filtering:** `WHERE status IN (2, 7)` for active subscriptions
4. **Column Resolution:** Dynamic column detection via `Schema::hasColumn()` for backward compatibility
5. **Aggregation:** `COUNT(DISTINCT subscriber_id)`, `SUM(price)`, `AVG(price)`
6. **Device Detection:** `CASE WHEN platform = 'web' THEN 'Web' WHEN '' THEN 'Android' WHEN 'ios' THEN 'iOS' ELSE 'Unknown' END`

---

> [!NOTE]
> This document maps data as discovered in the controller source code. Elasticsearch query DSL mirrors the SQL logic but uses ES aggregations (`terms`, `date_histogram`, `sum`, `avg`) instead of SQL `GROUP BY`. The ES services check `isEnabled()` / `isAvailable()` before executing—if ES is down, all queries fall back to PostgreSQL.