# Troubleshooting Guide: Passersby (Transeúntes) Data Showing as 0

## 📋 Table of Contents
1. [System Overview](#system-overview)
2. [How Data Flows](#how-data-flows)
3. [Common Problems & Solutions](#common-problems--solutions)
4. [Step-by-Step Diagnosis](#step-by-step-diagnosis)
5. [Database Schema Reference](#database-schema-reference)
6. [Code References](#code-references)

---

## 🎯 System Overview

The **Passersby (Transeúntes) Tracking System** uses WiFi scanning data from Cisco Meraki devices to detect people passing by your locations, even if they don't connect to your WiFi network.

### Key Concepts

- **PasserBy (Transeúnte)**: A device detected with:
  - No SSID (not connected to WiFi): `SSID IS NULL OR SSID = ''`
  - Weak signal (RSSI < -60): Device is outside or far from access points
  - Valid manufacturer (not in exclusion list)

- **Visitor**: A device detected with:
  - No SSID (not connected to WiFi)
  - Strong signal (RSSI >= -60): Device is inside the location
  - Valid manufacturer

- **Connected User**: A device that has connected to your WiFi (has SSID)

### Real-Time vs Historical Data

- **Real-Time Widget** (`PassersRealTimeWidget`):
  - Shows data from last **30 minutes only**
  - Queries `SplashWiFiScanningData` table directly
  - Updates every 15 seconds
  - Located at: `src/SplashPage.Web.Ui/src/app/dashboard/dynamicDashboard/_components/widgets/realtime/PassersRealTimeWidget.tsx`

- **Historical Metrics**:
  - Uses database views: `scanning_report_daily_unique` and `scanning_report_hourly_full`
  - Aggregated data by day/hour
  - Used for reports and analytics

---

## 🔄 How Data Flows

```
┌─────────────────┐
│  Meraki Devices │
│  (WiFi Scanning)│
└────────┬────────┘
         │ Sends scanning data every few minutes
         ▼
┌─────────────────────────────────────┐
│  Webhook Endpoint                   │
│  /ScanningAPI/ReceiveScanningData   │
│  (ScanningAPIController.cs:64)      │
└────────┬────────────────────────────┘
         │ Validates & filters data
         ▼
┌─────────────────────────────────────┐
│  SplashWiFiScanningData Table       │
│  (Raw scanning data)                │
└────────┬────────────────────────────┘
         │
         ├──► Real-Time Path (30 min window)
         │    └──► SplashMetricsService.RealTimeStats()
         │         └──► PassersRealTimeWidget (Frontend)
         │
         └──► Historical Path
              └──► Database Views (daily/hourly aggregation)
                   └──► SplashMetricsQueryService
                        └──► Reports & Analytics
```

### Critical Components

1. **Meraki Dashboard**: Must have Location Scanning webhook configured
2. **SplashLocationScanningConfigs**: Must have `IsEnabled=1` and `SyncStatus='Active'`
3. **SplashWiFiScanningData**: Raw data table populated by webhook
4. **Database Views**: Must exist for historical reporting
5. **Dashboard Network Selection**: Only selected networks show data

---

## ⚠️ Common Problems & Solutions

### Problem 1: Location Scanning Not Enabled or Synced

**Symptoms:**
- Master diagnostic shows: "Scanning Deshabilitado" or "Sync No Activo"
- No data in `SplashLocationScanningConfigs` table
- `IsEnabled = 0` or `SyncStatus != 'Active'`

**Causes:**
- Feature never configured for this network
- Configuration failed to sync with Meraki
- Network was disabled manually

**Solutions:**
1. Go to: Dashboard → Settings → Location Scanning (Synced Networks)
2. Find the network and click "Enable"
3. Verify sync status changes to "Active"
4. Check `ErrorMessage` field if sync fails
5. Ensure Meraki organization has Location Scanning enabled

**SQL to Check:**
```sql
SELECT Name, IsEnabled, SyncStatus, ErrorMessage, LastSyncedAt
FROM SplashLocationScanningConfigs c
JOIN SplashMerakiNetworks n ON n.Id = c.NetworkId
WHERE n.IsDeleted = 0;
```

---

### Problem 2: No Data Received from Meraki (Webhook Not Working)

**Symptoms:**
- Master diagnostic shows: "No hay datos"
- `SplashWiFiScanningData` table is empty or has no recent records
- Last record is older than 1 hour

**Causes:**
- Webhook not configured in Meraki Dashboard
- Webhook URL is incorrect
- Validator secret doesn't match
- Firewall blocking incoming webhooks
- Meraki devices not scanning

**Solutions:**
1. **Verify Webhook Configuration in Meraki Dashboard:**
   - Login to Meraki Dashboard
   - Go to: Network → General → Location and Scanning
   - Check webhook URL matches `PostUrl` from database
   - Expected format: `https://api-moonshot-center.beprime.mx/ScanningAPI/ReceiveScanningData/{secret}`
   - Verify "Post URL" is active and shows recent activity

2. **Check Application Logs:**
   - Look for webhook validation errors
   - Check for 401 (unauthorized) or 400 (bad request) errors
   - Verify `Secret` parameter matches `SplashPageConsts.EnvSecretKey`

3. **Test Webhook Manually:**
   - Use Meraki Dashboard's "Test" button
   - Check application logs for received requests

4. **Verify API Version:**
   - Database should have `ApiVersion = "3.0"`
   - Meraki webhook should send version "3.0"

**SQL to Check:**
```sql
-- Check configuration
SELECT NetworkId, PostUrl, ValidatorSecret, ApiVersion, LastSyncedAt
FROM SplashLocationScanningConfigs
WHERE IsEnabled = 1;

-- Check recent data
SELECT MAX(CreationTime) as last_record,
       TIMESTAMPDIFF(MINUTE, MAX(CreationTime), NOW()) as minutes_ago
FROM SplashWiFiScanningData;
```

**Code Reference:**
- Controller: `src/SplashPage.Web.Host/Controllers/ScanningAPIController.cs:64`
- Validation logic: Lines 85-94

---

### Problem 3: All Data is Filtered Out

**Symptoms:**
- Master diagnostic shows: "Todos los manufacturers excluidos" or "All devices have SSID"
- Raw data exists but no passersby in calculations
- Query 2 shows `passersby_eligible = 0`

**Causes:**
- All manufacturers in exclusion list
- All devices are connected to WiFi (have SSID)
- No devices with weak signal (RSSI >= -60 for all)

**Solutions:**

**3a. Manufacturer Exclusion:**
```sql
-- Check excluded manufacturers
SELECT Manufacturer,
       SUM(CASE WHEN ManufacturerIsExcluded = 1 THEN 1 ELSE 0 END) as excluded,
       SUM(CASE WHEN ManufacturerIsExcluded = 0 THEN 1 ELSE 0 END) as included
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY Manufacturer
ORDER BY excluded DESC;
```
- Review `ScanningAPIController.cs:99-107` for exclusion logic
- Manufacturers like "Unknown", "Meraki", etc. may be automatically excluded
- Update exclusion list if needed

**3b. All Devices Have SSID (Connected to WiFi):**
```sql
-- Check SSID distribution
SELECT
    CASE WHEN SSID IS NULL OR SSID = '' THEN 'No SSID' ELSE 'Has SSID' END as status,
    COUNT(*) as count
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
GROUP BY status;
```
- If all devices have SSID, they're not "passersby" - they're connected users
- This is normal during off-peak hours or in locations with few passersby
- Verify Meraki is scanning non-connected devices (check Meraki settings)

**3c. No Weak Signals (All RSSI >= -60):**
```sql
-- Check RSSI distribution
SELECT
    CASE
        WHEN NearestApRssi < -60 THEN 'Weak (PasserBy)'
        ELSE 'Strong (Visitor/Inside)'
    END as signal_strength,
    COUNT(DISTINCT ClientMac) as devices
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
  AND (SSID IS NULL OR SSID = '')
GROUP BY signal_strength;
```
- If all signals are strong (>= -60), devices are inside, not passing by
- This is the expected classification logic - they're "Visitors", not "PassersBy"
- Check frontend to ensure "Visitors" metric is displayed

**Code Reference:**
- RSSI threshold: `src/SplashPage.Application/Splash/SplashMetricsService.cs:774`
- SSID filter: `src/SplashPage.Application/Splash/SplashMetricsService.cs:682`

---

### Problem 4: Database Views Don't Exist

**Symptoms:**
- Historical reports show no data
- Error querying `scanning_report_daily_unique` or `scanning_report_hourly_full`
- Master diagnostic works but historical queries fail

**Causes:**
- Database migrations not run
- Views were never created
- Views were dropped accidentally

**Solutions:**
1. **Check if views exist:**
```sql
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('scanning_report_daily_unique', 'scanning_report_hourly_full')
  AND TABLE_TYPE = 'VIEW';
```

2. **Create views from SQL files:**
   - Location: `SQL/scanning_report/scanning_report_daily_unique.sql`
   - Run the SQL script to create the views
   - These views aggregate raw data for reporting

3. **Verify views work:**
```sql
SELECT COUNT(*) FROM scanning_report_daily_unique;
SELECT COUNT(*) FROM scanning_report_hourly_full;
```

**Code Reference:**
- View configuration: `src/SplashPage.EntityFrameworkCore/Configurations/SplashWifiScanningReportConfiguration.cs:15`
- View configuration: `src/SplashPage.EntityFrameworkCore/Configurations/ScanningReportHourlyFullConfiguration.cs:19`

---

### Problem 5: Wrong Networks Selected in Dashboard

**Symptoms:**
- Data exists but widget shows 0
- Other dashboards show data correctly
- Changing network selection fixes the issue

**Causes:**
- Dashboard filters are excluding the networks with data
- Network groups don't include the correct networks

**Solutions:**
1. **Check dashboard network selection:**
```sql
SELECT
    d.Name as dashboard_name,
    n.Name as network_name,
    n.Id as network_id
FROM SplashDashboards d
LEFT JOIN SplashNetworkGroups ng ON FIND_IN_SET(ng.Id, REPLACE(REPLACE(d.SelectedNetworkGroups, '[', ''), ']', ''))
LEFT JOIN SplashNetworkGroupMembers m ON m.NetworkGroupId = ng.Id
LEFT JOIN SplashMerakiNetworks n ON n.Id = m.NetworkId
WHERE d.Id = ? -- Replace with dashboard ID
  AND d.IsDeleted = 0;
```

2. **Verify which networks have data:**
```sql
SELECT
    n.Name as network_name,
    COUNT(*) as records_last_30min
FROM SplashWiFiScanningData s
JOIN SplashMerakiNetworks n ON n.Id = s.NetworkId
WHERE s.CreationTime >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
GROUP BY n.Name
ORDER BY records_last_30min DESC;
```

3. **Update dashboard filters to include networks with data**

**Code Reference:**
- Service filter: `src/SplashPage.Application/Splash/SplashMetricsService.cs:676` (line filtering by SelectedNetworks)

---

### Problem 6: Real-Time Window (30 Minutes) Has No Data

**Symptoms:**
- Historical data exists
- Master diagnostic shows old data (> 30 minutes ago)
- Real-time widget shows 0

**Causes:**
- Data is old (more than 30 minutes)
- Webhook stopped working recently
- Network activity paused

**Solutions:**
1. **Check data recency:**
```sql
SELECT
    MAX(CreationTime) as last_record,
    TIMESTAMPDIFF(MINUTE, MAX(CreationTime), NOW()) as minutes_ago,
    COUNT(*) as total_records
FROM SplashWiFiScanningData;
```

2. **If data is old (> 30 min):**
   - Follow [Problem 2: No Data Received](#problem-2-no-data-received-from-meraki-webhook-not-working)
   - Verify webhook is still active
   - Check for recent errors in logs

3. **Check if this is normal:**
   - Low traffic times may legitimately have no passersby
   - Verify with other time periods (use historical data)

**Code Reference:**
- 30-minute window: `src/SplashPage.Application/Splash/SplashMetricsService.cs:668` (`AddMinutes(-30)`)

---

## 🔍 Step-by-Step Diagnosis

Follow this process to diagnose the issue systematically:

### Step 1: Run Master Diagnostic Query

```sql
-- Open DIAGNOSTIC_QUERIES_PASSERSBY.sql
-- Run the MASTER DIAGNOSTIC QUERY at the top
```

**What to look for:**
- `diagnosis` column will tell you the problem
- `recommended_action` column will tell you what to do

### Step 2: Identify the Problem Category

Based on the diagnosis:

| Diagnosis | Go To |
|-----------|-------|
| "Scanning Deshabilitado" | [Problem 1](#problem-1-location-scanning-not-enabled-or-synced) |
| "Sync No Activo" | [Problem 1](#problem-1-location-scanning-not-enabled-or-synced) |
| "No hay datos" | [Problem 2](#problem-2-no-data-received-from-meraki-webhook-not-working) |
| "Datos obsoletos" | [Problem 6](#problem-6-real-time-window-30-minutes-has-no-data) |
| "Todos los manufacturers excluidos" | [Problem 3a](#problem-3-all-data-is-filtered-out) |
| "All devices have SSID" | [Problem 3b](#problem-3-all-data-is-filtered-out) |
| "No weak signals" | [Problem 3c](#problem-3-all-data-is-filtered-out) |

### Step 3: Run Specific Diagnostic Queries

Based on the problem, run the corresponding queries from `DIAGNOSTIC_QUERIES_PASSERSBY.sql`:

- **Problem 1**: Query 4 (Configuration)
- **Problem 2**: Query 1, 2, 12 (Data recency)
- **Problem 3**: Query 5, 6, 7 (Filters)
- **Problem 4**: Query 8, 9 (Views)
- **Problem 5**: Query 11 (Dashboard selection)
- **Problem 6**: Query 2, 10 (Real-time window)

### Step 4: Apply Solution

Follow the solution steps for the identified problem.

### Step 5: Verify Fix

After applying the solution:

1. Wait 5-10 minutes for new data
2. Run Query 2 (Real-Time Window) again
3. Check frontend widget for updates
4. Run Query 3 to simulate PassersBy calculation

---

## 📊 Database Schema Reference

### Table: `SplashWiFiScanningData`

Primary table for raw scanning data from Meraki webhooks.

| Column | Type | Description |
|--------|------|-------------|
| `Id` | int | Primary key |
| `NetworkId` | int | Foreign key to SplashMerakiNetworks |
| `ClientMac` | string | Device MAC address (unique identifier) |
| `SSID` | string | WiFi network name (NULL for passersby) |
| `Manufacturer` | string | Device manufacturer |
| `ManufacturerIsExcluded` | bool | True if in exclusion list |
| `OS` | string | Device operating system |
| `NearestApMac` | string | Nearest access point MAC |
| `NearestApRssi` | int | Signal strength (-90 to 0, higher is stronger) |
| `NearestApTime` | DateTime | Detection timestamp from Meraki |
| `CreationTime` | DateTime | Record insertion timestamp |

**Key**: `src/SplashPage.Core/Splash/SplashWiFiScanningData.cs`

---

### Table: `SplashLocationScanningConfigs`

Configuration for Location Scanning feature per network.

| Column | Type | Description |
|--------|------|-------------|
| `Id` | int | Primary key |
| `NetworkId` | int | Foreign key to SplashMerakiNetworks (unique) |
| `IsEnabled` | bool | Feature enabled flag |
| `SyncStatus` | string | "Pending", "Active", "Failed", "NotConfigured" |
| `ValidatorSecret` | string | Webhook validation secret |
| `PostUrl` | string | Webhook endpoint URL |
| `ApiVersion` | string | Meraki API version (default "3.0") |
| `LastSyncedAt` | DateTime | Last successful sync |
| `ErrorMessage` | string | Error details if sync failed |
| `FailedAttempts` | int | Number of failed sync attempts |

**Key**: `src/SplashPage.Core/Splash/SplashLocationScanningConfig.cs`

---

### View: `scanning_report_daily_unique`

Aggregated daily unique visitors/passersby per MAC address.

| Column | Type | Description |
|--------|------|-------------|
| `MacAddress` | string | Device MAC (part of composite key) |
| `DetectionDate` | date | Date of detection (part of composite key) |
| `NetworkId` | int | Network ID (part of composite key) |
| `PersonType` | string | "PasserBy" or "Visitor" |
| `PresenceCategory` | string | Dwell time classification |
| `DurationInMinutes` | int | Total time detected |
| `DetectionsCount` | int | Number of detections |
| `AverageRssi` | int | Average signal strength |
| `FirstDetection` | DateTime | First time seen |
| `LastDetection` | DateTime | Last time seen |

**Key**: `src/SplashPage.Core/Splash/SplashWifiScanningReport.cs`
**SQL**: `SQL/scanning_report/scanning_report_daily_unique.sql`

---

## 💻 Code References

### Backend Services

| Component | Location | Purpose |
|-----------|----------|---------|
| Webhook Controller | `src/SplashPage.Web.Host/Controllers/ScanningAPIController.cs:64` | Receives data from Meraki |
| Real-Time Stats | `src/SplashPage.Application/Splash/SplashMetricsService.cs:794` | Calculates PassersBy/Visitors |
| PassersBy Logic | `src/SplashPage.Application/Splash/SplashMetricsService.cs:670-774` | Filters and classifies devices |
| Historical Metrics | `src/SplashPage.Application/Splash/SplashMetricsQueryService.cs:119` | Uses database views |

### Frontend Components

| Component | Location | Purpose |
|-----------|----------|---------|
| Real-Time Widget | `src/SplashPage.Web.Ui/src/app/dashboard/dynamicDashboard/_components/widgets/realtime/PassersRealTimeWidget.tsx` | Displays PassersBy count |
| API Hook | Auto-generated | `usePostApiServicesAppSplashmetricsserviceRealtimestats` |

### Configuration Files

| File | Location | Purpose |
|------|----------|---------|
| Entity Config | `src/SplashPage.EntityFrameworkCore/Configurations/SplashWiFiScanningDataConfiguration.cs` | Table mapping |
| View Config | `src/SplashPage.EntityFrameworkCore/Configurations/SplashWifiScanningReportConfiguration.cs` | Daily view mapping |
| DbContext | `src/SplashPage.EntityFrameworkCore/EntityFrameworkCore/SplashPageDbContext.cs` | Database context |

---

## 🎯 Key Business Logic Rules

### PasserBy Classification

A device is classified as a **PasserBy** if:
1. ✅ Not connected to WiFi: `SSID IS NULL OR SSID = ''`
2. ✅ Not in exclusion list: `ManufacturerIsExcluded = false`
3. ✅ Has valid manufacturer: `Manufacturer IS NOT NULL`
4. ✅ Weak signal: `AverageRssi < -60`
5. ✅ Detected in last 30 minutes (for real-time)
6. ✅ Network is in selected networks

**Code**: `src/SplashPage.Application/Splash/SplashMetricsService.cs:774`

### RSSI Threshold

- **RSSI < -60**: PasserBy (outside, far from AP)
- **RSSI >= -60**: Visitor (inside, close to AP)

RSSI values typically range from -90 (very weak) to -30 (very strong).

### Real-Time Window

- Widget refreshes every **15 seconds**
- Only shows data from last **30 minutes**
- Uses `CreationTime >= NOW() - INTERVAL 30 MINUTE`

---

## 📝 Quick Reference: SQL Queries by Use Case

| Use Case | Query Number | Query Name |
|----------|--------------|------------|
| First diagnostic | Master | Master Diagnostic Query |
| Check if data exists | 1 | Raw Data (24h) |
| Check real-time window | 2 | Real-Time Window (30 min) |
| Simulate widget calculation | 3 | Simulate PassersBy |
| Check configuration | 4 | Location Scanning Config |
| Check manufacturer filters | 5 | Excluded Manufacturers |
| Check SSID distribution | 6 | SSID Distribution |
| Check signal strength | 7 | RSSI Distribution |
| Check views exist | 8 | Database Views Existence |
| Check historical data | 9 | Historical Data (Daily) |
| Check time distribution | 10 | Time Distribution (24h) |
| Check dashboard filters | 11 | Dashboard Network Selection |
| Check recent activity | 12 | Recent Webhook Activity |

---

## 🆘 Getting Help

If you've followed this guide and still see 0 passersby:

1. **Collect diagnostics:**
   - Run Master Diagnostic Query
   - Run Query 12 (Recent Activity)
   - Export results to share with team

2. **Check logs:**
   - Application logs for webhook errors
   - Meraki Dashboard logs for webhook activity

3. **Verify Meraki side:**
   - Login to Meraki Dashboard
   - Check Location and Scanning is enabled
   - Verify webhook shows recent posts
   - Test webhook manually

4. **Review recent changes:**
   - Check `changelog.MD` for recent code changes
   - Review database migrations
   - Check if configuration was modified

---

## ✅ Success Checklist

After fixing the issue, verify:

- [ ] Master diagnostic shows "OK - Should have data"
- [ ] Query 2 (Real-Time Window) shows `passersby_eligible > 0`
- [ ] Query 3 shows devices classified as PasserBy
- [ ] Frontend widget displays non-zero value
- [ ] Widget refreshes every 15 seconds
- [ ] Historical reports also show data
- [ ] Multiple networks showing data (if applicable)

---

**Last Updated**: 2025-01-10
**Related Files**:
- `DIAGNOSTIC_QUERIES_PASSERSBY.sql`
- `SQL/scanning_report/scanning_report_daily_unique.sql`