# Fix Plan: Call Stats Not Counting

**Based on:** `docs/INVESTIGATION.md`
**Priority:** CRITICAL
**Estimated Time:** 2-3 hours

---

## The Problem

1. **Team stats query uses wrong column:** `created_by` doesn't exist on `communications` table
2. **Should use:** `handled_by` (for call attribution) or `user_id` (for activity tracking)

---

## Fix 1: Update team.js Queries (CRITICAL)

**File:** `src/routes/team.js`

### Change 1: Line ~69-72 (today's comms query)

**Before:**
```js
const { data: todayComms } = await db
  .from('communications')
  .select('type, created_by, metadata')
  .gte('created_at', todayStart.toISOString())
  .in('created_by', userIds);
```

**After:**
```js
const { data: todayComms } = await db
  .from('communications')
  .select('type, handled_by, user_id, metadata, outcome')
  .gte('created_at', todayStart.toISOString())
  .or(`handled_by.in.(${userIds.join(',')}),user_id.in.(${userIds.join(',')})`);
```

### Change 2: Line ~76-79 (week's comms query)

**Before:**
```js
const { data: weekComms } = await db
  .from('communications')
  .select('type, created_by, metadata')
  .gte('created_at', weekStart.toISOString())
  .in('created_by', userIds);
```

**After:**
```js
const { data: weekComms } = await db
  .from('communications')
  .select('type, handled_by, user_id, metadata, outcome')
  .gte('created_at', weekStart.toISOString())
  .or(`handled_by.in.(${userIds.join(',')}),user_id.in.(${userIds.join(',')})`);
```

### Change 3: Line ~100 (filter logic)

**Before:**
```js
const todayUserComms = (todayComms || []).filter(c => c.created_by === userId);
```

**After:**
```js
const todayUserComms = (todayComms || []).filter(c => 
  c.handled_by === userId || c.user_id === userId
);
```

### Change 4: Line ~107 (week filter logic)

**Before:**
```js
const weekUserComms = (weekComms || []).filter(c => c.created_by === userId);
```

**After:**
```js
const weekUserComms = (weekComms || []).filter(c => 
  c.handled_by === userId || c.user_id === userId
);
```

### Change 5: Outcome counting (use denormalized column)

**Before:**
```js
const conversationsToday = todayUserComms.filter(c => {
  if (c.type !== 'call' && c.type !== 'call_outbound') return false;
  const outcome = c.metadata?.outcome;
  return outcome && !['no_answer', 'voicemail', 'busy', 'wrong_number'].includes(outcome);
}).length;
```

**After:**
```js
const conversationsToday = todayUserComms.filter(c => {
  if (c.type !== 'call' && c.type !== 'call_outbound') return false;
  // Use denormalized outcome column first, fallback to metadata
  const outcome = c.outcome || c.metadata?.outcome;
  return outcome && !['no_answer', 'voicemail', 'busy', 'wrong_number'].includes(outcome);
}).length;
```

---

## Fix 2: Update /:userId/stats Endpoint

**File:** `src/routes/team.js` (lines ~163-180)

### Change 1: Today's comms query

**Before:**
```js
const { data: todayComms } = await db
  .from('communications')
  .select('type, metadata, created_at')
  .eq('created_by', userId)
  .gte('created_at', todayStart.toISOString());
```

**After:**
```js
const { data: todayComms } = await db
  .from('communications')
  .select('type, metadata, outcome, created_at')
  .or(`handled_by.eq.${userId},user_id.eq.${userId}`)
  .gte('created_at', todayStart.toISOString());
```

### Change 2: Week's comms query

**Before:**
```js
const { data: weekComms } = await db
  .from('communications')
  .select('type, metadata, created_at')
  .eq('created_by', userId)
  .gte('created_at', weekStart.toISOString());
```

**After:**
```js
const { data: weekComms } = await db
  .from('communications')
  .select('type, metadata, outcome, created_at')
  .or(`handled_by.eq.${userId},user_id.eq.${userId}`)
  .gte('created_at', weekStart.toISOString());
```

---

## Fix 3: Update notion.js (Same Pattern)

**File:** `src/routes/notion.js`

4 occurrences to fix:

### Line 118
```js
// Before
.select('type, created_by, metadata')
// After
.select('type, handled_by, user_id, metadata')
```

### Line 123
```js
// Before
.select('type, created_by, metadata')
// After
.select('type, handled_by, user_id, metadata')
```

### Line 188
```js
// Before
const memberComms = todayComms.filter(c => c.created_by === member.user_id);
// After
const memberComms = todayComms.filter(c => 
  c.handled_by === member.user_id || c.user_id === member.user_id
);
```

### Line 255
```js
// Before
.select('type, metadata, created_at, created_by')
// After
.select('type, metadata, created_at, handled_by, user_id')
```

---

## Fix 4: Verify Dashboard Stats (Secondary)

**File:** `src/routes/dashboard.js`

The dashboard queries ALL communications (no user filter), so it should work. Verify:

1. Check that `getTodayStartAEST()` returns correct midnight UTC timestamp
2. Confirm calls exist in database with `type = 'call_outbound'`

If dashboard still shows 0, the issue might be:
- All calls were made "yesterday" in AEST
- Need to add better logging to diagnose

---

## Testing Plan

### After Fix

1. **Team Performance API:**
   ```bash
   curl -X GET "https://rateright-growth-production.up.railway.app/api/team/performance" \
     -H "Authorization: Bearer [token]"
   ```
   
   Verify Vena's stats show calls > 0

2. **Individual Stats:**
   ```bash
   curl -X GET "https://rateright-growth-production.up.railway.app/api/team/dc61845c-d191-4213-9616-3d4385056c1b/stats" \
     -H "Authorization: Bearer [token]"
   ```

3. **Dashboard Stats:**
   ```bash
   curl -X GET "https://rateright-growth-production.up.railway.app/api/dashboard/stats"
   ```

---

## Database Verification Query

Run this before/after to confirm fix:

```sql
-- Check calls by Vena
SELECT 
  type, 
  handled_by, 
  user_id, 
  outcome,
  created_at,
  metadata->>'outcome' as metadata_outcome
FROM communications 
WHERE (handled_by = 'dc61845c-d191-4213-9616-3d4385056c1b'::uuid
   OR user_id = 'dc61845c-d191-4213-9616-3d4385056c1b'::uuid)
  AND type IN ('call', 'call_outbound', 'call_inbound')
ORDER BY created_at DESC
LIMIT 30;
```

---

## Lessons Learned

1. **Column naming consistency:** Use same column name (`user_id` or `handled_by`) everywhere
2. **Test queries against actual schema:** Supabase silently ignores non-existent columns in SELECT
3. **Add validation:** Consider adding column existence checks in dev mode

---

## Files to Modify

| File | Priority | Changes |
|------|----------|---------|
| `src/routes/team.js` | CRITICAL | Replace `created_by` → `handled_by`/`user_id` |
| `src/routes/notion.js` | HIGH | Same pattern fix |
| `src/routes/dashboard.js` | VERIFY | Confirm timezone logic is correct |

---

## Rollout Checklist

- [ ] Update `src/routes/team.js`
- [ ] Update `src/routes/notion.js`
- [ ] Run test queries against database
- [ ] Deploy to Railway
- [ ] Verify team stats show Vena's calls
- [ ] Verify dashboard stats show all calls
- [ ] Update LESSONS.md with this pattern