# Subscriber 360° Page - Queries and Logic Documentation

## Table of Contents
1. [Overview](#overview)
2. [Filters and App ID Logic](#filters-and-app-id-logic)
3. [Card Data Queries](#card-data-queries)
4. [Mini Graph Data Generation](#mini-graph-data-generation)
5. [Chart Data Queries](#chart-data-queries)
6. [Engagement Calculation Formula](#engagement-calculation-formula)
7. [Table Data Queries](#table-data-queries)
8. [API Endpoints](#api-endpoints)

---

## Overview

The Subscriber 360° page displays comprehensive subscriber analytics including summary cards, charts, and a detailed customer table. All data is loaded via AJAX through the `Subscriber360ApiController`.

**Main Files:**
- `app/Filament/Resources/CustomerResource/Pages/ListCustomers.php` - Business logic and queries
- `app/Http/Controllers/Subscriber360/Subscriber360ApiController.php` - AJAX API endpoints
- `resources/views/filament/resources/customer-resource/pages/list-customers.blade.php` - Frontend view

**Default Date Range:** Last 6 months (from `now()->subMonths(6)` to `now()`)

**Default Payment Status Filter:** `'all'` (can be changed to `'paid'` or `'free'`)

---

## Filters and App ID Logic

### App ID Filtering

**Source:** `Auth::user()->app_id` (always prioritized over session)

**Implementation:**
```php
private function getAppId(): ?int
{
    // Cached at request level
    if ($this->cachedAppId !== null) {
        return $this->cachedAppId;
    }
    
    $appId = session('app_id');
    
    if (!$appId && Auth::check()) {
        $user = Auth::user();
        if ($user && isset($user->app_id)) {
            $appId = $user->app_id;
        }
    }
    
    $this->cachedAppId = $appId ? (int) $appId : null;
    return $this->cachedAppId;
}
```

**Applied to:** All queries use `WHERE app_id = ?` when `appId !== null`

---

### Paid/Unpaid Filter Logic

**Method:** `getFilteredSubscriberIds()`

**Filter Values:**
- `'all'` - Returns `null` (no filter applied)
- `'paid'` - Returns subscriber IDs with active subscriptions (`status = 2`)
- `'free'` - Returns subscriber IDs without paid subscriptions

**Query for Paid Subscribers:**
```sql
SELECT DISTINCT subscriber_id
FROM tbl_customer_subscriptions
WHERE status = 2  -- Active/Paid subscription
  AND app_id = ?  -- If app_id filter is set
```

**Query for Free Subscribers:**
```sql
-- Get all paid subscriber IDs first (same as above)
-- Then:
SELECT client_cust_id
FROM customers
WHERE client_cust_id IS NOT NULL
  AND client_cust_id NOT IN (paid_subscriber_ids)
  AND app_id = ?  -- If app_id filter is set
```

**Note:** The filter uses `client_cust_id` from `customers` table to match with `subscriber_id` from `tbl_customer_subscriptions` table.

---

### Date Range Filter

**Default:** Last 6 months
```php
$startDate = now()->subMonths(6)->format('Y-m-d');
$endDate = now()->format('Y-m-d');
```

**Applied to:**
- Card data queries (users created in date range)
- Chart data queries
- Table data queries (optional filter)

---

## Card Data Queries

**Method:** `getCardData()`

**Returns:** Array with counts for:
- Total Users
- Active Users
- Suspended Users
- Trial Users
- Trial Expiring Soon (within 3 days)
- Premium/Basic/Standard counts and percentages
- Mini graph data (7 data points)
- Trend calculations (positive/negative)

---

### 1. Total Users

**Query:**
```sql
SELECT COUNT(*)
FROM customers
WHERE created_at BETWEEN ? AND ?
  AND app_id = ?  -- If app_id filter is set
```

**PHP:**
```php
$customersBaseQuery = Customer::whereBetween('created_at', [$startDate, $endDate]);
if ($appId !== null) {
    $customersBaseQuery->where('app_id', $appId);
}
$ua_totalUsers = $customersBaseQuery->count();
```

**Note:** Does NOT apply paid/unpaid filter (shows all users in date range)

---

### 2. Suspended Users

**Query:**
```sql
SELECT COUNT(*)
FROM customers
WHERE created_at BETWEEN ? AND ?
  AND status != 1  -- Not active
  AND app_id = ?  -- If app_id filter is set
  AND client_cust_id IN (?)  -- If paid/unpaid filter is set
```

**PHP:**
```php
$suspendedQuery = Customer::whereBetween('created_at', [$startDate, $endDate]);
if ($appId !== null) {
    $suspendedQuery->where('app_id', $appId);
}
if ($filteredSubscriberIds !== null) {
    $suspendedQuery->whereIn('client_cust_id', $filteredSubscriberIds);
}
$ua_suspendedUsers = $suspendedQuery->where('status', '!=', 1)->count();
```

---

### 3. Active Users

**Formula:** Active subscriptions at end date checkpoint

**Query:**
```sql
SELECT COUNT(DISTINCT subscriber_id)
FROM tbl_customer_subscriptions
WHERE status = 2  -- Active subscription
  AND subscriber_id IS NOT NULL
  AND (
    (start_date IS NULL OR start_date <= ?)  -- periodEnd
    AND (end_date IS NULL OR end_date >= ?)  -- periodStart
  )
  AND app_id = ?  -- If app_id filter is set
  AND subscriber_id IN (?)  -- If paid/unpaid filter is set
```

**PHP:**
```php
$periodStart = $endDate->copy()->startOfDay();
$periodEnd = $endDate->copy()->endOfDay();

$activeUsersQuery = CustomerSubscription::where('status', 2)
    ->whereNotNull('subscriber_id')
    ->where(function($query) use ($periodStart, $periodEnd) {
        $query->where(function($subQ) use ($periodEnd) {
            $subQ->whereNull('start_date')
                  ->orWhere('start_date', '<=', $periodEnd);
        })
        ->where(function($subQ) use ($periodStart) {
            $subQ->whereNull('end_date')
                  ->orWhere('end_date', '>=', $periodStart);
        });
    });

if ($appId !== null) {
    $activeUsersQuery->where('app_id', $appId);
}
if ($filteredSubscriberIds !== null && !empty($filteredSubscriberIds)) {
    $activeUsersQuery->whereIn('subscriber_id', $filteredSubscriberIds);
}

$ua_activeUsers = $activeUsersQuery->distinct()->count('subscriber_id');
```

**Note:** Matches Subscriber Timeline Chart's last timeline point logic

---

### 4. Trial Users

**Query:**
```sql
SELECT COUNT(DISTINCT subscriber_id)
FROM tbl_customer_subscriptions
WHERE package_id = 0  -- Trial package
  AND status = 2
  AND app_id = ?  -- If app_id filter is set
  AND subscriber_id IN (?)  -- If paid/unpaid filter is set
```

**PHP:**
```php
// Loaded from cached subscription data
$allTrialSubscriptions = $this->cachedAllSubscriptionsData->filter(function($sub) {
    return ($sub->package_id ?? 0) == 0;
});

$trialSubscriberIds = $allTrialSubscriptions->pluck('subscriber_id')->unique();
$ua_trialUsers = $trialSubscriberIds->count();
```

---

### 5. Trial Expiring Soon

**Query:** Same as Trial Users, but filters by `end_date <= (endDate + 3 days)`

**PHP:**
```php
$expiringSoonDays = 3;
$expiringSoonDate = $endDate->copy()->addDays($expiringSoonDays);

$trialSubscriptionsWithEndDate = $allTrialSubscriptions->map(function($sub) {
    return [
        'subscriber_id' => $sub->subscriber_id,
        'end_date' => $sub->end_date
    ];
})->unique('subscriber_id');

$ua_trialExpiringSoon = $trialSubscriptionsWithEndDate->filter(function($sub) use ($expiringSoonDate) {
    return $sub['end_date'] && Carbon::parse($sub['end_date']) <= $expiringSoonDate;
})->count();
```

---

### 6. Premium/Basic/Standard Counts

**Query:** Loads all subscriptions with package info, then filters in memory

**Base Query:**
```sql
SELECT 
    tbl_customer_subscriptions.subscriber_id,
    tbl_packages.package_category_type,
    tbl_customer_subscriptions.package_id,
    tbl_customer_subscriptions.start_date,
    tbl_customer_subscriptions.end_date,
    tbl_customer_subscriptions.created_at
FROM tbl_customer_subscriptions
INNER JOIN tbl_packages ON tbl_packages.id = tbl_customer_subscriptions.package_id
WHERE tbl_customer_subscriptions.status = 2
  AND tbl_customer_subscriptions.subscriber_id IS NOT NULL
  AND (
    tbl_customer_subscriptions.start_date <= ?
    AND (tbl_customer_subscriptions.end_date >= ? OR tbl_customer_subscriptions.end_date IS NULL)
  )
  AND (
    tbl_packages.package_category_type IN (1, 2, 3)  -- Basic=1, Standard=2, Premium=3
    OR tbl_customer_subscriptions.package_id = 0  -- Trial
  )
  AND tbl_packages.app_id = ?  -- If app_id filter is set
ORDER BY tbl_customer_subscriptions.subscriber_id,
         tbl_customer_subscriptions.created_at DESC
```

**Logic:**
1. Filter subscriptions to Premium/Basic/Standard only (`package_category_type IN (1, 2, 3)`)
2. Get latest subscription per subscriber (by `created_at DESC`)
3. Count subscribers with ACTIVE subscriptions at end date checkpoint:
   - `(start_date <= periodEnd) AND (end_date >= periodStart OR end_date IS NULL)`

**Package Category Types:**
- `1` = Basic
- `2` = Standard
- `3` = Premium

**Percentages:**
```php
$ua_premiumPct = $ua_totalUsers ? round($ua_premiumCount / $ua_totalUsers * 100, 1) : 0;
$ua_basicPct = $ua_totalUsers ? round($ua_basicCount / $ua_totalUsers * 100, 1) : 0;
$ua_standardPct = $ua_totalUsers ? round($ua_standardCount / $ua_totalUsers * 100, 1) : 0;
```

---

## Mini Graph Data Generation

**Method:** `generateMiniGraphData(string $type, int $points = 7)`

**Purpose:** Generate 7 data points for mini graphs on each card

**Types:**
- `'total_users'` - Total users over time
- `'active_users'` - Active users over time
- `'suspended_users'` - Suspended users over time
- `'trial_users'` - Trial users over time
- `'premium'`, `'basic'`, `'standard'` - Plan counts over time

**Date Calculation:**
```php
$totalDays = $dateRangeStart->diffInDays($dateRangeEnd);
$intervalDays = max(1, floor($totalDays / ($points - 1)));

// Generate dates
for ($i = 0; $i < $points; $i++) {
    $date = $dateRangeStart->copy()->addDays($i * $intervalDays);
    if ($date->gt($dateRangeEnd)) {
        $date = $dateRangeEnd->copy();
    }
    $dates[] = $date;
}
```

---

### Total Users Mini Graph

**Query:** Batch query for all dates
```sql
SELECT id, created_at
FROM customers
WHERE created_at <= ?  -- maxDate
  AND app_id = ?  -- If app_id filter is set
  AND client_cust_id IN (?)  -- If paid/unpaid filter is set
```

**Logic:** Count customers where `created_at <= date` for each date point

---

### Active Users Mini Graph

**Query:**
```sql
SELECT subscriber_id, start_date, end_date
FROM tbl_customer_subscriptions
WHERE status = 2
  AND (
    (start_date IS NULL OR start_date <= ?)  -- maxDate
    AND (end_date IS NULL OR end_date >= ?)  -- minDate
  )
  AND app_id = ?  -- If app_id filter is set
  AND subscriber_id IN (?)  -- If paid/unpaid filter is set
```

**Logic:** For each date, count distinct subscribers where:
- `(start_date IS NULL OR start_date <= date) AND (end_date IS NULL OR end_date >= date)`

---

### Suspended Users Mini Graph

**Query:**
```sql
SELECT id, created_at
FROM customers
WHERE status != 1
  AND created_at <= ?  -- maxDate
  AND app_id = ?  -- If app_id filter is set
  AND client_cust_id IN (?)  -- If paid/unpaid filter is set
```

**Logic:** Count customers where `created_at <= date` and `status != 1` for each date point

---

### Trial Users Mini Graph

**Query:**
```sql
SELECT subscriber_id, created_at
FROM tbl_customer_subscriptions
WHERE package_id = 0
  AND status = 2
  AND created_at <= ?  -- maxDate
  AND app_id = ?  -- If app_id filter is set
  AND subscriber_id IN (?)  -- If paid/unpaid filter is set
```

**Logic:** Group by `subscriber_id`, get minimum `created_at`, then count where `created_at <= date` for each date point

---

### Plan Count Mini Graph (Premium/Basic/Standard)

**Query:**
```sql
SELECT 
    tbl_customer_subscriptions.subscriber_id,
    tbl_packages.package_category_type,
    tbl_customer_subscriptions.start_date,
    tbl_customer_subscriptions.created_at
FROM tbl_customer_subscriptions
INNER JOIN tbl_packages ON tbl_packages.id = tbl_customer_subscriptions.package_id
WHERE tbl_customer_subscriptions.status = 2
  AND CAST(tbl_packages.package_category_type AS TEXT) = ?  -- 'Premium', 'Basic', or 'Standard'
  AND tbl_customer_subscriptions.created_at <= ?  -- maxDate
  AND tbl_packages.app_id = ?  -- If app_id filter is set
  AND tbl_customer_subscriptions.app_id = ?  -- If app_id filter is set
ORDER BY tbl_customer_subscriptions.subscriber_id,
         tbl_customer_subscriptions.start_date DESC,
         tbl_customer_subscriptions.created_at DESC
```

**Logic:** For each date, get latest subscription per subscriber created before or on that date, then count distinct subscribers

---

### Trend Calculation

**Method:** `calculateTrend(array $data)`

**Formula:**
```php
$first = $data[0];
$last = $data[count($data) - 1];

if ($first == 0) {
    $percent = $last > 0 ? 100 : 0;
} else {
    $percent = round((($last - $first) / $first) * 100, 2);
}

$type = $percent >= 0 ? 'positive' : 'negative';
$value = abs($percent);
```

**Returns:**
```php
[
    'type' => 'positive' | 'negative',
    'value' => abs($percent),
    'percent' => $percent
]
```

---

## Chart Data Queries

**Method:** `getChartData()`

**Returns:** Array with:
- `ua_line_chart` - User Growth Chart
- `ua_engagement` - Engagement by Plan (pie chart)
- `ua_pie` - Active/Trial/Suspended Pie Chart
- `ua_heatmap` - Login Heatmap (first 6 weeks)
- `ua_heatmap_full` - Login Heatmap (all weeks)
- `ua_monthOptions` - Month options for heatmap

---

### 1. User Growth Chart

**Method:** `buildUserGrowthChart()`

**Purpose:** Cumulative total users over time (line chart)

**Query:**
```sql
-- Base count (users created before date range start)
SELECT COUNT(*)
FROM customers
WHERE created_at < ?  -- startDate
  AND app_id = ?  -- If app_id filter is set
  AND client_cust_id IN (?)  -- If paid/unpaid filter is set

-- Daily counts
SELECT 
    DATE(created_at) AS created_date,
    COUNT(*) AS daily_count
FROM customers
WHERE created_at BETWEEN ? AND ?  -- startDate and endDate
  AND app_id = ?  -- If app_id filter is set
  AND client_cust_id IN (?)  -- If paid/unpaid filter is set
GROUP BY DATE(created_at)
ORDER BY created_date ASC
```

**Logic:**
1. Get base count (users before date range)
2. Get daily counts for each day in range
3. Generate all dates in range (even if no users)
4. Calculate cumulative totals: `running += dailyCount` for each date
5. Ensure values are non-decreasing

**Y-Axis Max:**
```php
$maxValue = max($values ?: [0]);
$yAxisMax = $maxValue > 0 ? ceil($maxValue * 1.1) : 10;
```

---

### 2. Engagement by Plan Chart

**Method:** `buildEngagementByPlan()`

**Purpose:** Pie chart showing Premium/Basic/Standard/Trial distribution with engagement scores

**Query:** Same base query as Premium/Basic/Standard counts in card data

**Logic:**
1. Load all subscriptions (including trials) with package info
2. Separate plan subscriptions (`package_category_type IN (1, 2, 3)`) and trial subscriptions (`package_id = 29`)
3. Get latest subscription per subscriber for plan subscriptions
4. Count subscribers per plan type
5. Calculate engagement for each plan group (see [Engagement Calculation](#engagement-calculation-formula))
6. Build pie chart segments with SVG paths

**Pie Chart Calculation:**
```php
$percent = $seg['value'] / max(1, $total);
$sweep = $percent * 360.0;  // Convert to degrees
// Generate SVG arc path
```

**Colors:**
- Premium: `#8b5cf6` (purple)
- Standard: `#10b981` (green)
- Basic: `#3b82f6` (blue)
- User Distribution (Trial): `#f59e0b` (orange)

---

### 3. Active/Trial/Suspended Pie Chart

**Method:** `buildActiveTrialSuspendedPie()`

**Purpose:** Pie chart showing Active vs Suspended users

**Note:** Does NOT use paid/unpaid filter, only date range

**Query:**
```sql
SELECT 
    COUNT(*) as total,
    COUNT(CASE WHEN status = 1 THEN 1 END) as active,
    COUNT(CASE WHEN status != 1 THEN 1 END) as suspended
FROM customers
WHERE created_at BETWEEN ? AND ?
  AND app_id = ?  -- If app_id filter is set
```

**Status Values:**
- `1` = Active
- `!= 1` = Suspended (includes 0, 2, 3, 4)

**Colors:**
- Active: `#10b981` (green)
- Suspended: `#ef4444` (red)

---

### 4. Login Heatmap

**Method:** `buildLoginHeatmap(?string $selectedMonth = null, bool $is6Months = false)`

**Purpose:** Weekly login activity heatmap

**Parameters:**
- `$is6Months = false` - Show last 6 weeks (default view)
- `$is6Months = true` - Show all weeks in date range (modal view)

**Query:**
```sql
SELECT 
    DATE(last_login) as date,
    COUNT(DISTINCT id) as count
FROM customers
WHERE last_login IS NOT NULL
  AND last_login BETWEEN ? AND ?  -- queryStart and queryEnd
  AND app_id = ?  -- If app_id filter is set
GROUP BY DATE(last_login)
```

**Logic:**
1. Calculate start Sunday for the period
2. Generate weeks (6 weeks for default, all weeks for modal)
3. For each week, count logins per day (Mon-Sun)
4. Calculate color thresholds:
   - High: `ceil(maxValue * 0.65)` (green)
   - Medium: `ceil(maxValue * 0.35)` (yellow)
   - Low: below medium (red)

**Week Label Format:**
- `"Week 1 (Oct)"` - Single month
- `"Week 1 (Oct/Nov)"` - Spans two months

**Color Classes:**
- `high` - Green (>= thresholdHigh)
- `medium` - Yellow (>= thresholdMedium)
- `low` - Red (< thresholdMedium)

---

## Engagement Calculation Formula

**Method:** `calculateBatchEngagement()` or `calculateAverageEngagement()`

**Purpose:** Calculate average engagement score for a group of customers

**Data Source:** `tbl_content_watched` table

**Query:**
```sql
SELECT 
    user_id,
    SUM(duration) AS total_watch_sec,
    COUNT(*) AS sessions,
    COUNT(DISTINCT content_id) AS distinct_titles,
    COUNT(DISTINCT category_id) AS distinct_categories,
    SUM(CASE WHEN duration >= 600 THEN 1 ELSE 0 END) AS deep_sessions
FROM tbl_content_watched
WHERE user_id IN (?)
  AND app_id = ?  -- If app_id filter is set
  AND created BETWEEN ? AND ?  -- If date range is set
GROUP BY user_id
```

**Dummy Data Exclusion:**
- Excludes subscriptions with `id BETWEEN 2960 AND 3728` (dummy data)

**Engagement Score Calculation:**
```php
// For each customer:
$watchTimeScore = min($total_watch_sec / 7200.0, 1.0);  // 2 hours = 1.0
$titlesScore = min($distinct_titles / 5.0, 1.0);  // 5 titles = 1.0
$sessionsScore = min($sessions / 5.0, 1.0);  // 5 sessions = 1.0
$deepWatchScore = min($deep_sessions / 3.0, 1.0);  // 3 deep sessions = 1.0
$categoryDivScore = min($distinct_categories / 4.0, 1.0);  // 4 categories = 1.0

$engagementScore = (
    0.40 * $watchTimeScore +      // 40% weight
    0.20 * $titlesScore +          // 20% weight
    0.10 * $sessionsScore +        // 10% weight
    0.20 * $deepWatchScore +       // 20% weight
    0.10 * $categoryDivScore        // 10% weight
) * 100;  // Convert to 0-100 scale
```

**Average Engagement:**
```php
$totalEngagement = sum of all customer engagement scores
$count = number of customers (including those with 0 engagement)
$averageEngagement = round($totalEngagement / $count);
```

**Note:** Customers without any `tbl_content_watched` records are included with engagement = 0

---

## Table Data Queries

**Method:** `getTableData()` in `Subscriber360ApiController`

**Purpose:** Paginated customer list with search/filter capabilities

**Base Query:**
```sql
SELECT *
FROM customers
WHERE app_id = ?  -- If app_id filter is set
  AND client_cust_id IN (?)  -- If paid/unpaid filter is set
```

**Search Filters:**
- `customer_id` - Exact match on `client_cust_id` (numeric only)
- `email` - LIKE `%{email}%` on `email_id`
- `phone` - LIKE `%{phone}%` on `phone_number`
- `joined` - Exact date match on `created_at`
- `status` - Exact match on `status`
- `subscription_status` - Exact match on `subscription_status`
- `created_from` - `created_at >= ?`
- `created_until` - `created_at <= ?`

**Sorting:**
- Default: `created_at DESC`
- Allowed columns: `created_at`, `external_customer_id`, `full_name`, `email_id`, `phone_number`, `status`, `subscription_status`

**Pagination:**
- Default: 25 records per page
- Allowed: 10, 25, 50, 100

**DataTables Format:**
```json
{
    "draw": 1,
    "recordsTotal": 1000,
    "recordsFiltered": 150,
    "data": [...]
}
```

**Legacy Format:**
```json
{
    "success": true,
    "data": [...],
    "pagination": {
        "current_page": 1,
        "per_page": 25,
        "total": 150,
        "last_page": 6,
        "from": 1,
        "to": 25
    }
}
```

**Subscription Status Check:**
```php
$hasActiveSubscription = $customer->subscriptions()
    ->where('status', 2)
    ->whereDate('start_date', '<=', now())
    ->whereDate('end_date', '>=', now())
    ->exists();
$subscriptionStatusText = $hasActiveSubscription ? 'Subscribed' : 'Not Subscribed';
```

---

## API Endpoints

**Base URL:** `/subscriber-360/widgets`

**Authentication:** Bearer token via `Authorization` header

**Middleware:** `auth`, `web`, `throttle:300,1`, `CdnCacheHeaders`

---

### 1. Get Cards Data

**Endpoint:** `GET /subscriber-360/widgets/cards`

**Query Parameters:**
- `date_range_start` (optional) - Format: `Y-m-d`
- `date_range_end` (optional) - Format: `Y-m-d`
- `paid_unpaid_filter` (optional) - Values: `'all'`, `'paid'`, `'free'` (default: `'paid'`)

**Response:**
```json
{
    "success": true,
    "data": {
        "ua_totalUsers": 1000,
        "ua_activeUsers": 850,
        "ua_suspendedUsers": 150,
        "ua_trialUsers": 50,
        "ua_trialExpiringSoon": 5,
        "ua_role": {
            "premium": {"count": 300, "pct": 30.0},
            "basic": {"count": 400, "pct": 40.0},
            "standard": {"count": 150, "pct": 15.0}
        },
        "graphs": {
            "total_users": [100, 150, 200, ...],
            "active_users": [80, 120, 150, ...],
            ...
        },
        "trends": {
            "total_users": {"type": "positive", "value": 15.5, "percent": 15.5},
            ...
        }
    }
}
```

**Cache:** 300 seconds (5 minutes)

---

### 2. Get Charts Data

**Endpoint:** `GET /subscriber-360/widgets/charts`

**Query Parameters:** Same as Get Cards Data

**Response:**
```json
{
    "success": true,
    "data": {
        "ua_line_chart": {
            "datasets": [{
                "label": "Total Users",
                "data": [100, 150, 200, ...],
                "borderColor": "rgb(16, 185, 129)",
                ...
            }],
            "labels": ["Jan 1, 2024", "Jan 2, 2024", ...],
            "yAxisMax": 1100
        },
        "ua_engagement": {
            "segments": [...],
            "center": {"cx": 120, "cy": 120, "r": 85},
            "total": 900
        },
        "ua_pie": {
            "segments": [...],
            "center": {"cx": 120, "cy": 120, "r": 85}
        },
        "ua_heatmap": {
            "days": ["Mon", "Tue", ...],
            "weeks": [...]
        },
        "ua_heatmap_full": {...},
        "ua_monthOptions": {...},
        "date_range_start": "2024-01-01",
        "date_range_end": "2024-07-01"
    }
}
```

**Cache:** 300 seconds (5 minutes)

---

### 3. Get Table Data

**Endpoint:** `GET /subscriber-360/widgets/table`

**Query Parameters:**
- `date_range_start` (optional)
- `date_range_end` (optional)
- `paid_unpaid_filter` (optional)
- `draw` (DataTables) - Request counter
- `start` (DataTables) - Starting record index
- `length` (DataTables) - Records per page
- `order[0][column]` (DataTables) - Column index to sort
- `order[0][dir]` (DataTables) - Sort direction (`asc` or `desc`)
- `customer_id` (optional) - Search filter
- `email` (optional) - Search filter
- `phone` (optional) - Search filter
- `joined` (optional) - Search filter
- `status` (optional) - Search filter
- `subscription_status` (optional) - Search filter
- `created_from` (optional) - Search filter
- `created_until` (optional) - Search filter

**Response (DataTables Format):**
```json
{
    "draw": 1,
    "recordsTotal": 1000,
    "recordsFiltered": 150,
    "data": [
        {
            "id": 1,
            "customer_id": "CUST001",
            "name": "John Doe",
            "email": "john@example.com",
            "phone": "+1 555-1234",
            "status": 1,
            "status_label": "Active",
            "subscription_status": 1,
            "subscription_status_text": "Subscribed",
            "joined_date": "Jan 15, 2024",
            "view_url": "/customers/1"
        },
        ...
    ]
}
```

**Cache:** 300 seconds (5 minutes)

---

### 4. Get All Data

**Endpoint:** `GET /subscriber-360/widgets/all-data`

**Query Parameters:** Same as Get Cards Data

**Response:**
```json
{
    "success": true,
    "data": {
        "cards": {...},  // Same as Get Cards Data response
        "charts": {...}  // Same as Get Charts Data response
    }
}
```

**Cache:** 300 seconds (5 minutes)

---

## Cache Strategy

**Request-Level Caching:**
- `cachedFilteredSubscriberIds` - Paid/unpaid filter results
- `cachedAppId` - App ID lookup
- `cachedDummySubscriptionIds` - Dummy subscription IDs
- `cachedAllSubscriptionsData` - Subscription data (shared between cards and charts)

**Laravel Cache:**
- Cache keys include: `date_range_start`, `date_range_end`, `paid_unpaid_filter`, `app_id`
- TTL: 300 seconds (5 minutes)
- Cache tags: None (simple key-value cache)

**CDN Cache Headers:**
- `Cache-Control: public, max-age=300, s-maxage=300`
- `ETag` - Based on data hash + user ID + app ID
- `Vary: Cookie, Authorization, X-User-Id, X-App-Id`
- `304 Not Modified` - If `If-None-Match` header matches `ETag`

---

## Key Database Tables

1. **`customers`** - Main customer/user table
   - `id` - Primary key
   - `client_cust_id` - External customer ID (matches `subscriber_id` in subscriptions)
   - `app_id` - Tenant ID
   - `status` - User status (1=Active, 0=Inactive, 2=Deleted, 3=Suspended, 4=Blocked)
   - `created_at` - Registration date
   - `last_login` - Last login timestamp

2. **`tbl_customer_subscriptions`** - Subscription records
   - `id` - Primary key
   - `subscriber_id` - Customer ID (matches `client_cust_id` in customers)
   - `package_id` - Package ID (0 = Trial)
   - `status` - Subscription status (2 = Active/Paid)
   - `start_date` - Subscription start date
   - `end_date` - Subscription end date (NULL = ongoing)
   - `app_id` - Tenant ID
   - `created_at` - Subscription creation date

3. **`tbl_packages`** - Package/plan definitions
   - `id` - Primary key
   - `package_category_type` - Plan type (1=Basic, 2=Standard, 3=Premium)
   - `status` - Package status (1=Active)
   - `app_id` - Tenant ID

4. **`tbl_content_watched`** - Content viewing history
   - `user_id` - Customer ID (matches `client_cust_id`)
   - `duration` - Watch duration in seconds
   - `content_id` - Content ID
   - `category_id` - Category ID
   - `created` - View timestamp
   - `app_id` - Tenant ID

---

## Notes

1. **Date Range Overlap Logic:** Used consistently across all subscription queries:
   ```php
   (start_date <= periodEnd) AND (end_date >= periodStart OR end_date IS NULL)
   ```

2. **Latest Subscription Selection:** Uses `created_at DESC` (not `start_date DESC`) to match Cohort Retention logic

3. **Dummy Data Exclusion:** Subscriptions with `id BETWEEN 2960 AND 3728` are excluded from engagement calculations

4. **Payment Status Filter:** Uses `status = 2` (active subscription) as the definition of "paid", regardless of order status

5. **App ID Priority:** Always uses `Auth::user()->app_id` as source of truth, never URL parameters

6. **Request-Level Caching:** Multiple methods share cached data to avoid redundant queries within the same request

7. **Default Filters:** 
   - Date range: Last 6 months
   - Payment status: `'all'` (can be changed to `'paid'` or `'free'`)

---

## File Locations

- **Main Logic:** `app/Filament/Resources/CustomerResource/Pages/ListCustomers.php`
- **API Controller:** `app/Http/Controllers/Subscriber360/Subscriber360ApiController.php`
- **Blade View:** `resources/views/filament/resources/customer-resource/pages/list-customers.blade.php`
- **Resource Definition:** `app/Filament/Resources/CustomerResource.php`
- **Routes:** `routes/web.php` (lines 329-341)

---

*Last Updated: 2024-01-13*