WHERE s1c.allocated_commission_amount IS NOT NULL  -- Only rows with allocations

-- Remove WHERE clause entirely
-- Add these fields to SELECT:
CAST(CASE WHEN s1c.allocated_commission_amount IS NOT NULL THEN TRUE ELSE FALSE END AS BOOL) AS is_allocated,
CAST(
  CASE 
    WHEN s1c.allocated_commission_amount IS NOT NULL THEN 0
    ELSE s1c.stage1_total
  END AS NUMERIC(18, 2)
) AS unallocated_amount

TO_HEX(SHA256(
  CONCAT(
    CAST(tenant_id AS STRING), '|',
    CAST(period_label AS STRING), '|',
    CAST(business_label AS STRING), '|',
    CAST(member_id AS STRING), '|',
    CAST(payee_agent_id AS STRING), '|',
    CAST(period_code AS STRING)
  )
)) AS stage1_row_id

TO_HEX(SHA256(
  CONCAT(
    CAST(tenant_id AS STRING), '|',
    CAST(period_label AS STRING), '|',
    CAST(business_label AS STRING), '|',
    CAST(member_id AS STRING), '|',
    CAST(payee_agent_id AS STRING), '|',
    CAST(period_code AS STRING), '|',
    CAST(credit AS STRING), '|',
    CAST(debit AS STRING), '|',
    CAST(total AS STRING)
    -- Note: row index not available in stage1_snapshots schema, skip if not present
  )
)) AS stage1_row_id

CAST(s1c.stage1_credit AS NUMERIC(18, 2)) AS stage1_credit,
CAST(s1c.stage1_debit AS NUMERIC(18, 2)) AS stage1_debit,
CAST(s1c.stage1_total AS NUMERIC(18, 2)) AS stage1_total,
CAST(s1c.allocated_commission_amount AS NUMERIC(18, 2)) AS allocated_commission_amount,

-- Raw NUMERIC fields (full precision)
CAST(s1c.stage1_credit AS NUMERIC(38, 10)) AS stage1_credit_raw,
CAST(s1c.stage1_debit AS NUMERIC(38, 10)) AS stage1_debit_raw,
CAST(s1c.stage1_total AS NUMERIC(38, 10)) AS stage1_total_raw,
CAST(s1c.allocated_commission_amount AS NUMERIC(38, 10)) AS allocated_commission_amount_raw,

-- Rounded display fields (for UI)
ROUND(CAST(s1c.stage1_credit AS NUMERIC(38, 10)), 2) AS stage1_credit,
ROUND(CAST(s1c.stage1_debit AS NUMERIC(38, 10)), 2) AS stage1_debit,
ROUND(CAST(s1c.stage1_total AS NUMERIC(38, 10)), 2) AS stage1_total,
ROUND(CAST(s1c.allocated_commission_amount AS NUMERIC(38, 10)), 2) AS allocated_commission_amount,

-- Residual handling (for exact cent reconciliation)
CAST(
  CASE 
    WHEN s1c.allocated_commission_amount IS NOT NULL THEN
      s1c.stage1_total - s1c.allocated_commission_amount
    ELSE 0
  END AS NUMERIC(38, 10)
) AS allocation_residual_raw,
ROUND(
  CASE 
    WHEN s1c.allocated_commission_amount IS NOT NULL THEN
      s1c.stage1_total - s1c.allocated_commission_amount
    ELSE 0
  END, 2
) AS allocation_residual

-- Traceability method (how allocation was determined)
CAST(
  CASE
    WHEN s1c.allocated_commission_amount IS NOT NULL 
      AND ar.stage3_total_allocated IS NOT NULL 
      AND ar.stage3_total_allocated > 0 THEN 'PROPORTIONAL_ALLOCATION'
    WHEN s1c.allocated_commission_amount IS NOT NULL 
      AND (ar.stage3_total_allocated IS NULL OR ar.stage3_total_allocated = 0) THEN 'INFERRED'
    ELSE 'UNALLOCATED'
  END AS STRING
) AS traceability_method,

-- Flag for inferred allocations (always TRUE for proportional method)
CAST(
  CASE
    WHEN s1c.allocated_commission_amount IS NOT NULL 
      AND (ar.stage3_total_allocated IS NULL OR ar.stage3_total_allocated = 0) THEN TRUE
    ELSE FALSE
  END AS BOOL
) AS is_inferred_allocation

FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots`
WHERE payee_agent_id IN (721995, 668078, 0)  -- Robin, Richard, or backfilled
  AND business_label IS NOT NULL
  AND business_label != ''

FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots`
WHERE business_label IS NOT NULL
  AND business_label != ''
  -- Note: payee_agent_id filter removed - include all Stage1 records

INSERT INTO `payroll-bi-gauntlet.payroll_analytics.dim_rule_definitions` 
(tenant_id, rule_id, rule_name, rule_description, calculation_formula, rule_type, plan_version_id, effective_start_date, effective_end_date, is_active, created_at, updated_at)
VALUES
-- TPA PEPM Rate Rule
('creative_benefit_strategies', 'TPA_PEPM_RATE', 'TPA PEPM Rate Calculation', 
 'TPA Producing Agent commission calculated using PEPM rate', 
 '(pepm_rate * 12 / pay_periods) * employee_count',
 'TPA', NULL, DATE('2024-01-01'), NULL, TRUE, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP()),
...

MERGE `payroll-bi-gauntlet.payroll_analytics.dim_rule_definitions` AS target
USING (
  SELECT * FROM UNNEST([
    STRUCT(
      'creative_benefit_strategies' AS tenant_id,
      'TPA_PEPM_RATE' AS rule_id,
      'TPA PEPM Rate Calculation' AS rule_name,
      'TPA Producing Agent commission calculated using PEPM rate' AS rule_description,
      '(pepm_rate * 12 / pay_periods) * employee_count' AS calculation_formula,
      'TPA' AS rule_type,
      CAST(NULL AS STRING) AS plan_version_id,
      DATE('2024-01-01') AS effective_start_date,
      CAST(NULL AS DATE) AS effective_end_date,
      TRUE AS is_active,
      CURRENT_TIMESTAMP() AS created_at,
      CURRENT_TIMESTAMP() AS updated_at
    ),
    STRUCT(
      'creative_benefit_strategies' AS tenant_id,
      'OWNER_RESIDUAL' AS rule_id,
      'Owner Commission Residual' AS rule_name,
      'Owner commission = Stage 1 Gross - Sum(PEPM Agent Payouts)' AS rule_description,
      'gross_payout - sum(agent_payouts)' AS calculation_formula,
      'OWNER' AS rule_type,
      CAST(NULL AS STRING) AS plan_version_id,
      DATE('2024-01-01') AS effective_start_date,
      CAST(NULL AS DATE) AS effective_end_date,
      TRUE AS is_active,
      CURRENT_TIMESTAMP() AS created_at,
      CURRENT_TIMESTAMP() AS updated_at
    ),
    STRUCT(
      'creative_benefit_strategies' AS tenant_id,
      'MIXED_OWNER_RESIDUAL' AS rule_id,
      'MIXED_OWNER Per-Employee Residual' AS rule_name,
      'Per-employee residual when business has both TPA and owner commissions' AS rule_description,
      '(credit_per_employee - total_tpa_pepm_per_employee) * employee_count' AS calculation_formula,
      'MIXED_OWNER' AS rule_type,
      CAST(NULL AS STRING) AS plan_version_id,
      DATE('2024-01-01') AS effective_start_date,
      CAST(NULL AS DATE) AS effective_end_date,
      TRUE AS is_active,
      CURRENT_TIMESTAMP() AS created_at,
      CURRENT_TIMESTAMP() AS updated_at
    )
  ])
) AS source
ON target.tenant_id = source.tenant_id 
  AND target.rule_id = source.rule_id
WHEN MATCHED THEN
  UPDATE SET
    rule_name = source.rule_name,
    rule_description = source.rule_description,
    calculation_formula = source.calculation_formula,
    rule_type = source.rule_type,
    plan_version_id = source.plan_version_id,
    effective_start_date = source.effective_start_date,
    effective_end_date = source.effective_end_date,
    is_active = source.is_active,
    updated_at = source.updated_at
WHEN NOT MATCHED THEN
  INSERT (tenant_id, rule_id, rule_name, rule_description, calculation_formula, rule_type, plan_version_id, effective_start_date, effective_end_date, is_active, created_at, updated_at)
  VALUES (source.tenant_id, source.rule_id, source.rule_name, source.rule_description, source.calculation_formula, source.rule_type, source.plan_version_id, source.effective_start_date, source.effective_end_date, source.is_active, source.created_at, source.updated_at);

ARRAY_CONCAT(
  IF(bm.business_id IS NULL, ['MISSING_BUSINESS_MAPPING'], []),
  IF(ah.agent_id IS NULL, ['MISSING_HIERARCHY'], []),
  IF(rm.rule_id IS NULL, ['MISSING_METADATA:dim_rule_definitions'], []),
  -- NEW: Warn when Stage3 total is NULL (unallocated cash)
  IF(ar.stage3_total_allocated IS NULL, ['UNALLOCATED_CASH:stage3_total_allocated_is_null'], []),
  -- NEW: Warn when SAFE_DIVIDE returns NULL (division by zero or NULL inputs)
  IF(s1c.allocated_commission_amount IS NULL AND s1c.stage1_total > 0, ['ALLOCATION_FAILED:safe_divide_returned_null'], [])
) AS data_warning

(
  bm.business_id IS NULL
  OR ah.agent_id IS NULL
  OR rm.rule_id IS NULL
  OR ar.stage3_total_allocated IS NULL  -- NEW
  OR (s1c.allocated_commission_amount IS NULL AND s1c.stage1_total > 0)  -- NEW
) AS has_warning