Janady13/missionmatch / internal/modules/commandcenter/service_assignment.go

1package commandcenter 2 3import ( 4 "context" 5 "fmt" 6 "sort" 7 "time" 8 9 "github.com/jackc/pgx/v5" 10 "github.com/shopspring/decimal" 11) 12 13// ReqCoverage is one requirement's staffing coverage from the greedy assignment (the slate coverage 14// logic). Unmet positions are Quantity minus Assigned. 15type ReqCoverage struct { 16 Quantity int 17 Assigned int 18 Qualified int 19 LaborCategory string 20 KeyPersonnel bool 21} 22 23// Coverage builds the per-requirement staffing coverage for the caller's org, reusing the exact same 24// greedy assignment the Command Center uses (each available candidate consumed at most once). It also 25// returns the eligibility edges (requirement id to the set of candidate ids that pass the hard gates: 26// clearance, years, and all mandatory skills), so Win Impact gates on the SAME eligibility the 27// assignment uses, not a softer band heuristic. The caller supplies an open tenant transaction 28// (RLS-scoped). This is the single source of coverage truth shared by the dashboard and the candidate 29// intelligence Win Impact. 30func Coverage(ctx context.Context, tx pgx.Tx, asOf time.Time) (map[string]ReqCoverage, map[string]map[string]bool, error) { 31 if err := buildTemps(ctx, tx, asOf.Format("2006-01-02")); err != nil { 32 return nil, nil, err 33 } 34 assigned, qualified, reqs, edges, err := fetchAssignment(ctx, tx) 35 if err != nil { 36 return nil, nil, err 37 } 38 out := make(map[string]ReqCoverage, len(reqs)) 39 for _, r := range reqs { 40 out[r.id] = ReqCoverage{ 41 Quantity: r.quantity, Assigned: assigned[r.id], Qualified: qualified[r.id], 42 LaborCategory: r.lcat, KeyPersonnel: r.keyPersonnel, 43 } 44 } 45 eligible := make(map[string]map[string]bool, len(edges)) 46 for rid, cids := range edges { 47 m := make(map[string]bool, len(cids)) 48 for _, cid := range cids { 49 m[cid] = true 50 } 51 eligible[rid] = m 52 } 53 return out, eligible, nil 54} 55 56// reqMeta is one active requirement's metadata for the assignment. 57type reqMeta struct { 58 id, oppID, lcat, level string 59 quantity int 60 keyPersonnel bool 61} 62 63// fetchAssignment computes a TRUE staffing assignment: each available candidate is consumed AT MOST 64// ONCE across all active requirements (greedy, scarcest-requirement-first). This replaces the prior 65// per-requirement qualification count that double-counted a candidate across every requirement they 66// fit [review COV-1/COV-2]. Returns the per-requirement assigned headcount, the per-requirement 67// qualified-pool size (distinct eligible candidates), and the requirement metadata. 68func fetchAssignment(ctx context.Context, tx pgx.Tx) (assigned, qualified map[string]int, reqs []reqMeta, edges map[string][]string, err error) { 69 mrows, err := tx.Query(ctx, ` 70 SELECT id::text, COALESCE(opportunity_id::text,''), 71 COALESCE(NULLIF(trim(labor_category),''),'Uncategorized'), quantity, key_personnel, required_level 72 FROM cc_active_req ORDER BY id`) 73 if err != nil { 74 return nil, nil, nil, nil, fmt.Errorf("req meta: %w", err) 75 } 76 for mrows.Next() { 77 var r reqMeta 78 var q int32 79 if err := mrows.Scan(&r.id, &r.oppID, &r.lcat, &q, &r.keyPersonnel, &r.level); err != nil { 80 mrows.Close() 81 return nil, nil, nil, nil, err 82 } 83 r.quantity = int(q) 84 reqs = append(reqs, r) 85 } 86 mrows.Close() 87 if err := mrows.Err(); err != nil { 88 return nil, nil, nil, nil, err 89 } 90 91 // eligibility edges: which available candidate qualifies for which active req (clearance + years + 92 // ALL mandatory skills). Ordered for deterministic greedy selection. 93 erows, err := tx.Query(ctx, ` 94 SELECT ar.id::text, c.id::text 95 FROM cc_active_req ar 96 JOIN cc_cand c ON c.available 97 AND (ar.rlvl < 2 OR (c.effective AND c.lvl >= ar.rlvl AND c.poly >= ar.rpoly)) 98 AND c.years_experience >= ar.required_years 99 AND NOT EXISTS ( 100 SELECT 1 FROM unnest(COALESCE((SELECT array_agg(skill_id) FROM required_skills rs WHERE rs.requirement_id=ar.id AND rs.mandatory),'{}'::uuid[])) AS rsk 101 WHERE NOT EXISTS (SELECT 1 FROM candidate_skills cs WHERE cs.candidate_id=c.id AND cs.skill_id=rsk) 102 ) 103 ORDER BY ar.id, c.id`) 104 if err != nil { 105 return nil, nil, nil, nil, fmt.Errorf("eligibility edges: %w", err) 106 } 107 edges = map[string][]string{} 108 for erows.Next() { 109 var rid, cid string 110 if err := erows.Scan(&rid, &cid); err != nil { 111 erows.Close() 112 return nil, nil, nil, nil, err 113 } 114 edges[rid] = append(edges[rid], cid) 115 } 116 erows.Close() 117 if err := erows.Err(); err != nil { 118 return nil, nil, nil, nil, err 119 } 120 121 qualified = make(map[string]int, len(reqs)) 122 for _, r := range reqs { 123 qualified[r.id] = len(edges[r.id]) 124 } 125 126 // greedy: fill the scarcest (fewest-eligible) requirements first; each candidate used at most once. 127 order := make([]reqMeta, len(reqs)) 128 copy(order, reqs) 129 sort.Slice(order, func(i, j int) bool { 130 qi, qj := qualified[order[i].id], qualified[order[j].id] 131 if qi != qj { 132 return qi < qj 133 } 134 if order[i].quantity != order[j].quantity { 135 return order[i].quantity > order[j].quantity 136 } 137 return order[i].id < order[j].id 138 }) 139 assigned = make(map[string]int, len(reqs)) 140 used := make(map[string]bool) 141 for _, r := range order { 142 for _, cid := range edges[r.id] { 143 if assigned[r.id] >= r.quantity { 144 break 145 } 146 if !used[cid] { 147 used[cid] = true 148 assigned[r.id]++ 149 } 150 } 151 } 152 return assigned, qualified, reqs, edges, nil 153} 154 155func oppSortVal(v *string) decimal.Decimal { 156 if v == nil { 157 return decimal.NewFromInt(-1) 158 } 159 d, err := decimal.NewFromString(*v) 160 if err != nil { 161 return decimal.NewFromInt(-1) 162 } 163 return d 164} 165 166// opportunitiesFromAssignment rolls the per-requirement assignment up to each open opportunity. 167func opportunitiesFromAssignment(reqs []reqMeta, assigned map[string]int, opp map[string]oppInfo) ([]Opportunity, int) { 168 byOpp := map[string][]reqMeta{} 169 for _, r := range reqs { 170 if r.oppID != "" { 171 byOpp[r.oppID] = append(byOpp[r.oppID], r) 172 } 173 } 174 out := make([]Opportunity, 0, len(byOpp)) 175 high := 0 176 for oppID, rs := range byOpp { 177 info := opp[oppID] 178 total := len(rs) 179 covered, hcNeed, hcCov, kpOpen := 0, 0, 0, 0 180 kpOK := true 181 for _, r := range rs { 182 a := assigned[r.id] 183 hcNeed += r.quantity 184 hcCov += a 185 if a >= r.quantity { 186 covered++ 187 } 188 if r.keyPersonnel && a < r.quantity { 189 kpOpen++ 190 kpOK = false 191 } 192 } 193 ratio := pct(int64(covered), int64(total)) 194 o := Opportunity{ 195 OpportunityID: oppID, Name: info.name, CustomerAgency: info.customer, 196 SolicitationNumber: info.solicitation, DueDate: info.dueDate, 197 ReadinessScore: ratio.StringFixed(2), TotalRequirements: total, CoveredRequirements: covered, 198 HeadcountNeeded: hcNeed, HeadcountCovered: hcCov, KeyPersonnelOpen: kpOpen, 199 } 200 if info.value != nil { 201 vs := info.value.StringFixed(2) 202 o.Value = &vs 203 } 204 switch { 205 case !kpOK || ratio.LessThan(decimal.NewFromInt(50)): 206 o.StaffingRisk, o.StaffingRiskCode = "High", "high" 207 high++ 208 case ratio.LessThan(decimal.NewFromInt(85)): 209 o.StaffingRisk, o.StaffingRiskCode = "Medium", "medium" 210 default: 211 o.StaffingRisk, o.StaffingRiskCode = "Low", "low" 212 } 213 out = append(out, o) 214 } 215 sort.Slice(out, func(i, j int) bool { 216 vi, vj := oppSortVal(out[i].Value), oppSortVal(out[j].Value) 217 if !vi.Equal(vj) { 218 return vi.GreaterThan(vj) 219 } 220 return out[i].OpportunityID < out[j].OpportunityID 221 }) 222 return out, high 223} 224 225// gapsFromAssignment derives true unmet positions (quantity minus ASSIGNED) grouped by labor category 226// + required level. Banner staffing_gaps == sum of these (incl Uncategorized) so they reconcile [COV-3]. 227func gapsFromAssignment(reqs []reqMeta, assigned map[string]int, opp map[string]oppInfo) ([]Gap, *string, int) { 228 type key struct{ lcat, level string } 229 type acc struct { 230 unmet int 231 blocksKP bool 232 oppIDs map[string]bool 233 } 234 groups := map[key]*acc{} 235 keyOrder := []key{} 236 totalGaps := 0 237 var suggestReq *string 238 maxUnmet := -1 239 // demandedTotal is the total headcount demanded across every active requirement. Filling one gap 240 // raises filled headcount by exactly that gap's unmet, so pct(unmet, demandedTotal) is the points of 241 // overall labor category coverage that closing the gap adds, the modeled Mission Readiness Impact. 242 var demandedTotal int64 243 for _, r := range reqs { 244 demandedTotal += int64(r.quantity) 245 unmet := r.quantity - assigned[r.id] 246 if unmet < 0 { 247 unmet = 0 248 } 249 totalGaps += unmet 250 if unmet == 0 { 251 continue 252 } 253 if unmet > maxUnmet { // reqs are in id order => stable lowest-id pick on ties 254 maxUnmet = unmet 255 id := r.id 256 suggestReq = &id 257 } 258 k := key{r.lcat, r.level} 259 g := groups[k] 260 if g == nil { 261 g = &acc{oppIDs: map[string]bool{}} 262 groups[k] = g 263 keyOrder = append(keyOrder, k) 264 } 265 g.unmet += unmet 266 if r.keyPersonnel { 267 g.blocksKP = true 268 } 269 if r.oppID != "" { 270 g.oppIDs[r.oppID] = true 271 } 272 } 273 out := make([]Gap, 0, len(groups)) 274 for _, k := range keyOrder { 275 g := groups[k] 276 ids := make([]string, 0, len(g.oppIDs)) 277 for id := range g.oppIDs { 278 ids = append(ids, id) 279 } 280 sort.Strings(ids) 281 names := []string{} 282 var risk decimal.Decimal 283 priced, unpriced := 0, 0 284 allReal := true // true only when every PRICED opportunity is a cached federal value 285 for _, id := range ids { 286 info := opp[id] 287 if info.name != "" { 288 names = append(names, info.name) 289 } 290 if info.value != nil { 291 risk = risk.Add(*info.value) 292 priced++ 293 if !isRealValueSource(info.valueSource) { 294 allReal = false 295 } 296 } else { 297 unpriced++ 298 } 299 } 300 sort.Strings(names) 301 total := priced + unpriced 302 var riskStr *string 303 if priced > 0 { // only emit a dollar when at least one opportunity is priced 304 vs := risk.StringFixed(2) 305 riskStr = &vs 306 } 307 // Representative unless every counted dollar is a cached federal figure: a single synthetic dollar 308 // in the sum taints the headline, so the conservative, honest answer is representative. 309 representative := priced == 0 || !allReal 310 impact := pct(int64(g.unmet), demandedTotal) 311 impactStr := impact.StringFixed(2) 312 impactLabel := fmt.Sprintf( 313 "Modeled coverage delta, filling this gap adds %s points to overall labor category coverage, not a guarantee of award.", 314 impactStr) 315 out = append(out, Gap{ 316 LaborCategory: k.lcat, RequiredLevel: k.level, RequiredLevelLabel: levelLabel(k.level), 317 Unmet: g.unmet, BlocksKeyPersonnel: g.blocksKP, ImpactedOpportunities: names, ValueAtRisk: riskStr, 318 PricedOpportunities: priced, UnpricedOpportunities: unpriced, TotalOpportunities: total, 319 ValueRepresentative: representative, ValueBasis: valueBasisLine(priced, unpriced, total, representative), 320 ImpactModeledPct: impactStr, ImpactModeledLabel: impactLabel, 321 Headline: fmt.Sprintf("NEED %d %s (%s)", g.unmet, pluralize(k.lcat, int64(g.unmet)), levelLabel(k.level)), 322 Recommendation: fmt.Sprintf("Recruit %d %s %s%s.", g.unmet, levelLabel(k.level), pluralize(k.lcat, int64(g.unmet)), kpClause(g.blocksKP)), 323 }) 324 } 325 sort.SliceStable(out, func(i, j int) bool { 326 if out[i].Unmet != out[j].Unmet { 327 return out[i].Unmet > out[j].Unmet 328 } 329 if out[i].LaborCategory != out[j].LaborCategory { 330 return out[i].LaborCategory < out[j].LaborCategory 331 } 332 return out[i].RequiredLevel < out[j].RequiredLevel 333 }) 334 return out, suggestReq, totalGaps 335} 336 337// valueBasisLine states how a gap's dollar was derived so the figure is auditable, never a bare sum. It 338// names the counted (priced) and excluded (unpriced) opportunity counts and whether the figure is 339// representative demo data or modeled from cached federal values. It never invents a number. 340func valueBasisLine(priced, unpriced, total int, representative bool) string { 341 if total == 0 { 342 return "No priced opportunities tied to this gap." 343 } 344 if priced == 0 { 345 return fmt.Sprintf("No published value, %d of %d opportunities excluded as unpriced.", unpriced, total) 346 } 347 source := "modeled exposure from cached federal values" 348 if representative { 349 source = "representative demo exposure" 350 } 351 word := "opportunities" 352 if priced == 1 { 353 word = "opportunity" 354 } 355 if unpriced == 0 { 356 return fmt.Sprintf("Summed across %d priced %s, %s.", priced, word, source) 357 } 358 return fmt.Sprintf("Summed across %d of %d priced %s, %d excluded as unpriced, %s.", priced, total, word, unpriced, source) 359} 360 361// lcatCoverageFromAssignment is the true fill rate per labor category (assigned / demanded). 362func lcatCoverageFromAssignment(reqs []reqMeta, assigned map[string]int) []LcatCoverage { 363 type acc struct{ filled, demanded int } 364 m := map[string]*acc{} 365 order := []string{} 366 for _, r := range reqs { 367 a := m[r.lcat] 368 if a == nil { 369 a = &acc{} 370 m[r.lcat] = a 371 order = append(order, r.lcat) 372 } 373 a.filled += assigned[r.id] 374 a.demanded += r.quantity 375 } 376 out := make([]LcatCoverage, 0, len(m)) 377 for _, lcat := range order { 378 a := m[lcat] 379 cov := pct(int64(a.filled), int64(a.demanded)) 380 out = append(out, LcatCoverage{lcat, a.filled, a.demanded, cov.StringFixed(2), colorFor(cov)}) 381 } 382 sort.SliceStable(out, func(i, j int) bool { // ascending coverage via cross-multiply (exact) 383 li := int64(out[i].Filled) * int64(out[j].Demanded) 384 lj := int64(out[j].Filled) * int64(out[i].Demanded) 385 if li != lj { 386 return li < lj 387 } 388 return out[i].LaborCategory < out[j].LaborCategory 389 }) 390 return out 391} 392 393// proposalFromAssignment buckets each open opportunity (KP-missing > LCAT-missing > at-risk > ready) 394// and returns the proposal-readiness driver % (opportunities fully ASSIGNABLE / open opportunities). 395func proposalFromAssignment(reqs []reqMeta, assigned, qualified map[string]int, opp map[string]oppInfo) (ProposalReadiness, decimal.Decimal) { 396 byOpp := map[string][]reqMeta{} 397 for _, r := range reqs { 398 if r.oppID != "" { 399 byOpp[r.oppID] = append(byOpp[r.oppID], r) 400 } 401 } 402 pr := ProposalReadiness{ 403 ReadyOpportunities: []OppRef{}, AtRiskOpportunities: []OppRef{}, 404 KeyPersonnelMissingOpportunities: []OppRef{}, LaborCategoriesMissingOpportunities: []OppRef{}, 405 } 406 oppIDs := make([]string, 0, len(byOpp)) 407 for id := range byOpp { 408 oppIDs = append(oppIDs, id) 409 } 410 sort.Slice(oppIDs, func(i, j int) bool { 411 ni, nj := opp[oppIDs[i]].name, opp[oppIDs[j]].name 412 if ni != nj { 413 return ni < nj 414 } 415 return oppIDs[i] < oppIDs[j] 416 }) 417 ready := 0 418 for _, oppID := range oppIDs { 419 kpMissing, lcatMissing, fully := false, false, true 420 for _, r := range byOpp[oppID] { 421 if qualified[r.id] == 0 { 422 if r.keyPersonnel { 423 kpMissing = true 424 } else { 425 lcatMissing = true 426 } 427 } 428 if assigned[r.id] < r.quantity { 429 fully = false 430 } 431 } 432 ref := OppRef{OpportunityID: oppID, Name: opp[oppID].name} 433 switch { 434 case kpMissing: 435 pr.KeyPersonnelMissing++ 436 pr.KeyPersonnelMissingOpportunities = append(pr.KeyPersonnelMissingOpportunities, ref) 437 case lcatMissing: 438 pr.LaborCategoriesMissing++ 439 pr.LaborCategoriesMissingOpportunities = append(pr.LaborCategoriesMissingOpportunities, ref) 440 case !fully: 441 pr.AtRisk++ 442 pr.AtRiskOpportunities = append(pr.AtRiskOpportunities, ref) 443 default: 444 pr.Ready++ 445 pr.ReadyOpportunities = append(pr.ReadyOpportunities, ref) 446 ready++ 447 } 448 } 449 return pr, pct(int64(ready), int64(len(oppIDs))) 450} 451