# Find where data is being fetched
grep -r "get_.*_metrics" api/
grep -r "Mock\|mock\|fallback" api/

# Check existing BigQuery views
bq ls payroll_analytics
bq show --schema payroll_analytics.ceo_snapshot

# Identify which functions are being called
gcloud run services logs read fastapi-backend --region=us-central1 --limit=20

# Look for:
# - "Using mock data" warnings
# - "No data found" errors
# - Which BigQuery tables/views are being queried

# Get the view definition
bq show --format=prettyjson payroll_analytics.ceo_snapshot > current_view.json

# Read the SQL
cat integration/bigquery/sql/views/analytics_ceo_snapshot.sql

-- WRONG: Calculated in view
business_owner_commission = gross_payout * 0.2

-- CORRECT: Let API calculate from raw data
-- View should only provide: gross_payout, chargebacks, net_payout

-- Example: Simplified CEO snapshot view
CREATE OR REPLACE VIEW `payroll-bi-gauntlet.payroll_analytics.ceo_snapshot` AS
WITH stage1_aggregated AS (
  SELECT
    tenant_id,
    period_label,
    COUNT(DISTINCT business_label) as total_businesses,
    COUNT(DISTINCT member_id) as total_employees,
    SUM(credit) as gross_payout,
    SUM(debit) as chargebacks,
    SUM(total) as net_payout
  FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots`
  WHERE payee_agent_id IN (721995, 668078, 0)  -- Owner IDs
    AND business_label IS NOT NULL
    AND period_label >= '2024-01-01'  -- Include historical data
  GROUP BY tenant_id, period_label
)
SELECT
  tenant_id,
  period_label,
  total_businesses,
  total_employees,
  gross_payout,
  chargebacks,
  net_payout
FROM stage1_aggregated
ORDER BY period_label DESC;

# Test the SQL locally first
bq query --use_legacy_sql=false --dry_run < integration/bigquery/sql/views/analytics_ceo_snapshot.sql

# Deploy the view
Get-Content integration/bigquery/sql/views/analytics_ceo_snapshot.sql -Raw | bq query --use_legacy_sql=false

# Verify data exists
bq query --use_legacy_sql=false "
SELECT period_label, total_businesses, total_employees, gross_payout 
FROM payroll-bi-gauntlet.payroll_analytics.ceo_snapshot 
WHERE period_label IN ('2024-03-01', '2025-03-01') 
ORDER BY period_label;"

# WRONG: Querying old table
query = f"""
SELECT * 
FROM `{project_id}.commissions.ceo_snapshots`
WHERE period = DATE('{period_label}')
"""

# CORRECT: Using updated view
query = f"""
SELECT 
    total_businesses,
    total_employees,
    gross_payout,
    chargebacks,
    net_payout
FROM `{project_id}.payroll_analytics.ceo_snapshot`
WHERE period_label = DATE('{period_label}')
  AND tenant_id = '{tenant_id}'
"""

def get_ceo_metrics_simple(tenant_id: str, period_label: str):
    """CEO metrics using canonical calculation logic"""
    client = bigquery.Client(project=project_id)
    
    # Query 1: Get base metrics from view
    ceo_query = f"""
    SELECT 
        total_businesses,
        total_employees,
        gross_payout,
        chargebacks,
        net_payout
    FROM `{project_id}.payroll_analytics.ceo_snapshot`
    WHERE period_label = DATE('{period_label}')
      AND tenant_id = '{tenant_id}'
    """
    
    ceo_results = list(client.query(ceo_query).result())
    if not ceo_results:
        logger.warning(f"No data found for {period_label}")
        return None
    
    row = ceo_results[0]
    
    # Query 2: Get agent commissions for owner commission calculation
    agent_query = f"""
    SELECT
        SUM(agent_total) as agent_commissions
    FROM `{project_id}.payroll_processed.stage3_snapshots`
    WHERE period_label = DATE('{period_label}')
      AND tenant_id = '{tenant_id}'
      AND tpa_applied = TRUE
    """
    
    agent_results = list(client.query(agent_query).result())
    agent_commissions = float(agent_results[0].agent_commissions or 0) if agent_results and agent_results[0].agent_commissions else 0
    
    # Calculate owner commission using CANONICAL formula
    # Owner Commission = Gross - Chargebacks - Agent Commissions
    gross_payout = float(row.gross_payout or 0)
    chargebacks = float(row.chargebacks or 0)
    business_owner_commission = gross_payout - chargebacks - agent_commissions
    
    return {
        "period_label": period_label,
        "tenant_id": tenant_id,
        "total_businesses": int(row.total_businesses or 0),
        "total_employees": int(row.total_employees or 0),
        "gross_payout": gross_payout,
        "business_owner_commission": business_owner_commission,
        "total_commission": agent_commissions,
        "total_chargebacks": chargebacks,
        # Calculated fields
        "avg_commission_per_policy": gross_payout / max(int(row.total_businesses or 1), 1),
        "avg_commission_per_life": gross_payout / max(int(row.total_employees or 1), 1)
    }

SELECT
    SUM(gross_payout) AS gross_payout,
    SUM(chargebacks) AS chargebacks,
    SUM(net_payout) AS net_payout
FROM `payroll-bi-gauntlet.payroll_analytics.ceo_snapshot`
WHERE period_label BETWEEN @start_date AND @end_date
  AND tenant_id = @tenant_id

SELECT
    COUNT(DISTINCT business_label) AS total_businesses_ytd,
    COUNT(DISTINCT member_id) AS total_employees_ytd
FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots`
WHERE period_label >= @start_date
  AND period_label <= @end_date
  AND tenant_id = @tenant_id
  AND payee_agent_id IN (721995, 668078, 0)
  AND business_label IS NOT NULL
  AND business_label != ''

SELECT
    COUNT(DISTINCT business_label) AS active_businesses,
    COUNT(DISTINCT member_id) AS total_employees
FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots`
WHERE period_label = @end_date
  AND tenant_id = @tenant_id
  AND payee_agent_id IN (721995, 668078, 0)
  AND business_label IS NOT NULL
  AND business_label != ''

-- Date range calculation (QTD):
-- start_date = DATE(EXTRACT(YEAR FROM @period_label), (QUARTER - 1) * 3 + 1, 1)
-- end_date = @period_label
SELECT 
    COUNT(DISTINCT business_label) as total_businesses
FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots`
WHERE period_label >= (SELECT start_date FROM date_range)
  AND period_label <= (SELECT end_date FROM date_range)
  AND tenant_id = @tenant_id
  AND payee_agent_id IN (721995, 668078, 0)
  AND business_label IS NOT NULL
  AND business_label != ''

-- ceo_snapshot view already includes MoM fields:
SELECT
    gross_payout_change_mom,
    business_change_mom,
    employee_change_mom
FROM `payroll-bi-gauntlet.payroll_analytics.ceo_snapshot`
WHERE period_label = @period_label
  AND tenant_id = @tenant_id

{
  "period_label": "2025-10-01",
  "tenant_id": "creative_benefit_strategies",
  "total_businesses": 221,  // Backward compatibility (maps to total_businesses_ytd)
  "total_businesses_ytd": 221,  // Distinct count across date range
  "active_businesses": 96,  // Distinct count at end period (only for YTD/QTD)
  "total_employees": 1720,  // Employees at end period
  "gross_payout": 195990.48,
  "chargebacks": 0,
  "net_payout": 195990.48,
  "business_owner_commission": 185368.19,
  "total_commission": 10534.29
}

**Example**: For `period_label = '2025-10-01'`:
- `start_date` = `2025-10-01` (Q4 start)
- `end_date` = `2025-10-01` (selected period)
- Aggregates Q4 2025 data (October only in this case)

**Quarter Boundaries**:
- Q1: January 1 - March 31
- Q2: April 1 - June 30
- Q3: July 1 - September 30
- Q4: October 1 - December 31

### Key Differences from Single-Period Queries

1. **Date Range Filtering**: Uses `>=` and `<=` instead of `=`
2. **Aggregation Functions**: Uses `SUM()`, `COUNT(DISTINCT ...)` across multiple periods
3. **Table Source**: Queries underlying tables (`stage1_snapshots`, `stage3_snapshots`) instead of views
4. **Performance**: May be slower due to scanning multiple partitions (optimize with partitioned filters)

### Performance Considerations

- **Partitioned Tables**: Ensure `period_label` filters use partitioned columns
- **Clustering**: Use tenant_id clustering for efficient multi-tenant queries
- **Query Timeout**: Set 30s timeout with graceful degradation
- **Caching**: Consider Redis cache for frequently accessed YTD/QTD queries

### 3.4 Handle None Values and Edge Cases
**Common pitfalls:**
```python
# BAD: Will fail if any value is None
total = sum(item.get('value', 0) for item in data)

# GOOD: Handle None explicitly
total = sum(item.get('value', 0) or 0 for item in data)

# GOOD: Safe division
avg = value / max(count, 1)  # Prevents division by zero

# Remove these patterns:
client = None  # Forces mock data
# Force mock data for now
BIGQUERY_AVAILABLE = False

try:
    client = get_client()
    if client is None:
        logger.warning("BigQuery client not available, using mock data")
    else:
        logger.info(f"BigQuery client initialized for project: {PROJECT_ID}")
except Exception as e:
    logger.error(f"Failed to initialize BigQuery client: {e}")
    client = None

// Check what fields the API schema expects
// File: dashboard/src/lib/api-client.ts

interface CEOMetrics {
    period_label: string;
    tenant_id: string;
    total_commission: string;  // Note: string, not number!
    total_policies: number;
    total_lives: number;
    total_businesses: number;
    avg_commission_per_policy: string;
    avg_commission_per_life: string;
}

// Add 2024 periods to dropdown
const periods = [
  // 2024 months
  { value: '2024-01-01', label: 'January 2024' },
  { value: '2024-03-01', label: 'March 2024' },
  { value: '2024-06-01', label: 'June 2024' },
  { value: '2024-12-01', label: 'December 2024' },
  // 2025 months
  { value: '2025-01-01', label: 'January 2025' },
  // ... etc
];

// Add feature flag
const USE_FALLBACK = process.env.NEXT_PUBLIC_USE_CEO_FALLBACK === 'true';

if (USE_FALLBACK && selectedPeriod.startsWith('2024-')) {
  console.log('⚠️ Using hardcoded fallback data');
  // Fallback logic here
}

// ERROR: Type 'number' is not assignable to type 'string'
total_commission: fallbackData.total_commission  // ❌

// FIX: Convert to string
total_commission: fallbackData.total_commission.toString()  // ✅

// ERROR: Object literal may only specify known properties
ceoMetricsResponse = {
  ...ceoMetricsResponse,
  total_employees: 100,  // ❌ Not in type definition
  gross_payout: 5000     // ❌ Not in type definition
}

// FIX: Only include defined properties
ceoMetricsResponse = {
  ...ceoMetricsResponse,
  total_businesses: fallbackData.total_businesses,  // ✅
  total_policies: fallbackData.total_employees      // ✅
}

// File: dashboard/src/lib/apiClient.ts
const API_BASE_URL = process.env.NEXT_PUBLIC_API_URL || 
  'https://fastapi-backend-238826317621.us-central1.run.app';

# Get the service account being used
gcloud run services describe fastapi-backend \
  --region us-central1 \
  --format='value(spec.template.spec.serviceAccountName)'

# Output: fastapi-service-account@payroll-bi-gauntlet.iam.gserviceaccount.com

# Required roles for BigQuery access
gcloud projects add-iam-policy-binding payroll-bi-gauntlet \
  --member="serviceAccount:fastapi-service-account@payroll-bi-gauntlet.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataViewer"

gcloud projects add-iam-policy-binding payroll-bi-gauntlet \
  --member="serviceAccount:fastapi-service-account@payroll-bi-gauntlet.iam.gserviceaccount.com" \
  --role="roles/bigquery.user"

# Deploy with required env vars
gcloud run deploy fastapi-backend \
  --source . \
  --region us-central1 \
  --allow-unauthenticated \
  --set-env-vars GCP_PROJECT_ID=payroll-bi-gauntlet

# Build and deploy to Cloud Run
gcloud run deploy fastapi-backend \
  --source . \
  --region us-central1 \
  --allow-unauthenticated \
  --set-env-vars GCP_PROJECT_ID=payroll-bi-gauntlet

# Note the service URL from output
# Service URL: https://fastapi-backend-238826317621.us-central1.run.app

# Test with a known period
curl "https://fastapi-backend-238826317621.us-central1.run.app/api/v1/ceo-metrics?period_label=2024-03-01"

# Expected response:
# {
#   "period_label": "2024-03-01",
#   "tenant_id": "creative_benefit_strategies",
#   "total_businesses": 33,
#   "total_employees": 484,
#   "gross_payout": 4517.78,
#   "business_owner_commission": 2988.06,
#   ...
# }

# Monitor for errors
gcloud run services logs read fastapi-backend --region=us-central1 --limit=20

# Look for:
# ✅ "BigQuery client initialized successfully"
# ✅ "Loaded CEO metrics from BigQuery"
# ❌ "Using mock data" - Should NOT appear
# ❌ "No data found" - Investigate if present

# Commit changes
git add dashboard/src/pages/ceo/index.tsx dashboard/src/lib/apiClient.ts
git commit -m "fix: Connect CEO dashboard to live BigQuery data"
git push

# Vercel will auto-deploy from GitHub
# Monitor build at: https://vercel.com/dashboard

-- Expected results for March 2024
SELECT 
    total_businesses,  -- Should be 33
    total_employees,   -- Should be 484
    gross_payout,      -- Should be ~4517.78
    chargebacks        -- Should be 0
FROM `payroll-bi-gauntlet.payroll_analytics.ceo_snapshot`
WHERE period_label = '2024-03-01';

-- Calculate owner commission manually
SELECT 
    4517.78 - 0 - 1529.72 as owner_commission;  -- Should be 2988.06

# Consider removing or deprecating mock data functions
# Keep them if needed for local development without BigQuery
def get_mock_ceo_metrics(tenant_id: str, period_label: str):
    """DEPRECATED: Only for local dev without BigQuery access"""
    logger.warning("Using mock data - this should only happen in local development")
    # ... mock data ...

# Add docstring with calculation details
def get_ceo_metrics(tenant_id: str, period_label: str):
    """
    Get CEO-level metrics for a specific period from BigQuery.
    
    Calculation Logic:
    - Owner Commission = Gross Payout - Chargebacks - Agent Commissions
    - Agent Commissions = SUM(agent_total) WHERE tpa_applied = TRUE
    
    Data Sources:
    - Base metrics: payroll_analytics.ceo_snapshot view
    - Agent commissions: payroll_processed.stage3_snapshots table
    
    Args:
        tenant_id: Tenant identifier (e.g., 'creative_benefit_strategies')
        period_label: Period in YYYY-MM-01 format (e.g., '2024-03-01')
    
    Returns:
        Dict with CEO metrics including calculated owner commission
    
    Raises:
        Exception: If BigQuery queries fail or no data found
    """

# Save a snapshot of the working state
git tag -a "ceo-dashboard-bigquery-v1" -m "CEO dashboard successfully migrated to BigQuery"
git push origin ceo-dashboard-bigquery-v1

- [ ] Create `analytics_agent_performance.sql` view
- [ ] Filter by specific agent ID (from JWT/session)
- [ ] Join with business data for client lists
- [ ] Calculate agent-specific metrics (commission, client retention, etc.)
- [ ] Create `api/routes/agent_metrics.py` endpoint
- [ ] Implement `get_agent_metrics()` function in queries.py
- [ ] Update `dashboard/src/pages/agent/index.tsx`
- [ ] Add agent-specific period filters
- [ ] Grant BigQuery permissions if using different service account
- [ ] Test with multiple agent IDs to ensure data isolation

# Replace
total = value1 + value2

# With
total = (value1 or 0) + (value2 or 0)

Backend:
- main.py (get_ceo_metrics_simple function)
- api/bigquery/queries.py (get_ceo_metrics function)
- integration/bigquery/sql/views/analytics_ceo_snapshot.sql

Frontend:
- dashboard/src/pages/ceo/index.tsx
- dashboard/src/lib/apiClient.ts

Configuration:
- Cloud Run environment variables
- Service account IAM permissions