package ai

// M5 price to win. This file replaces the G6 price to win honest floor with a REAL BLS OEWS wage band for
// the requirement's occupation and place of performance. The band is ADVISORY MARKET context, the
// competitive labor rate a capture analyst reads, explicitly NOT a match score, NOT a candidate score, and
// NOT an employment decision number; the frozen engine in internal/modules/matching/engine stays the only
// scorer and is untouched. Where wage data does not exist for a requirement's role or state, the G6
// composition keeps the honest floor and fabricates no rate.
//
// bls_wages is a GLOBAL public reference table (no organization_id, outside Row Level Security), so NO small
// cell suppression is needed and the reads carry no person data. Every read still runs through the same
// posture as the other market reads: GuardReadOnlyQuery against the allowlist, RunInTenantTx, and a
// parameterized single SELECT the model never authors.
//
// Money is shopspring/decimal end to end, never float64: the wage value column is numeric(14,2) and is
// scanned straight into decimal. The role to SOC crosswalk is the existing bls.GalapagosOccupations mapping,
// reused, never reinvented; the area code is the existing bls.GalapagosAreas set. Both resolvers are
// deterministic and conservative: an ambiguous or unrecognized input returns false so the honest floor
// holds rather than binding a wrong occupation or state.

import (
	"context"
	"errors"
	"strings"

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

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

// IntentLaborRateBand is the registered name of the advisory OEWS wage band read. It is a PUBLIC reference
// intent (bls_wages is global public data), NOT a bench intent, NOT suppressed, and NOT a candidate score.
const IntentLaborRateBand = "labor_rate_band"

// laborRateBandSQL is the FIXED wage band read. Given a SOC occupation code ($1) and an OEWS area code ($2,
// HI or NM), it pivots the two metric rows (annual_mean and annual_median) of bls_wages for that occupation
// and area into one row carrying the mean and the median in dollars, plus the occupation title, the
// Galapagos role label, and the area name. It aggregates over the (occupation_code, area) key the index
// covers, using max over the labels (they are constant per occupation and area) and a filtered max over the
// value per metric, so the two rows collapse to a single band row. The mean and median are numeric, scanned
// straight into shopspring/decimal, never float. The HAVING keeps only a band that carries at least one of
// the two metrics, so an occupation and area with no wage row returns no row and the honest floor holds.
// Deterministic by construction: the aggregate over a single (occupation_code, area) key returns exactly one
// grouped row, and the ORDER BY on the grouped keys is a total order tiebreaker for byte stable output.
const laborRateBandSQL = `SELECT
    w.occupation_code,
    max(w.occupation_title)                                      AS occupation_title,
    max(w.role)                                                  AS role,
    w.area,
    max(w.area_name)                                             AS area_name,
    max(w.value) FILTER (WHERE w.metric = 'annual_mean')         AS annual_mean,
    max(w.value) FILTER (WHERE w.metric = 'annual_median')       AS annual_median
  FROM bls_wages w
  WHERE w.occupation_code = $1
    AND w.area = $2
    AND w.metric IN ('annual_mean', 'annual_median')
  GROUP BY w.occupation_code, w.area
  HAVING count(*) FILTER (WHERE w.metric IN ('annual_mean', 'annual_median')) > 0
  ORDER BY w.occupation_code, w.area`

// WageBand is one resolved OEWS wage band for an occupation and area: the SOC, the OEWS occupation title, the
// Galapagos role label, the OEWS area code, and the annual mean and annual median in dollars. The two dollar
// figures are shopspring/decimal, never float. It is advisory market structure: it carries no match score, no
// candidate id, and no employment decision number.
type WageBand struct {
	SOC             string          `json:"soc"`
	OccupationTitle string          `json:"occupation_title"`
	Role            string          `json:"role"`
	Area            string          `json:"area"`
	AreaName        string          `json:"area_name"`
	AnnualMean      decimal.Decimal `json:"annual_mean"`
	AnnualMedian    decimal.Decimal `json:"annual_median"`
}

// LaborRateBand reads the OEWS wage band for an occupation (SOC) and an OEWS area code (HI or NM) from the
// global public bls_wages reference, pivoting the two metric rows (annual_mean and annual_median) into the
// mean and median decimals. It returns ok false when no wage row exists for that occupation and area, so the
// G6 composition keeps the honest floor rather than fabricating a rate. It runs the guarded read inside
// RunInTenantTx like every other market read even though the table is global public reference data with no
// RLS, so the read posture is uniform. Money is scanned straight into shopspring/decimal, never float.
func LaborRateBand(ctx context.Context, db *tenant.DB, soc, area string) (WageBand, bool, error) {
	if db == nil {
		return WageBand{}, false, errors.New("ai: no database")
	}
	soc = strings.TrimSpace(soc)
	area = strings.TrimSpace(area)
	if soc == "" || area == "" {
		return WageBand{}, false, nil
	}
	var (
		band  WageBand
		found bool
	)
	err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		if err := GuardReadOnlyQuery(laborRateBandSQL, ReadAllowlist); err != nil {
			return err
		}
		row := tx.QueryRow(ctx, laborRateBandSQL, soc, area)
		var (
			mean   *decimal.Decimal
			median *decimal.Decimal
		)
		if err := row.Scan(&band.SOC, &band.OccupationTitle, &band.Role, &band.Area, &band.AreaName, &mean, &median); err != nil {
			if errors.Is(err, pgx.ErrNoRows) {
				return nil // no wage row for this occupation and area: found stays false, the honest floor holds
			}
			return err
		}
		if mean != nil {
			band.AnnualMean = *mean
		}
		if median != nil {
			band.AnnualMedian = *median
		}
		found = true
		return nil
	})
	if err != nil {
		return WageBand{}, false, err
	}
	return band, found, nil
}

// nationalOccupationsSQL reads the distinct national OEWS occupations from bls_wages: the (occupation_code,
// occupation_title) pairs of the national rows (area = 'US'). It is the broadened crosswalk source for
// resolveSOCBroadened, so a requirement title can bind to any ingested OEWS occupation, not only the five
// curated Galapagos roles. It is a single read over the allowlisted bls_wages table with no mutating
// keyword, so it passes GuardReadOnlyQuery. It is deterministic: DISTINCT plus an ORDER BY on both grouped
// columns is a total order, so the returned set is byte stable.
const nationalOccupationsSQL = `SELECT DISTINCT w.occupation_code, w.occupation_title
  FROM bls_wages w
  WHERE w.area = 'US'
    AND w.occupation_code <> ''
    AND w.occupation_title <> ''
  ORDER BY w.occupation_code, w.occupation_title`

// nationalOccupation is one ingested national OEWS occupation: the SOC and the OEWS title. It is the
// broadened crosswalk entry resolveSOCBroadened matches a requirement title against.
type nationalOccupation struct {
	SOC   string
	Title string
}

// nationalOccupations reads the distinct national OEWS occupations (area = 'US') from the global public
// bls_wages reference. It runs the guarded read inside RunInTenantTx like every other market read even
// though the table is global public reference data with no RLS, so the read posture is uniform. It returns
// an empty slice (not an error) when no national rows exist yet, so an unpopulated catalog degrades to the
// curated crosswalk and the honest floor rather than failing the capture answer.
func nationalOccupations(ctx context.Context, db *tenant.DB) ([]nationalOccupation, error) {
	if db == nil {
		return nil, errors.New("ai: no database")
	}
	var out []nationalOccupation
	err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		if err := GuardReadOnlyQuery(nationalOccupationsSQL, ReadAllowlist); err != nil {
			return err
		}
		rows, err := tx.Query(ctx, nationalOccupationsSQL)
		if err != nil {
			return err
		}
		defer rows.Close()
		for rows.Next() {
			var o nationalOccupation
			if err := rows.Scan(&o.SOC, &o.Title); err != nil {
				return err
			}
			out = append(out, o)
		}
		return rows.Err()
	})
	if err != nil {
		return nil, err
	}
	return out, nil
}

// resolveSOCBroadened matches a requirement's free text title against the full set of ingested national OEWS
// occupation titles (in addition to the curated Galapagos crosswalk resolveSOC covers). It is conservative
// and deterministic: it collects every occupation whose OEWS title is contained in the requirement title OR
// whose OEWS title contains the requirement title (case insensitive), keeps the LONGEST matched occupation
// title (the most specific overlap), and returns ok true only when that longest match is unambiguous. A tie
// across DIFFERENT SOCs at the same longest length returns false so the G6 composition keeps the honest
// floor rather than binding a wrong occupation; it never fabricates a SOC. An empty title or an empty
// occupation set returns false. The curated Galapagos SOCs (from resolveSOC) take precedence and are tried
// first by the caller; this broadened pass is the fallback for titles the curated crosswalk does not cover.
func resolveSOCBroadened(title string, occs []nationalOccupation) (nationalOccupation, bool) {
	t := strings.ToLower(strings.TrimSpace(title))
	if t == "" || len(occs) == 0 {
		return nationalOccupation{}, false
	}
	var best nationalOccupation
	bestLen := 0
	ambiguous := false
	for _, occ := range occs {
		ot := strings.ToLower(strings.TrimSpace(occ.Title))
		if ot == "" {
			continue
		}
		// A conservative overlap: the requirement title contains the OEWS title, or the OEWS title contains the
		// requirement title. The matched span length is the length of the OEWS title (the occupation label we
		// bind to), so the most specific occupation title wins.
		if !strings.Contains(t, ot) && !strings.Contains(ot, t) {
			continue
		}
		switch {
		case len(ot) > bestLen:
			best = occ
			bestLen = len(ot)
			ambiguous = false
		case len(ot) == bestLen && occ.SOC != best.SOC:
			ambiguous = true // a tie across DIFFERENT SOCs at the same length is ambiguous
		}
	}
	if bestLen == 0 || ambiguous {
		return nationalOccupation{}, false
	}
	return best, true
}

// resolveArea parses a requirement's free text work_location to a known OEWS area code (HI or NM). It is a
// small, fixed, conservative matcher over the two states where the wage baseline exists: a location naming
// Hawaii, HI, Honolulu, and the like maps to HI; a location naming New Mexico, NM, Albuquerque, and the like
// maps to NM. It returns ok false when the text maps to no known GalapagosArea, so the G6 composition keeps
// the honest floor rather than guessing a state. The matching is case insensitive substring over a fixed
// phrase table; a two letter state token is matched only as a delimited token (a comma or space bounded
// abbreviation, for example "Honolulu, HI") so a stray "hi" or "nm" inside a longer word does not false
// match.
func resolveArea(workLocation string) (string, bool) {
	loc := strings.ToLower(strings.TrimSpace(workLocation))
	if loc == "" {
		return "", false
	}
	// Full name and city phrases, matched as case insensitive substrings. These are unambiguous.
	hawaiiPhrases := []string{"hawaii", "honolulu", "pearl harbor", "hickam", "oahu", "kaneohe", "schofield"}
	newMexicoPhrases := []string{"new mexico", "albuquerque", "kirtland", "las cruces", "santa fe", "holloman", "cannon"}
	for _, p := range hawaiiPhrases {
		if strings.Contains(loc, p) {
			return "HI", true
		}
	}
	for _, p := range newMexicoPhrases {
		if strings.Contains(loc, p) {
			return "NM", true
		}
	}
	// Two letter state abbreviation, matched only as a delimited token so a stray substring never false
	// matches. The tokens are the location text split on commas and whitespace.
	for _, tok := range strings.FieldsFunc(loc, func(r rune) bool {
		return r == ',' || r == ' ' || r == '\t' || r == '\n' || r == '/' || r == ';'
	}) {
		switch tok {
		case "hi":
			return "HI", true
		case "nm":
			return "NM", true
		}
	}
	return "", false
}

// resolveSOC matches a requirement's free text title against the existing bls.GalapagosOccupations crosswalk
// by the Galapagos role label or the OEWS occupation title (case insensitive contains), returning the matched
// Occupation (with its SOC) on a clear, unambiguous match and ok false otherwise. It is conservative: it
// reuses the ONE curated role to SOC crosswalk (never invents a new one), and when the title matches zero
// occupations OR matches more than one it returns false so the G6 composition keeps the honest floor rather
// than binding the wrong occupation. The Role label is checked first (it is the analyst facing label a
// requirement title most often carries, for example "Program Manager"), then the OEWS occupation title.
func resolveSOC(title string) (bls.Occupation, bool) {
	t := strings.ToLower(strings.TrimSpace(title))
	if t == "" {
		return bls.Occupation{}, false
	}
	// First pass: a role label contained in the title is the primary, analyst facing match.
	var roleMatches []bls.Occupation
	for _, occ := range bls.GalapagosOccupations {
		if role := strings.ToLower(strings.TrimSpace(occ.Role)); role != "" && strings.Contains(t, role) {
			roleMatches = append(roleMatches, occ)
		}
	}
	if occ, ok := singleMatch(roleMatches); ok {
		return occ, true
	}
	if len(roleMatches) > 1 {
		return bls.Occupation{}, false // an ambiguous role match keeps the honest floor
	}
	// Second pass: the OEWS occupation title contained in the requirement title.
	var titleMatches []bls.Occupation
	for _, occ := range bls.GalapagosOccupations {
		if ot := strings.ToLower(strings.TrimSpace(occ.Title)); ot != "" && strings.Contains(t, ot) {
			titleMatches = append(titleMatches, occ)
		}
	}
	if occ, ok := singleMatch(titleMatches); ok {
		return occ, true
	}
	return bls.Occupation{}, false
}

// singleMatch returns the one occupation when exactly one matched, so a zero or ambiguous match falls to the
// honest floor. It keys on the SOC so two occupations that happen to share a SOC (they do not today) would
// count as one, but distinct SOCs are treated as ambiguous.
func singleMatch(matches []bls.Occupation) (bls.Occupation, bool) {
	if len(matches) == 0 {
		return bls.Occupation{}, false
	}
	first := matches[0]
	for _, m := range matches[1:] {
		if m.SOC != first.SOC {
			return bls.Occupation{}, false
		}
	}
	return first, true
}

// resolveAreaName returns the OEWS area name (Hawaii or New Mexico) for a known area code, so the surfaced
// price to win line can name the state in prose. An unknown code returns the code itself so the line still
// reads, though resolveArea only ever yields a known code.
func resolveAreaName(area string) string {
	for _, a := range bls.GalapagosAreas {
		if a.Abbr == area {
			return a.Name
		}
	}
	return area
}

// resolvePriceToWinSOC resolves the requirement's title to a SOC for the price to win leg. It tries the ONE
// curated Galapagos crosswalk first (resolveSOC, the analyst facing roles, pure and deterministic); when
// that does not bind it falls back to the broadened national OEWS occupation set read from bls_wages, so a
// requirement title outside the five curated roles can still bind to any ingested occupation. It stays
// conservative: an unresolvable or ambiguous title returns false so the honest floor holds. It never
// fabricates a SOC. The national read runs only when the curated pass misses, so the common curated case
// costs no query.
func resolvePriceToWinSOC(ctx context.Context, db *tenant.DB, title string) (string, bool) {
	if occ, ok := resolveSOC(title); ok {
		return occ.SOC, true
	}
	occs, err := nationalOccupations(ctx, db)
	if err != nil || len(occs) == 0 {
		return "", false // an unpopulated catalog or a read error degrades to the honest floor
	}
	if occ, ok := resolveSOCBroadened(title, occs); ok {
		return occ.SOC, true
	}
	return "", false
}

// resolveWageBandWithNationalFallback reads the OEWS wage band for a SOC, preferring the specific STATE band
// (HI or NM) when the requirement's work_location names that state AND a state wage row exists for the SOC,
// and FALLING BACK to the US national band otherwise. So a requirement in a state with state level data gets
// the state band (state preferred over national), and a requirement in any other state, or one whose work
// location does not resolve to a known state, gets the national band labeled United States. It returns ok
// false only when NEITHER a state row nor a national row exists for the SOC, so the honest floor holds. The
// band it returns already names the area used (the state name or United States) for the surfaced line.
func resolveWageBandWithNationalFallback(ctx context.Context, db *tenant.DB, soc, workLocation string) (WageBand, bool, error) {
	// Prefer the specific state band when the requirement names a known OEWS state and a state row exists.
	if area, areaOK := resolveArea(workLocation); areaOK {
		band, ok, err := LaborRateBand(ctx, db, soc, area)
		if err != nil {
			return WageBand{}, false, err
		}
		if ok {
			return band, true, nil
		}
	}
	// Fall back to the US national band (area = 'US'). This is the M5 Phase 1 broadening: any resolvable SOC
	// gets at least the national band even outside HI and NM, labeled United States honestly.
	band, ok, err := LaborRateBand(ctx, db, soc, bls.NationalAreaAbbr)
	if err != nil {
		return WageBand{}, false, err
	}
	return band, ok, nil
}