-- Validate employee counts match between audit view and helper view
SELECT 
  a.tenant_id,
  a.business_id,
  a.period_label,
  a.employee_count AS audit_count,
  h.employee_count AS helper_count,
  ABS(CAST(a.employee_count AS NUMERIC) - CAST(h.employee_count AS NUMERIC)) AS diff
FROM audit_employee_headcount_timeline a
LEFT JOIN stage1_headcount_by_business_period h
  ON a.tenant_id = h.tenant_id 
  AND a.business_id = h.business_id 
  AND a.period_label = h.period_label
WHERE ABS(CAST(a.employee_count AS NUMERIC) - CAST(h.employee_count AS NUMERIC)) > 0;
-- Expected: 0 rows (perfect match)

-- Validate commission impact (loss_amount) ties to authoritative source
SELECT 
  c.tenant_id,
  c.business_id,
  c.churn_period,
  c.commission_at_peak AS churn_view_peak,
  c.commission_at_churn AS churn_view_churn,
  c.loss_amount AS churn_view_loss,
  s.peak_commission AS source_peak,
  s.churn_commission AS source_churn,
  (s.peak_commission - s.churn_commission) AS source_loss
FROM audit_business_churn_events c
LEFT JOIN (
  SELECT 
    tenant_id,
    business_id,
    SUM(CASE WHEN period_label = peak_period THEN allocated_commission_amount_raw ELSE 0 END) AS peak_commission,
    SUM(CASE WHEN period_label = churn_period THEN allocated_commission_amount_raw ELSE 0 END) AS churn_commission
  FROM view_commission_traceability
  GROUP BY tenant_id, business_id
) s ON c.tenant_id = s.tenant_id AND c.business_id = s.business_id
WHERE ABS(CAST(c.commission_at_peak AS NUMERIC) - CAST(s.peak_commission AS NUMERIC)) > CAST('0.01' AS NUMERIC)
   OR ABS(CAST(c.commission_at_churn AS NUMERIC) - CAST(s.churn_commission AS NUMERIC)) > CAST('0.01' AS NUMERIC)
   OR ABS(CAST(c.loss_amount AS NUMERIC) - CAST((s.peak_commission - s.churn_commission) AS NUMERIC)) > CAST('0.01' AS NUMERIC);
-- Expected: 0 rows (tolerance: $0.01)
-- Note: loss_amount should be positive (commission_at_peak > commission_at_churn for churned businesses)

-- Validate churn events are correctly identified with confidence levels
SELECT 
  churn_type,
  churn_confidence,
  COUNT(*) AS churn_count,
  COUNT(DISTINCT business_id) AS unique_businesses_churned
FROM audit_business_churn_events
WHERE tenant_id = 'creative_benefit_strategies'
GROUP BY churn_type, churn_confidence
ORDER BY churn_confidence DESC, churn_type;
-- Expected: 
-- - HIGH confidence churn events match manual review (FULL_CHURN or DISAPPEARED with >= grace_window)
-- - MEDIUM/LOW confidence events are flagged for review (may be data gaps)

-- Validate grace window logic for DISAPPEARED businesses
SELECT 
  business_id,
  business_name,
  last_active_period,
  churn_period,
  DATE_DIFF(churn_period, last_active_period, MONTH) AS months_absent,
  churn_confidence
FROM audit_business_churn_events
WHERE churn_type = 'DISAPPEARED'
  AND tenant_id = 'creative_benefit_strategies'
ORDER BY months_absent DESC;
-- Expected: DISAPPEARED businesses with HIGH confidence have >= 2 months absent (monthly) or >= 6 weeks (weekly)

-- Validate money/allocation views have stage3_component_id (REQUIRED)
SELECT 
  'audit_commission_summary' AS view_name,
  COUNT(*) AS total_rows,
  COUNT(stage3_component_id) AS rows_with_component_id,
  COUNT(*) - COUNT(stage3_component_id) AS rows_missing_component_id
FROM audit_commission_summary
UNION ALL
SELECT 
  'audit_agent_payout_trace',
  COUNT(*),
  COUNT(stage3_component_id),
  COUNT(*) - COUNT(stage3_component_id)
FROM audit_agent_payout_trace
UNION ALL
SELECT 
  'audit_business_churn_agent_impact',
  COUNT(*),
  COUNT(ARRAY_LENGTH(stage3_component_ids_at_peak)) + COUNT(ARRAY_LENGTH(stage3_component_ids_at_churn)),
  COUNT(*) - (COUNT(ARRAY_LENGTH(stage3_component_ids_at_peak)) + COUNT(ARRAY_LENGTH(stage3_component_ids_at_churn)))
FROM audit_business_churn_agent_impact;
-- Expected: rows_missing_component_id = 0 for money/allocation views (all rows traceable)

-- Validate headcount-only view has OPTIONAL stage3_component_id (may be NULL)
SELECT 
  'audit_employee_headcount_timeline' AS view_name,
  COUNT(*) AS total_rows,
  COUNT(ARRAY_LENGTH(stage3_component_ids)) AS rows_with_component_ids,
  COUNT(*) - COUNT(ARRAY_LENGTH(stage3_component_ids)) AS rows_without_component_ids
FROM audit_employee_headcount_timeline;
-- Expected: rows_without_component_ids >= 0 (NULL is valid for headcount-only view)

-- Validate lifecycle/churn views include stage3_component_ids when commission exists
SELECT 
  'audit_business_lifecycle' AS view_name,
  COUNT(*) AS total_rows,
  COUNT(ARRAY_LENGTH(stage3_component_ids)) AS rows_with_commission,
  COUNT(*) - COUNT(ARRAY_LENGTH(stage3_component_ids)) AS rows_without_commission
FROM audit_business_lifecycle;
-- Expected: rows_without_commission >= 0 (NULL/empty array is valid when no commission)

-- Validate tenant isolation: no cross-tenant data leakage
SELECT 
  'audit_commission_summary' AS view_name,
  COUNT(*) AS cross_tenant_rows
FROM audit_commission_summary a
INNER JOIN stage3_snapshots s3
  ON a.tenant_id != s3.tenant_id
  AND a.stage3_component_id = TO_HEX(SHA256(
    CONCAT(
      CAST(s3.tenant_id AS STRING), '|',
      CAST(s3.period_label AS STRING), '|',
      CAST(UPPER(TRIM(s3.business_name)) AS STRING), '|',
      CAST(s3.period_code AS STRING), '|',
      CAST(s3.agent_id AS STRING), '|',
      CAST(COALESCE(s3.commission_type, 'NULL') AS STRING), '|',
      CAST(COALESCE(s3.downline_agent_name, s3.agent_name, 'NULL') AS STRING)
    )
  ));
-- Expected: 0 rows (no cross-tenant matches)

-- Check for cross-tenant leakage in audit views
-- This query checks if any business_id or agent_id appears with mismatched tenant_id
SELECT 
  'audit_commission_summary' AS view_name,
  COUNT(*) AS leakage_count
FROM `payroll-bi-gauntlet.payroll_analytics.audit_commission_summary` acs
WHERE EXISTS (
  SELECT 1
  FROM `payroll-bi-gauntlet.payroll_analytics.dim_business_mapping` bm
  WHERE bm.business_id = acs.business_id
    AND bm.tenant_id != acs.tenant_id
)
UNION ALL
SELECT 
  'audit_agent_payout_trace' AS view_name,
  COUNT(*) AS leakage_count
FROM `payroll-bi-gauntlet.payroll_analytics.audit_agent_payout_trace` apt
WHERE EXISTS (
  SELECT 1
  FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots` s1
  WHERE TO_HEX(SHA256(
    CONCAT(
      CAST(s1.tenant_id AS STRING), '|',
      CAST(s1.period_label AS STRING), '|',
      CAST(s1.business_label AS STRING), '|',
      CAST(s1.member_id AS STRING), '|',
      CAST(s1.payee_agent_id AS STRING), '|',
      CAST(s1.period_code AS STRING), '|',
      CAST(s1.credit AS STRING), '|',
      CAST(s1.debit AS STRING), '|',
      CAST(s1.total AS STRING)
    )
  )) = apt.stage1_row_id
    AND s1.tenant_id != apt.tenant_id
);

-- Check for missing stage3_component_id in money/allocation views
SELECT 
  'audit_commission_summary' AS view_name,
  COUNT(*) AS rows_missing_component_id
FROM `payroll-bi-gauntlet.payroll_analytics.audit_commission_summary`
WHERE stage3_component_id IS NULL
UNION ALL
SELECT 
  'audit_agent_payout_trace' AS view_name,
  COUNT(*) AS rows_missing_component_id
FROM `payroll-bi-gauntlet.payroll_analytics.audit_agent_payout_trace`
WHERE stage3_component_id IS NULL
UNION ALL
SELECT 
  'audit_business_churn_agent_impact' AS view_name,
  COUNT(*) AS rows_missing_component_id
FROM `payroll-bi-gauntlet.payroll_analytics.audit_business_churn_agent_impact`
WHERE ARRAY_LENGTH(stage3_component_ids_at_peak) = 0
  AND ARRAY_LENGTH(stage3_component_ids_at_churn) = 0
  AND commission_at_peak > 0;  -- Only flag if commission exists but no component_ids

-- Compare headcount from audit views vs direct Stage1 count
WITH audit_headcounts AS (
  SELECT 
    tenant_id,
    business_id,
    period_label,
    employee_count AS audit_employee_count
  FROM `payroll-bi-gauntlet.payroll_analytics.stage1_headcount_by_business_period`
),
stage1_direct_counts AS (
  SELECT 
    tenant_id,
    period_label,
    UPPER(TRIM(business_label)) AS business_name_normalized,
    COUNT(DISTINCT member_id) AS direct_employee_count
  FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots`
  WHERE business_label IS NOT NULL
    AND TRIM(business_label) != ''
    AND period_label >= DATE('2024-01-01')
  GROUP BY tenant_id, period_label, UPPER(TRIM(business_label))
),
business_mapping AS (
  SELECT
    tenant_id,
    business_id,
    UPPER(TRIM(normalized_name)) AS normalized_name
  FROM `payroll-bi-gauntlet.payroll_analytics.dim_business_mapping`
  WHERE is_active = TRUE
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY tenant_id, UPPER(TRIM(normalized_name))
    ORDER BY COALESCE(last_seen_date, DATE '1900-01-01') DESC
  ) = 1
)
SELECT 
  ah.tenant_id,
  ah.business_id,
  ah.period_label,
  ah.audit_employee_count,
  s1.direct_employee_count,
  ABS(ah.audit_employee_count - s1.direct_employee_count) AS diff
FROM audit_headcounts ah
INNER JOIN business_mapping bm
  ON ah.tenant_id = bm.tenant_id
  AND ah.business_id = bm.business_id
INNER JOIN stage1_direct_counts s1
  ON ah.tenant_id = s1.tenant_id
  AND ah.period_label = s1.period_label
  AND bm.normalized_name = s1.business_name_normalized
WHERE ah.audit_employee_count != s1.direct_employee_count;

-- Verify churn view uses tenant_asof (deterministic) not CURRENT_DATE()
-- This query checks that asof_period is derived from MAX(period_label) per tenant
WITH tenant_asof_check AS (
  SELECT 
    tenant_id,
    MAX(period_label) AS expected_asof_period
  FROM `payroll-bi-gauntlet.payroll_analytics.stage1_headcount_by_business_period`
  GROUP BY tenant_id
),
churn_events_check AS (
  SELECT DISTINCT
    tenant_id,
    churn_period,
    last_active_period
  FROM `payroll-bi-gauntlet.payroll_analytics.audit_business_churn_events`
  WHERE churn_type = 'DISAPPEARED'
)
SELECT 
  ta.tenant_id,
  ta.expected_asof_period,
  ce.churn_period,
  ce.last_active_period,
  DATE_DIFF(ta.expected_asof_period, ce.last_active_period, DAY) AS days_since_last_active
FROM tenant_asof_check ta
LEFT JOIN churn_events_check ce
  ON ta.tenant_id = ce.tenant_id
WHERE ce.churn_period IS NOT NULL
  AND ce.churn_period > ta.expected_asof_period;  -- Should be 0 rows (churn_period should be <= asof_period)

-- Verify commission reconciliation: stage3_source_total vs allocated_total +/- unallocated_total
SELECT 
  tenant_id,
  period_label,
  business_id,
  stage3_component_id,
  stage3_source_total,
  allocated_total,
  unallocated_total,
  (allocated_total + unallocated_total) AS total_accounted,
  ABS(stage3_source_total - (allocated_total + unallocated_total)) AS abs_diff,
  reconciliation_status
FROM `payroll-bi-gauntlet.payroll_analytics.audit_commission_summary`
WHERE ABS(stage3_source_total - (allocated_total + unallocated_total)) > CAST('0.01' AS NUMERIC)
  AND reconciliation_status != 'RECONCILED'
ORDER BY abs_diff DESC
LIMIT 100;  -- Show top 100 reconciliation gaps

-- Check for DISAPPEARED events from tenants with insufficient history
WITH tenant_period_counts AS (
  SELECT 
    tenant_id,
    COUNT(DISTINCT period_label) AS total_periods
  FROM `payroll-bi-gauntlet.payroll_analytics.stage1_headcount_by_business_period`
  GROUP BY tenant_id
),
churn_events_by_tenant AS (
  SELECT 
    tenant_id,
    COUNT(*) AS disappeared_count,
    COUNTIF(churn_confidence = 'HIGH') AS high_confidence_count,
    COUNTIF(churn_confidence = 'LOW') AS low_confidence_count
  FROM `payroll-bi-gauntlet.payroll_analytics.audit_business_churn_events`
  WHERE churn_type = 'DISAPPEARED'
  GROUP BY tenant_id
)
SELECT 
  tpc.tenant_id,
  tpc.total_periods,
  COALESCE(ce.disappeared_count, 0) AS disappeared_count,
  COALESCE(ce.high_confidence_count, 0) AS high_confidence_count,
  COALESCE(ce.low_confidence_count, 0) AS low_confidence_count,
  CASE
    WHEN tpc.total_periods < 3 THEN 'INSUFFICIENT_HISTORY'
    WHEN tpc.total_periods < 6 THEN 'EARLY_HISTORY'
    ELSE 'SUFFICIENT_HISTORY'
  END AS history_category
FROM tenant_period_counts tpc
LEFT JOIN churn_events_by_tenant ce
  ON tpc.tenant_id = ce.tenant_id
WHERE COALESCE(ce.disappeared_count, 0) > 0
ORDER BY tpc.total_periods ASC, ce.high_confidence_count DESC;

SELECT tenant_id, business_id, agent_id, COUNT(*) c
FROM `payroll-bi-gauntlet.payroll_analytics.audit_business_churn_agent_impact`
GROUP BY 1,2,3
HAVING COUNT(*) > 1;

SELECT *
FROM `payroll-bi-gauntlet.payroll_analytics.audit_business_churn_agent_impact` a
JOIN `payroll-bi-gauntlet.payroll_analytics.audit_business_churn_events` e
  ON a.business_id = e.business_id
 AND a.tenant_id != e.tenant_id
LIMIT 1;

SELECT agent_role, COUNT(*) c
FROM `payroll-bi-gauntlet.payroll_analytics.audit_business_churn_agent_impact`
GROUP BY 1
ORDER BY c DESC;

-- Measure % of rows where role derivation fell back to UNKNOWN
-- grouped by tenant_id, business_id, and churn_period
SELECT
  tenant_id,
  business_id,
  churn_period,
  COUNT(*) AS total_rows,
  SUM(CASE WHEN agent_role = 'UNKNOWN' THEN 1 ELSE 0 END) AS unknown_rows,
  CAST(
    CASE
      WHEN COUNT(*) = 0 THEN NULL
      ELSE (CAST(SUM(CASE WHEN agent_role = 'UNKNOWN' THEN 1 ELSE 0 END) AS NUMERIC) / CAST(COUNT(*) AS NUMERIC)) * CAST(100 AS NUMERIC)
    END
  AS NUMERIC) AS pct_unknown
FROM `payroll-bi-gauntlet.payroll_analytics.audit_business_churn_agent_impact`
GROUP BY tenant_id, business_id, churn_period
ORDER BY pct_unknown DESC, total_rows DESC;

-- Verify percent_of_total_loss IS NULL when business total loss=0
-- and that no SAFE_DIVIDE/FLOAT64 exists in the view definition
WITH business_totals AS (
  SELECT
    tenant_id,
    business_id,
    SUM(CAST(loss_amount AS NUMERIC)) AS total_loss_amount
  FROM `payroll-bi-gauntlet.payroll_analytics.audit_business_churn_agent_impact`
  GROUP BY tenant_id, business_id
)
SELECT
  a.tenant_id,
  COUNTIF(a.percent_of_total_loss IS NULL) AS pct_nulls,
  COUNTIF(bt.total_loss_amount IS NULL OR CAST(bt.total_loss_amount AS NUMERIC) = CAST(0 AS NUMERIC)) AS zero_or_null_denom,
  COUNT(*) AS total
FROM `payroll-bi-gauntlet.payroll_analytics.audit_business_churn_agent_impact` a
LEFT JOIN business_totals bt
  ON a.tenant_id = bt.tenant_id
 AND a.business_id = bt.business_id
GROUP BY a.tenant_id;
-- Expect: pct_nulls should match zero_or_null_denom (NULL only when denom=0/null)
-- Also verify view definition has no SAFE_DIVIDE or FLOAT64 casts

-- Check view definition for SAFE_DIVIDE or FLOAT64
SELECT
  view_name,
  view_definition
FROM `payroll-bi-gauntlet.payroll_analytics.INFORMATION_SCHEMA.VIEWS`
WHERE view_name = 'audit_business_churn_agent_impact'
  AND (
    view_definition LIKE '%SAFE_DIVIDE%'
    OR view_definition LIKE '%FLOAT64%'
  );
-- Expect: 0 rows (no SAFE_DIVIDE or FLOAT64 in definition)