package ai

// G7 agency buying pattern graph. This file adds the buyer side capture intelligence as FIXED, named,
// parameterized QueryIntents over the GLOBAL public market tables: opportunity_records (the unified catalog
// that carries the full (agency, naics, psc, set_aside, contract_vehicle, value) tuple), usaspending_awards
// (the federal award cache, the obligation dollars), and sam_opportunities (the SAM solicitation feed whose
// full_parent_path is the dotted agency hierarchy). Where G1 reads the competitor field and G9 reads the
// recompete lifecycle, G7 reads how a BUYER behaves: the vehicle mix and set aside mix it favors, the NAICS
// and PSC distribution it spreads across, the award value band it lives in, the recipient concentration in
// its segment, and the obligation trend over fiscal periods. The capture team reads it as where to position.
//
// The graph NEVER computes a match score, a band, or any employment decision number. A vehicle mix, a set
// aside share, a median value band, a recipient dollar share are MARKET aggregates, structure the capture
// team reads, and they are explicitly NOT match scores; the frozen engine in internal/modules/matching/engine
// stays the only scorer and is untouched. The model never authors this SQL: every statement is a static,
// reviewed constant, registered in the closed Registry, asserted by ValidateRegistry at boot, and re guarded
// by GuardReadOnlyQuery on every execution.
//
// Honesty mandates from the GraphRAG review, encoded by construction:
//   - the agency rollup is a split_part(full_parent_path, '.', 1) string split plus a GROUP BY, NOT a
//     recursive CTE, because the sam_opportunities hierarchy is already a materialized dotted path; recursion
//     would add nothing but a determinism risk.
//   - usaspending_awards carries NO psc, NO set_aside, and NO pop_state column, so the award dollar totals and
//     the vendor concentration key ONLY on (awarding_agency, naics_code). The finer slices (the vehicle mix,
//     the set aside mix, the NAICS and PSC distribution, the value band) come from opportunity_records, which
//     does carry the full tuple, and are honestly opportunity_records sourced. usaspending_awards also has no
//     award_date column, so the spend trend keys the award fiscal period on the only date the award carries,
//     pop_start_date.
//   - every recipient or incumbent edge is labeled prime-of-record (the constant label), never the staffing
//     subcontractor, because the sub award labor reality is absent from the public award data; is_galapagos
//     labels the tenant's own past performance, which is a public award flag, never a tenant table read.
//
// Decimal, not float. Every dollar figure (estimated_value, award_amount), every average, every median (via
// percentile_cont, a numeric), and every recency weighted share is Postgres numeric end to end and is scanned
// into shopspring/decimal in Go, NEVER float64 and NEVER cast through ::float8: a surfaced market figure is a
// decision adjacent number and the platform's decimal rule applies. Nullable text columns are COALESCEd to
// the empty string and nullable numerics are kept as decimal pointers (the G9 NULL discipline), so a row with
// a missing slice scans cleanly.

import (
	"context"
	"errors"

	"github.com/jackc/pgx/v5"
	"github.com/shopspring/decimal"

	"github.com/missionworkforce/missionmatch/internal/platform/tenant"
)

// The registered names of the three G7 agency buying pattern intents. Each is a FIXED entry in the closed
// Registry in tag.go, so ValidateRegistry asserts it at boot and ExecuteIntent runs it through the same
// guard, tenant transaction, and audit posture as every other TAG intent.
const (
	IntentAgencyBuyingPatternDetail = "agency_buying_pattern_detail"
	IntentAgencyVendorConcentration = "agency_vendor_concentration"
	IntentAgencySpendTrend          = "agency_spend_trend"
)

// agencyBuyingPatternDetailSQL is the FIXED buyer detail. Given an agency or department prefix ($1) and an
// optional NAICS ($2, the empty string means every NAICS), it returns, per slice of the buyer's
// opportunity_records, the vehicle mix, the set aside mix, the NAICS and PSC distribution, the contributing
// record count (the evidence), and the average and median estimated_value (the award value band), PLUS the
// usaspending_awards obligation totals keyed on (agency, naics), so the capture team reads where the buyer
// spends and where to position.
//
// The agency rollup is the sam_opportunities full_parent_path string split, NOT a recursive CTE: the buyer is
// matched when the first dotted segment of the path equals the prefix, or the prefix is the whole path, or the
// prefix is a leading segment, so a department prefix rolls up all its sub agencies through a split_part and a
// GROUP BY. The slice distribution is honestly opportunity_records sourced (the only table with the full
// (naics, psc, set_aside, contract_vehicle, estimated_value) tuple); the award dollar totals are honestly
// keyed on (awarding_agency, naics_code), the only keys usaspending_awards carries, and are LEFT JOINed by
// (agency, naics) so an opportunity slice with no matching award still appears with a zero award total.
//
// Honesty and decimal, line by line:
//   - nullable text slices (naics, psc, set_aside, contract_vehicle) are COALESCEd to the empty string so a
//     record with a missing slice still groups cleanly (the G9 NULL lesson),
//   - the average is avg(estimated_value), a numeric, and the median is percentile_cont(0.5) within group
//     ordered by estimated_value, also a numeric; both stay numeric end to end and are scanned into decimal
//     pointers (null when no priced record contributes), never float,
//   - the award dollar total is sum(award_amount) over the matching (agency, naics) awards, a numeric scanned
//     into a decimal pointer,
//   - every list ORDER BY ends with a total order over the grouped slice keys, so the same inputs yield byte
//     identical rows.
const agencyBuyingPatternDetailSQL = `
WITH agency_match AS (
    SELECT DISTINCT s.department, s.sub_agency
    FROM sam_opportunities s
    WHERE split_part(s.full_parent_path, '.', 1) = $1
       OR s.full_parent_path = $1
       OR s.full_parent_path LIKE ($1 || '.%')
), buyer_opps AS (
    SELECT
        coalesce(o.naics, '')            AS naics,
        coalesce(o.psc, '')              AS psc,
        coalesce(o.set_aside, '')        AS set_aside,
        coalesce(o.contract_vehicle, '') AS contract_vehicle,
        o.estimated_value                AS estimated_value
    FROM opportunity_records o
    WHERE ($2 = '' OR o.naics = $2)
      AND (
            o.agency = $1
         OR EXISTS (
                SELECT 1 FROM agency_match am
                WHERE am.department = o.agency OR am.sub_agency = o.agency
            )
      )
), sliced AS (
    SELECT
        b.naics,
        b.psc,
        b.set_aside,
        b.contract_vehicle,
        count(*)                                                                    AS record_count,
        count(b.estimated_value)                                                    AS priced_count,
        avg(b.estimated_value)::numeric                                             AS avg_value,
        percentile_cont(0.5) WITHIN GROUP (ORDER BY b.estimated_value)::numeric     AS median_value
    FROM buyer_opps b
    GROUP BY b.naics, b.psc, b.set_aside, b.contract_vehicle
), award_rollup AS (
    -- One row per award, matched when the award's agency is the queried agency OR a node in the queried
    -- agency's full_parent_path hierarchy. The OR EXISTS keeps the two match paths DISJOINT so an award
    -- whose agency is both the direct match and a hierarchy node is counted ONCE, never doubled (the prior
    -- UNION ALL of a direct arm and a hierarchy arm overlapped and inflated the dollars up to twice).
    SELECT
        a.naics_code                       AS naics,
        sum(a.award_amount)::numeric       AS award_dollars,
        count(*)                           AS award_count
    FROM usaspending_awards a
    WHERE a.award_amount IS NOT NULL
      AND ($2 = '' OR a.naics_code = $2)
      AND (
            a.awarding_agency = $1
            OR EXISTS (
                SELECT 1 FROM agency_match am
                WHERE a.awarding_agency = am.department OR a.awarding_agency = am.sub_agency
            )
          )
    GROUP BY a.naics_code
)
SELECT
    s.naics,
    s.psc,
    s.set_aside,
    s.contract_vehicle,
    s.record_count,
    s.priced_count,
    s.avg_value                         AS avg_value,
    s.median_value                      AS median_value,
    coalesce(ar.award_dollars, 0)::numeric AS award_dollars,
    coalesce(ar.award_count, 0)         AS award_count
FROM sliced s
LEFT JOIN award_rollup ar ON ar.naics = s.naics
ORDER BY s.record_count DESC, s.naics, s.psc, s.set_aside, s.contract_vehicle`

// agencyVendorConcentrationSQL is the FIXED vendor concentration. Given an agency ($1) and a NAICS ($2), it
// returns, per recipient UEI in that (agency, naics) award segment, the recency weighted dollar share, ordered
// share DESC with a UEI tiebreaker, so a dominant share reads wired and a fragmented field reads open, with
// is_galapagos labeling the tenant's own past performance. It reuses the G1 segment_competitor_field recency
// pattern (a numeric step function on the period of performance end, the more recent the heavier the weight),
// kept inside the numeric type so the weighted share stays exact decimal.
//
// The segment key is ONLY (awarding_agency, naics_code), the only keys usaspending_awards carries; the
// recipient edge is implicitly prime-of-record (the public award data carries only the prime recipient). The
// share is the recipient's recency weighted dollars over the segment total recency weighted dollars, computed
// in numeric over a window, never float. The ORDER BY ends with the recipient_uei tiebreaker, a total order
// over the grouped rows, so the same inputs yield byte identical rows.
const agencyVendorConcentrationSQL = `
WITH segment AS (
    SELECT
        a.recipient_uei,
        a.recipient_name,
        a.is_galapagos,
        a.award_amount,
        CASE
            WHEN a.pop_end_date >= (now()::date - (5 * 365)) THEN 1.00::numeric
            WHEN a.pop_end_date >= (now()::date - (10 * 365)) THEN 0.50::numeric
            ELSE 0.25::numeric
        END AS recency_weight
    FROM usaspending_awards a
    WHERE a.awarding_agency = $1
      AND a.naics_code = $2
      AND a.award_amount IS NOT NULL
), weighted AS (
    SELECT
        recipient_uei,
        max(recipient_name)                          AS recipient_name,
        bool_or(is_galapagos)                        AS is_galapagos,
        count(*)                                     AS award_count,
        sum(award_amount * recency_weight)::numeric  AS weighted_dollars
    FROM segment
    GROUP BY recipient_uei
)
SELECT
    w.recipient_uei,
    w.recipient_name,
    w.is_galapagos,
    w.award_count,
    w.weighted_dollars,
    CASE
        WHEN sum(w.weighted_dollars) OVER () = 0 THEN 0::numeric
        ELSE (w.weighted_dollars / sum(w.weighted_dollars) OVER ())::numeric
    END AS dollar_share
FROM weighted w
ORDER BY dollar_share DESC, w.recipient_uei`

// agencySpendTrendSQL is the FIXED obligation trend. Given an agency ($1) and a NAICS ($2), it returns the
// agency's obligation total and award count per fiscal period (the year) in that NAICS over usaspending_awards,
// so the capture team reads whether the buyer's spend is concentrating or fragmenting. usaspending_awards has
// NO award_date column, so the fiscal period is keyed on the only date the award carries, pop_start_date (the
// award's period of performance start, the honest period anchor); a row with a null pop_start_date carries no
// fiscal year and is excluded from the trend rather than bucketed under a fabricated year.
//
// The fiscal period is computed with date_part('year', ...), the function form of the year extraction, not the
// extract(year FROM ...) keyword form, deliberately: the read only guard's table reference scan treats a bare
// FROM token as a table reference, so the function form keeps the year extraction without tripping the guard,
// the same result with no FROM keyword inside an expression.
//
// The segment key is ONLY (awarding_agency, naics_code), the only keys usaspending_awards carries. The
// obligation total is sum(award_amount), a numeric scanned into decimal, never float. The result is ordered by
// the fiscal period ascending so the trend reads forward in time, with no further tiebreaker needed because the
// fiscal period is the unique grouping key (one row per year), a total order on its own.
const agencySpendTrendSQL = `
SELECT
    date_part('year', a.pop_start_date)::int  AS fiscal_period,
    count(*)                                  AS award_count,
    sum(a.award_amount)::numeric              AS obligation_dollars
FROM usaspending_awards a
WHERE a.awarding_agency = $1
  AND a.naics_code = $2
  AND a.award_amount IS NOT NULL
  AND a.pop_start_date IS NOT NULL
GROUP BY date_part('year', a.pop_start_date)::int
ORDER BY fiscal_period`

// BuyingPatternDetailRow is one slice of an agency's buying pattern: the NAICS, PSC, set aside, and contract
// vehicle of the buyer's opportunities, the contributing record count and the priced count (the evidence), the
// average and median estimated value (the award value band, decimal pointers, null when no priced record
// contributes), and the usaspending_awards obligation total and award count for that (agency, naics). It is
// opportunity_records sourced for the slice and award sourced for the dollar total. It carries no score.
type BuyingPatternDetailRow struct {
	NAICS           string           `json:"naics"`
	PSC             string           `json:"psc"`
	SetAside        string           `json:"set_aside"`
	ContractVehicle string           `json:"contract_vehicle"`
	RecordCount     int              `json:"record_count"`
	PricedCount     int              `json:"priced_count"`
	AvgValue        *decimal.Decimal `json:"avg_value,omitempty"`
	MedianValue     *decimal.Decimal `json:"median_value,omitempty"`
	AwardDollars    decimal.Decimal  `json:"award_dollars"`
	AwardCount      int              `json:"award_count"`
}

// AgencyBuyingPatternDetail runs the registered agency_buying_pattern_detail intent for an agency or department
// prefix and an optional NAICS filter, scanning the value band (average and median) and the award dollars into
// decimal. The agency rollup is the full_parent_path string split, NOT a recursive CTE. The average and the
// median are nullable decimal pointers, so a slice with no priced record scans cleanly (the G9 NULL lesson).
func AgencyBuyingPatternDetail(ctx context.Context, db *tenant.DB, agencyPrefix, naics string) ([]BuyingPatternDetailRow, error) {
	if db == nil {
		return nil, errors.New("ai: no database")
	}
	var out []BuyingPatternDetailRow
	err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		if err := GuardReadOnlyQuery(agencyBuyingPatternDetailSQL, ReadAllowlist); err != nil {
			return err
		}
		rows, err := tx.Query(ctx, agencyBuyingPatternDetailSQL, agencyPrefix, naics)
		if err != nil {
			return err
		}
		defer rows.Close()
		for rows.Next() {
			var r BuyingPatternDetailRow
			if err := rows.Scan(&r.NAICS, &r.PSC, &r.SetAside, &r.ContractVehicle, &r.RecordCount,
				&r.PricedCount, &r.AvgValue, &r.MedianValue, &r.AwardDollars, &r.AwardCount); err != nil {
				return err
			}
			out = append(out, r)
		}
		return rows.Err()
	})
	return out, err
}

// VendorConcentrationRow is one recipient in an agency's (agency, naics) award segment: the recipient, whether
// it is the tenant's own past performance (is_galapagos), the contributing award count (the evidence), the
// recency weighted dollars, and the recency weighted dollar share, both decimal, never float. It is pure
// market structure: there is no match score and no employment decision number on it. The recipient edge is the
// prime recipient of record, the only vendor the public award data carries.
type VendorConcentrationRow struct {
	RecipientUEI    string          `json:"recipient_uei"`
	RecipientName   string          `json:"recipient_name"`
	IsGalapagos     bool            `json:"is_galapagos"`
	AwardCount      int             `json:"award_count"`
	WeightedDollars decimal.Decimal `json:"weighted_dollars"`
	DollarShare     decimal.Decimal `json:"dollar_share"`
}

// AgencyVendorConcentration runs the registered agency_vendor_concentration intent for an agency and a NAICS,
// scanning the weighted dollars and the share into decimal (the numeric columns are scanned directly into
// decimal, never through float64). The segment keys on (agency, naics) the only way usaspending_awards
// supports; the recency weight is the G1 numeric step function, so the weighted share stays exact decimal.
func AgencyVendorConcentration(ctx context.Context, db *tenant.DB, agency, naics string) ([]VendorConcentrationRow, error) {
	if db == nil {
		return nil, errors.New("ai: no database")
	}
	var out []VendorConcentrationRow
	err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		if err := GuardReadOnlyQuery(agencyVendorConcentrationSQL, ReadAllowlist); err != nil {
			return err
		}
		rows, err := tx.Query(ctx, agencyVendorConcentrationSQL, agency, naics)
		if err != nil {
			return err
		}
		defer rows.Close()
		for rows.Next() {
			var r VendorConcentrationRow
			if err := rows.Scan(&r.RecipientUEI, &r.RecipientName, &r.IsGalapagos, &r.AwardCount,
				&r.WeightedDollars, &r.DollarShare); err != nil {
				return err
			}
			out = append(out, r)
		}
		return rows.Err()
	})
	return out, err
}

// SpendTrendRow is one fiscal period of an agency's obligation trend in a NAICS: the period (the year), the
// award count in that period (the evidence), and the obligation total (decimal, never float). Reading the
// periods in order tells the capture team whether the buyer's spend is concentrating or fragmenting. It
// carries no score.
type SpendTrendRow struct {
	FiscalPeriod      int             `json:"fiscal_period"`
	AwardCount        int             `json:"award_count"`
	ObligationDollars decimal.Decimal `json:"obligation_dollars"`
}

// AgencySpendTrend runs the registered agency_spend_trend intent for an agency and a NAICS, scanning the
// obligation total into decimal. The fiscal period is keyed on the award's pop_start_date (usaspending_awards
// carries no award_date), so a null pop_start_date is excluded rather than bucketed under a fabricated year.
func AgencySpendTrend(ctx context.Context, db *tenant.DB, agency, naics string) ([]SpendTrendRow, error) {
	if db == nil {
		return nil, errors.New("ai: no database")
	}
	var out []SpendTrendRow
	err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		if err := GuardReadOnlyQuery(agencySpendTrendSQL, ReadAllowlist); err != nil {
			return err
		}
		rows, err := tx.Query(ctx, agencySpendTrendSQL, agency, naics)
		if err != nil {
			return err
		}
		defer rows.Close()
		for rows.Next() {
			var r SpendTrendRow
			if err := rows.Scan(&r.FiscalPeriod, &r.AwardCount, &r.ObligationDollars); err != nil {
				return err
			}
			out = append(out, r)
		}
		return rows.Err()
	})
	return out, err
}