package ai

// M7 bid outcomes advisory base rate (a TAG aggregate intent, NOT a graph intent and NOT a bench intent).
// bid_win_rate_by_segment reports an advisory MARKET base rate over the caller's own bid_outcomes ledger:
// per customer agency segment (read from the linked requirement), the win count, the total decided (win
// plus loss, the denominator that excludes pending and no_bid), and the win rate as a numeric ratio. It is
// advisory context for a capture team, explicitly NOT a candidate score and NOT an employment decision
// number; the frozen matching engine stays the only scorer and no learned model consumes this read yet.
//
// Honesty and safety are enforced by construction. The denominator counts only decided bids (win plus
// loss), so a pending or no_bid bid never inflates or deflates the rate. The ratio is computed in SQL as a
// numeric divided by a numeric guarded with NULLIF so a segment with no decided bid yields a NULL rate, not
// a divide by zero; the Go side scans that ratio into a shopspring/decimal pointer (a NULL rate is a nil
// pointer), never a float64. It reads only bid_outcomes and requirements, both allowlisted tenant tables,
// never a person table, never a score, never the eeo schema. It takes no SQL parameter: it reports every
// segment of the caller's ledger, RLS scoped so one tenant's win rate can never surface in another's.
//
// The SQL is a reviewed static constant. ValidateRegistry asserts it through GuardReadOnlyQuery at boot and
// a unit test, and ExecuteIntent re guards it on every execution, so it can only ever run as a single read
// only SELECT over the allowlisted tables.

// IntentBidWinRateBySegment is the registered name of the advisory bid win rate base rate read.
const IntentBidWinRateBySegment = "bid_win_rate_by_segment"

// bidWinRateBySegmentSQL aggregates the caller's bid_outcomes by the customer agency of the linked
// requirement. The LEFT JOIN keeps outcomes whose requirement link is null (or whose requirement has no
// customer agency) under an "(unspecified)" segment label rather than dropping them. The denominator
// total_decided counts only win plus loss; the win rate is win_count over total_decided guarded with NULLIF
// so a segment with zero decided bids returns a NULL ratio, never a divide by zero. The numeric cast keeps
// the ratio exact for the shopspring/decimal scan in Go. Ordered by total decided descending with a segment
// tiebreaker for a stable, byte deterministic result.
const bidWinRateBySegmentSQL = `SELECT
    COALESCE(NULLIF(r.customer_agency, ''), '(unspecified)') AS customer_agency,
    count(*) FILTER (WHERE b.result = 'win')                  AS win_count,
    count(*) FILTER (WHERE b.result IN ('win', 'loss'))       AS total_decided,
    (count(*) FILTER (WHERE b.result = 'win'))::numeric
      / NULLIF(count(*) FILTER (WHERE b.result IN ('win', 'loss')), 0) AS win_rate
  FROM bid_outcomes b
  LEFT JOIN requirements r ON r.id = b.requirement_id
  GROUP BY COALESCE(NULLIF(r.customer_agency, ''), '(unspecified)')
  ORDER BY total_decided DESC, customer_agency`