package ai

// G3 skill and role adjacency graph. This file adds the curated, multi hop skill traversal as FIXED,
// named, parameterized QueryIntents. It is the deterministic counterpart to the live single hop
// SkillAdjacency in scaffolds.go: where SkillAdjacency reads the semantic embedding neighborhood, this
// walks the CURATED skill_relationships graph (the WS2 editorially reviewed substitutes_for and
// broader_than edges) and the ontology_crosswalk taxonomy, returning structure (the reachable skills,
// the accumulated curated strength, and the evidence path) that a caller routes to the frozen engine.
// The graph NEVER computes a match score, a band, or any employment decision number; it returns the
// adjacency a downstream scoring call may consult, exactly as the curated relationship layer already
// feeds the engine's partial credit. The model never authors this SQL: the recursive CTE text is static,
// reviewed, registered in the closed Registry, asserted by ValidateRegistry, and re guarded by
// GuardReadOnlyQuery on every execution.
//
// Recursive determinism (the AEDT reproducibility mandate). Both traversals carry, by construction:
//   - a hard depth cap, bound as a parameter (default 3 hops), enforced in the recursive arm,
//   - a cycle guard, a visited skill id array carried per row plus UNION (not UNION ALL), so a cycle in
//     the curated graph can never loop and the walk always terminates,
//   - a unique id tiebreaker on every ORDER BY, so the same inputs yield byte identical rows.
//
// The accumulated curated strength is carried through the recursion as Postgres numeric and is scanned
// into shopspring/decimal in Go, NEVER float64: a coverage or strength figure shown as evidence is a
// decision adjacent number and the platform's decimal rule applies. Cosine and embedding are NOT used
// here; this is the curated graph, not the semantic matcher (which lives in SkillAdjacency).

import (
	"context"
	"errors"

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

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

// IntentSkillSubstitutionClosure is the registered name of the transitive substitution closure intent.
const IntentSkillSubstitutionClosure = "skill_substitution_closure"

// IntentSkillGapBridge is the registered name of the gap bridge intent.
const IntentSkillGapBridge = "skill_gap_bridge"

// DefaultGraphDepth is the default hard depth cap for the recursive walks when a caller does not bind one.
const DefaultGraphDepth = 3

// maxGraphDepth bounds the depth a caller may request, so an oversized depth cannot turn a bounded walk
// into an unbounded one. The curated graph is shallow; three to five hops is the meaningful range.
const maxGraphDepth = 5

// skillSubstitutionClosureSQL is the FIXED recursive CTE for the substitution and broadening closure. It
// starts from a skill id ($1) and walks skill_relationships over the curated substitutes_for and
// broader_than edges (verified_at IS NOT NULL), to a bound depth ($2).
//
// Determinism, line by line:
//   - the base arm seeds depth 1, the starting accumulated strength 1.0 (numeric), and the visited path
//     array with the start id, so the first hop's strength is the edge strength itself,
//   - the recursive arm multiplies the carried numeric acc_strength by the next edge strength (a
//     multiplicative transitive confidence that stays inside 0 to 1), appends the next skill id to the
//     visited path, and admits a hop only when c.depth < $2 (the hard depth cap) AND the next skill id is
//     NOT already in the visited path (the cycle guard), so a cycle cannot loop,
//   - UNION (not UNION ALL) is the additional duplicate defense the mandate requires,
//   - the final SELECT joins skills for the readable name and orders by depth, then acc_strength
//     descending, then the skill_id tiebreaker, then the full visited path, so the order is total even
//     when two distinct evidence paths reach the same skill at the same depth and strength, and the
//     output is byte stable on a re run.
//
// acc_strength is Postgres numeric end to end and is scanned into decimal.Decimal in Go, never float64.
const skillSubstitutionClosureSQL = `
WITH RECURSIVE closure AS (
    SELECT
        sr.to_skill_id                         AS skill_id,
        1                                      AS depth,
        sr.strength::numeric                   AS acc_strength,
        ARRAY[sr.from_skill_id, sr.to_skill_id] AS path,
        ARRAY[sr.relation]                     AS relations
    FROM skill_relationships sr
    WHERE sr.from_skill_id = $1
      AND sr.verified_at IS NOT NULL
      AND sr.relation IN ('substitutes_for', 'broader_than')
    UNION
    SELECT
        sr.to_skill_id,
        c.depth + 1,
        c.acc_strength * sr.strength,
        c.path || sr.to_skill_id,
        c.relations || sr.relation
    FROM skill_relationships sr
    JOIN closure c ON sr.from_skill_id = c.skill_id
    WHERE sr.verified_at IS NOT NULL
      AND sr.relation IN ('substitutes_for', 'broader_than')
      AND c.depth < $2
      AND NOT (sr.to_skill_id = ANY(c.path))
)
SELECT
    c.skill_id,
    s.name AS skill_name,
    c.depth,
    c.acc_strength,
    c.relations AS evidence_relations,
    c.path AS evidence_path
FROM closure c
JOIN skills s ON s.id = c.skill_id
ORDER BY c.depth, c.acc_strength DESC, c.skill_id, c.path`

// skillGapBridgeSQL is the FIXED bridge intent. Given a required skill id ($1) that is not exactly held,
// it returns the nearest curated skills one or two hops away (the same bounded substitution and
// broadening closure, depth $2) UNIONed with any taxonomy or certification bridge from ontology_crosswalk
// that maps to the required skill's source soc_code, each row labeled with its bridge_kind and carrying
// the evidence path. The curated graph hops carry their accumulated numeric strength; the crosswalk hop
// is a categorical taxonomy link, so it carries the neutral strength 1.0 (an exact crosswalk equivalence)
// and a one element relation chain naming the crosswalk relation. Both arms are read only over
// allowlisted GLOBAL reference tables.
//
// The recursive determinism is identical to the closure: depth cap $2, the visited path cycle guard,
// UNION not UNION ALL, and the skill_id tiebreaker followed by the bridge_kind and the full evidence
// path on the final ORDER BY, so the order is total. acc_strength stays numeric.
const skillGapBridgeSQL = `
WITH RECURSIVE closure AS (
    SELECT
        sr.to_skill_id                         AS skill_id,
        1                                      AS depth,
        sr.strength::numeric                   AS acc_strength,
        ARRAY[sr.from_skill_id, sr.to_skill_id] AS path,
        ARRAY[sr.relation]                     AS relations
    FROM skill_relationships sr
    WHERE sr.from_skill_id = $1
      AND sr.verified_at IS NOT NULL
      AND sr.relation IN ('substitutes_for', 'broader_than')
    UNION
    SELECT
        sr.to_skill_id,
        c.depth + 1,
        c.acc_strength * sr.strength,
        c.path || sr.to_skill_id,
        c.relations || sr.relation
    FROM skill_relationships sr
    JOIN closure c ON sr.from_skill_id = c.skill_id
    WHERE sr.verified_at IS NOT NULL
      AND sr.relation IN ('substitutes_for', 'broader_than')
      AND c.depth < $2
      AND NOT (sr.to_skill_id = ANY(c.path))
)
SELECT
    c.skill_id,
    s.name        AS skill_name,
    c.depth,
    c.acc_strength,
    'curated_relationship' AS bridge_kind,
    c.relations   AS evidence_relations,
    c.path        AS evidence_path
FROM closure c
JOIN skills s ON s.id = c.skill_id
UNION
SELECT
    b.id          AS skill_id,
    b.name        AS skill_name,
    1             AS depth,
    1.0           AS acc_strength,
    'ontology_crosswalk' AS bridge_kind,
    ARRAY[ox.relation] AS evidence_relations,
    ARRAY[$1::uuid, b.id] AS evidence_path
FROM skills req
JOIN ontology_crosswalk ox ON ox.from_code = req.soc_code AND req.soc_code <> ''
JOIN skills b ON b.soc_code = ox.to_code AND b.soc_code <> '' AND b.id <> req.id
WHERE req.id = $1
ORDER BY depth, acc_strength DESC, skill_id, bridge_kind, evidence_path`

// The two intents above are registered in the closed Registry in tag.go (the FIXED entries
// skill_substitution_closure and skill_gap_bridge), so ValidateRegistry asserts them at boot (read only,
// eeo free, allowlisted) and ExecuteIntent runs them through the same RunInTenantTx, guard, and audit
// posture as every other TAG intent. The recursive SQL stays here, next to the typed read helpers, and
// the Registry entries reference these constants by name.

// ClampGraphDepth normalizes a requested depth to the bounded, deterministic range: a non positive or
// missing depth becomes DefaultGraphDepth, and a depth above maxGraphDepth is capped, so the walk is
// always bounded no matter what a caller binds.
func ClampGraphDepth(depth int) int {
	if depth <= 0 {
		return DefaultGraphDepth
	}
	if depth > maxGraphDepth {
		return maxGraphDepth
	}
	return depth
}

// SkillGraphNode is one reachable skill from a curated traversal: the skill, the hop depth at which it was
// reached, the accumulated curated strength along the path (decimal, never float), the bridge kind that
// reached it, and the evidence path (the ordered relation chain and the ordered skill id chain). It is
// pure structure: there is no score, no band, and no employment decision number on it.
type SkillGraphNode struct {
	SkillID    string          `json:"skill_id"`
	SkillName  string          `json:"skill_name"`
	Depth      int             `json:"depth"`
	Strength   decimal.Decimal `json:"strength"`
	BridgeKind string          `json:"bridge_kind,omitempty"`
	Relations  []string        `json:"relations"`
	Path       []string        `json:"path"`
}

// SkillSubstitutionClosure runs the registered skill_substitution_closure intent and scans the result into
// typed nodes, carrying the accumulated curated strength as decimal.Decimal (the numeric column is scanned
// directly into decimal, never through float64). It is the typed read for callers that want the curated
// neighborhood as structure; the generic ExecuteIntent path remains available for the narration layer. It
// opens the same RLS scoped, app role transaction as every other read; the tables are GLOBAL reference, so
// the walk is correct without any tenant data, and the uniform tenant posture gives one connection, audit,
// and timeout discipline.
func SkillSubstitutionClosure(ctx context.Context, db *tenant.DB, startSkillID string, depth int) ([]SkillGraphNode, error) {
	if db == nil {
		return nil, errors.New("ai: no database")
	}
	var out []SkillGraphNode
	err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		var e error
		out, e = SkillSubstitutionClosureOnTx(ctx, tx, startSkillID, depth)
		return e
	})
	return out, err
}

// SkillSubstitutionClosureOnTx is SkillSubstitutionClosure on a caller supplied transaction, so a module
// already inside a tenant transaction reuses the curated walk without nesting a transaction. It re guards
// the static SQL and scans the numeric strength into decimal.
func SkillSubstitutionClosureOnTx(ctx context.Context, tx pgx.Tx, startSkillID string, depth int) ([]SkillGraphNode, error) {
	if err := GuardReadOnlyQuery(skillSubstitutionClosureSQL, ReadAllowlist); err != nil {
		return nil, err
	}
	rows, err := tx.Query(ctx, skillSubstitutionClosureSQL, startSkillID, ClampGraphDepth(depth))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []SkillGraphNode
	for rows.Next() {
		var n SkillGraphNode
		if err := rows.Scan(&n.SkillID, &n.SkillName, &n.Depth, &n.Strength, &n.Relations, &n.Path); err != nil {
			return nil, err
		}
		out = append(out, n)
	}
	return out, rows.Err()
}

// SkillGapBridge runs the registered skill_gap_bridge intent and scans the result into typed nodes. Like
// the closure, the accumulated curated strength is carried as decimal.Decimal, never float64.
func SkillGapBridge(ctx context.Context, db *tenant.DB, requiredSkillID string, depth int) ([]SkillGraphNode, error) {
	if db == nil {
		return nil, errors.New("ai: no database")
	}
	var out []SkillGraphNode
	err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		var e error
		out, e = SkillGapBridgeOnTx(ctx, tx, requiredSkillID, depth)
		return e
	})
	return out, err
}

// SkillGapBridgeOnTx is SkillGapBridge on a caller supplied transaction. It re guards the static SQL and
// scans the numeric strength into decimal.
func SkillGapBridgeOnTx(ctx context.Context, tx pgx.Tx, requiredSkillID string, depth int) ([]SkillGraphNode, error) {
	if err := GuardReadOnlyQuery(skillGapBridgeSQL, ReadAllowlist); err != nil {
		return nil, err
	}
	rows, err := tx.Query(ctx, skillGapBridgeSQL, requiredSkillID, ClampGraphDepth(depth))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []SkillGraphNode
	for rows.Next() {
		var n SkillGraphNode
		if err := rows.Scan(&n.SkillID, &n.SkillName, &n.Depth, &n.Strength, &n.BridgeKind, &n.Relations, &n.Path); err != nil {
			return nil, err
		}
		out = append(out, n)
	}
	return out, rows.Err()
}