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

1package commandcenter 2 3import ( 4 "context" 5 "fmt" 6 7 "github.com/jackc/pgx/v5" 8 "github.com/shopspring/decimal" 9) 10 11var polyLabels = map[string]string{"full_scope": "Full Scope", "ci": "CI", "none": "None"} 12 13func polyLabel(code string) string { 14 if l, ok := polyLabels[code]; ok { 15 return l 16 } 17 return "None" 18} 19 20// buildTemps materializes the per-org RLS-scoped cand/active_req/qual sets once per request so the 21// set-cover isn't recomputed for every panel. ON COMMIT DROP cleans them up. 22func buildTemps(ctx context.Context, tx pgx.Tx, asOf string) error { 23 // Defense in depth: confirm the tenant GUC is set (RLS active) before any aggregate runs, so a 24 // wrong pool/role fails closed here rather than silently returning a wrong scope [review RLS-2]. 25 var guc string 26 if err := tx.QueryRow(ctx, `SELECT COALESCE(current_setting('app.current_org_id', true),'')`).Scan(&guc); err != nil { 27 return fmt.Errorf("tenant guc check: %w", err) 28 } 29 if guc == "" { 30 return fmt.Errorf("commandcenter: tenant GUC not set, refusing to aggregate") 31 } 32 33 if _, err := tx.Exec(ctx, ` 34 CREATE TEMP TABLE cc_cand ON COMMIT DROP AS 35 SELECT id, full_name, location, available, years_experience, 36 clearance_level, clearance_poly, clearance_status, clearance_expires_at, reinvestigation_due_at, 37 mm_lvl_ord(clearance_level) AS lvl, mm_poly_ord(clearance_poly) AS poly, 38 (clearance_status='active' AND (clearance_expires_at IS NULL OR clearance_expires_at >= $1::date)) AS effective 39 FROM candidates WHERE deleted_at IS NULL`, asOf); err != nil { 40 return fmt.Errorf("cc_cand: %w", err) 41 } 42 if _, err := tx.Exec(ctx, ` 43 CREATE TEMP TABLE cc_active_req ON COMMIT DROP AS 44 SELECT r.id, r.opportunity_id, r.labor_category, COALESCE(r.quantity,1) AS quantity, r.key_personnel, 45 r.required_years, r.required_level, mm_lvl_ord(r.required_level) AS rlvl, mm_poly_ord(r.required_poly) AS rpoly 46 FROM requirements r 47 LEFT JOIN opportunities o ON o.organization_id=r.organization_id AND o.id=r.opportunity_id 48 WHERE r.deleted_at IS NULL AND (o.id IS NULL OR o.status='open')`); err != nil { 49 return fmt.Errorf("cc_active_req: %w", err) 50 } 51 if _, err := tx.Exec(ctx, ` 52 CREATE TEMP TABLE cc_qual ON COMMIT DROP AS 53 WITH mand AS (SELECT requirement_id, array_agg(skill_id) AS skills FROM required_skills WHERE mandatory GROUP BY requirement_id) 54 SELECT ar.id AS req_id, count(DISTINCT c.id) AS n 55 FROM cc_active_req ar 56 JOIN cc_cand c ON c.available 57 AND (ar.rlvl < 2 OR (c.effective AND c.lvl >= ar.rlvl AND c.poly >= ar.rpoly)) 58 AND c.years_experience >= ar.required_years 59 AND NOT EXISTS ( 60 SELECT 1 FROM unnest(COALESCE((SELECT skills FROM mand WHERE mand.requirement_id=ar.id),'{}'::uuid[])) AS rsk 61 WHERE NOT EXISTS (SELECT 1 FROM candidate_skills cs WHERE cs.candidate_id=c.id AND cs.skill_id=rsk) 62 ) 63 GROUP BY ar.id`); err != nil { 64 return fmt.Errorf("cc_qual: %w", err) 65 } 66 _, err := tx.Exec(ctx, `CREATE INDEX ON cc_qual (req_id)`) 67 return err 68} 69 70// drivers computes the four "inventory coverage" drivers (a requirement is COVERED iff >=1 qualified 71// available candidate exists, coverability, not assignment). The fifth driver, proposal_readiness, is 72// derived from the assignment in the service (proposalFromAssignment). 73func (s *Service) drivers(ctx context.Context, tx pgx.Tx) (map[string]decimal.Decimal, decimal.Decimal, error) { 74 d := map[string]decimal.Decimal{} 75 var denom, covered int64 76 77 if err := tx.QueryRow(ctx, ` 78 WITH clreq AS (SELECT rlvl, rpoly FROM cc_active_req WHERE rlvl>=2) 79 SELECT count(*), count(*) FILTER (WHERE EXISTS ( 80 SELECT 1 FROM cc_cand c WHERE c.available AND c.effective AND c.lvl>=clreq.rlvl AND c.poly>=clreq.rpoly)) 81 FROM clreq`).Scan(&denom, &covered); err != nil { 82 return nil, decimal.Zero, fmt.Errorf("clearance cov: %w", err) 83 } 84 d["clearance_coverage"] = pct(covered, denom) 85 86 if err := tx.QueryRow(ctx, ` 87 WITH skreq AS (SELECT ar.id FROM cc_active_req ar WHERE EXISTS (SELECT 1 FROM required_skills rs WHERE rs.requirement_id=ar.id AND rs.mandatory)) 88 SELECT count(*), count(*) FILTER (WHERE COALESCE(q.n,0)>0) FROM skreq LEFT JOIN cc_qual q ON q.req_id=skreq.id`). 89 Scan(&denom, &covered); err != nil { 90 return nil, decimal.Zero, fmt.Errorf("skill cov: %w", err) 91 } 92 d["skill_coverage"] = pct(covered, denom) 93 94 if err := tx.QueryRow(ctx, ` 95 WITH reqcov AS (SELECT COALESCE(NULLIF(trim(ar.labor_category),''),'Uncategorized') AS lcat, bool_or(COALESCE(q.n,0)>0) AS covered 96 FROM cc_active_req ar LEFT JOIN cc_qual q ON q.req_id=ar.id 97 WHERE ar.labor_category IS NOT NULL AND trim(ar.labor_category)<>'' GROUP BY 1) 98 SELECT count(*), count(*) FILTER (WHERE covered) FROM reqcov`).Scan(&denom, &covered); err != nil { 99 return nil, decimal.Zero, fmt.Errorf("lcat cov: %w", err) 100 } 101 d["labor_category_coverage"] = pct(covered, denom) 102 103 var availNum, clearedDen int64 104 if err := tx.QueryRow(ctx, `SELECT count(*) FILTER (WHERE available AND effective AND lvl>=2), count(*) FILTER (WHERE effective AND lvl>=2) FROM cc_cand`). 105 Scan(&availNum, &clearedDen); err != nil { 106 return nil, decimal.Zero, fmt.Errorf("avail cov: %w", err) 107 } 108 d["availability_coverage"] = pct(availNum, clearedDen) 109 110 var reqDen, reqCov int64 111 if err := tx.QueryRow(ctx, `SELECT count(*), count(*) FILTER (WHERE COALESCE(q.n,0)>0) FROM cc_active_req ar LEFT JOIN cc_qual q ON q.req_id=ar.id`). 112 Scan(&reqDen, &reqCov); err != nil { 113 return nil, decimal.Zero, fmt.Errorf("avail req cov: %w", err) 114 } 115 return d, pct(reqCov, reqDen), nil 116} 117 118func (s *Service) clearance(ctx context.Context, tx pgx.Tx, asOf string) (ClearanceIntel, error) { 119 ci := ClearanceIntel{} 120 var sec, secA, ts, tsA, tsci, tsciA, ciN, ciA, fs, fsA int64 121 if err := tx.QueryRow(ctx, `SELECT 122 count(*) FILTER (WHERE effective AND lvl=3), count(*) FILTER (WHERE effective AND lvl=3 AND available), 123 count(*) FILTER (WHERE effective AND lvl=4), count(*) FILTER (WHERE effective AND lvl=4 AND available), 124 count(*) FILTER (WHERE effective AND lvl=5), count(*) FILTER (WHERE effective AND lvl=5 AND available), 125 count(*) FILTER (WHERE effective AND poly>=1), count(*) FILTER (WHERE effective AND poly>=1 AND available), 126 count(*) FILTER (WHERE effective AND poly=2), count(*) FILTER (WHERE effective AND poly=2 AND available) 127 FROM cc_cand`).Scan(&sec, &secA, &ts, &tsA, &tsci, &tsciA, &ciN, &ciA, &fs, &fsA); err != nil { 128 return ci, fmt.Errorf("clearance inventory: %w", err) 129 } 130 ci.InventoryByLevel = []LevelCount{ 131 {"secret", "Secret", int(sec), int(secA)}, 132 {"top_secret", "Top Secret", int(ts), int(tsA)}, 133 {"ts_sci", "TS/SCI", int(tsci), int(tsciA)}, 134 } 135 ci.InventoryByPoly = []PolyCount{ 136 {"ci", "CI Poly", int(ciN), int(ciA)}, 137 {"full_scope", "FS Poly", int(fs), int(fsA)}, 138 } 139 140 var now, d30, d60, d90 int64 141 if err := tx.QueryRow(ctx, `SELECT 142 count(*) FILTER (WHERE available AND effective), 143 count(*) FILTER (WHERE available AND effective AND (clearance_expires_at IS NULL OR clearance_expires_at >= $1::date + 30)), 144 count(*) FILTER (WHERE available AND effective AND (clearance_expires_at IS NULL OR clearance_expires_at >= $1::date + 60)), 145 count(*) FILTER (WHERE available AND effective AND (clearance_expires_at IS NULL OR clearance_expires_at >= $1::date + 90)) 146 FROM cc_cand`, asOf).Scan(&now, &d30, &d60, &d90); err != nil { 147 return ci, fmt.Errorf("avail windows: %w", err) 148 } 149 ci.AvailabilityWindows = AvailWindows{int(now), int(d30), int(d60), int(d90)} 150 151 var expired, due30, due60, due90, reinv int64 152 if err := tx.QueryRow(ctx, `SELECT 153 count(*) FILTER (WHERE clearance_expires_at < $1::date), 154 count(*) FILTER (WHERE clearance_expires_at >= $1::date AND clearance_expires_at < $1::date + 30), 155 count(*) FILTER (WHERE clearance_expires_at >= $1::date AND clearance_expires_at < $1::date + 60), 156 count(*) FILTER (WHERE clearance_expires_at >= $1::date AND clearance_expires_at < $1::date + 90), 157 count(*) FILTER (WHERE reinvestigation_due_at < $1::date) 158 FROM candidates WHERE deleted_at IS NULL AND clearance_status='active'`, asOf). 159 Scan(&expired, &due30, &due60, &due90, &reinv); err != nil { 160 return ci, fmt.Errorf("expiring counts: %w", err) 161 } 162 163 alerts := []ExpiryAlert{} 164 arows, err := tx.Query(ctx, `SELECT id::text, full_name, clearance_level, (clearance_expires_at - $1::date) 165 FROM candidates WHERE deleted_at IS NULL AND clearance_status='active' AND clearance_expires_at IS NOT NULL 166 AND clearance_expires_at >= $1::date AND clearance_expires_at < $1::date + 90 167 ORDER BY clearance_expires_at, id LIMIT 50`, asOf) 168 if err != nil { 169 return ci, fmt.Errorf("expiry alerts: %w", err) 170 } 171 for arows.Next() { 172 var a ExpiryAlert 173 var days int 174 if err := arows.Scan(&a.CandidateID, &a.FullName, &a.ClearanceLevel, &days); err != nil { 175 arows.Close() 176 return ci, err 177 } 178 a.DaysUntilExpiry = days 179 switch { 180 case days < 30: 181 a.Severity = "critical" 182 case days < 60: 183 a.Severity = "warning" 184 default: 185 a.Severity = "info" 186 } 187 alerts = append(alerts, a) 188 } 189 arows.Close() 190 if err := arows.Err(); err != nil { 191 return ci, err 192 } 193 ci.Expiring = ExpiringSummary{int(expired), int(due30), int(due60), int(due90), int(reinv), alerts} 194 return ci, nil 195} 196 197func (s *Service) winImpact(ctx context.Context, tx pgx.Tx) (WinImpact, error) { 198 const where = `c.available AND c.effective AND c.lvl=5 199 AND (c.poly>=1 OR EXISTS (SELECT 1 FROM candidate_customer_experience cce WHERE cce.candidate_id=c.id)) 200 AND EXISTS (SELECT 1 FROM candidate_skills cs WHERE cs.candidate_id=c.id)` 201 wi := WinImpact{Top: []WinCandidate{}} 202 var count int64 203 if err := tx.QueryRow(ctx, `SELECT count(*) FROM cc_cand c WHERE `+where).Scan(&count); err != nil { 204 return wi, fmt.Errorf("win impact count: %w", err) 205 } 206 wi.HighImpactCount = int(count) 207 rows, err := tx.Query(ctx, `SELECT c.id::text, c.full_name, c.clearance_level, c.clearance_poly, c.years_experience 208 FROM cc_cand c WHERE `+where+` ORDER BY c.poly DESC, c.years_experience DESC, c.full_name, c.id LIMIT 10`) 209 if err != nil { 210 return wi, fmt.Errorf("win impact top: %w", err) 211 } 212 for rows.Next() { 213 var wc WinCandidate 214 var poly string 215 var years int 216 if err := rows.Scan(&wc.CandidateID, &wc.FullName, &wc.ClearanceLevel, &poly, &years); err != nil { 217 rows.Close() 218 return wi, err 219 } 220 wc.Poly = polyLabel(poly) 221 wc.YearsExperience = years 222 wi.Top = append(wi.Top, wc) 223 } 224 rows.Close() 225 return wi, rows.Err() 226} 227 228func (s *Service) heatMap(ctx context.Context, tx pgx.Tx) ([]HeatMetro, error) { 229 rows, err := tx.Query(ctx, ` 230 SELECT COALESCE(NULLIF(trim(location),''),'Unknown'), 231 count(*), count(*) FILTER (WHERE available), count(*) FILTER (WHERE effective AND lvl>=2) 232 FROM cc_cand GROUP BY 1 ORDER BY 2 DESC, 1 LIMIT 25`) 233 if err != nil { 234 return nil, fmt.Errorf("heat map: %w", err) 235 } 236 out := []HeatMetro{} 237 for rows.Next() { 238 var m HeatMetro 239 var total, avail, cleared int64 240 if err := rows.Scan(&m.Metro, &total, &avail, &cleared); err != nil { 241 rows.Close() 242 return nil, err 243 } 244 m.Total, m.Available, m.Cleared = int(total), int(avail), int(cleared) 245 out = append(out, m) 246 } 247 rows.Close() 248 return out, rows.Err() 249} 250 251// ---------- pure Go assembly ---------- 252 253func buildActionCenter(suggestReq *string, opps []Opportunity, openNoSlate, reqsUnscreened int) ActionCenter { 254 var suggestOpp *string 255 for i := range opps { 256 if opps[i].StaffingRiskCode == "high" { // opps already sorted by value desc 257 id := opps[i].OpportunityID 258 suggestOpp = &id 259 break 260 } 261 } 262 return ActionCenter{ 263 SuggestedRequirementID: suggestReq, SuggestedOpportunityID: suggestOpp, 264 OpenOpportunitiesWithoutSlate: openNoSlate, RequirementsUnscreened: reqsUnscreened, 265 Launch: []Launch{ 266 {"slateMATCH", "generate_slate", "Generate Slate", "/slates/generate"}, 267 {"screenMATCH", "screen_candidate", "Screen Candidate", "/match"}, 268 {"missionMATCH", "open_mission", "Refresh", "/api/command-center"}, 269 }, 270 } 271} 272 273func buildRecommendations(v View) []Recommendation { 274 recs := []Recommendation{} 275 for i, g := range v.StaffingGaps { 276 if i >= 4 { 277 break 278 } 279 sev := "medium" 280 if g.BlocksKeyPersonnel || g.Unmet >= 5 { 281 sev = "high" 282 } 283 msg := fmt.Sprintf("Need %d more %s %s", g.Unmet, g.RequiredLevelLabel, pluralize(g.LaborCategory, int64(g.Unmet))) 284 if len(g.ImpactedOpportunities) > 0 { 285 msg += " for " + g.ImpactedOpportunities[0] 286 } 287 msg += "." 288 recs = append(recs, Recommendation{ 289 Severity: sev, Message: msg, ActionTarget: "slate", RelatedIDs: []string{}, 290 SourceRef: "gap:" + g.LaborCategory + ":" + g.RequiredLevel, 291 }) 292 } 293 if v.ClearanceIntelligence.Expiring.Due30 > 0 { 294 recs = append(recs, Recommendation{ 295 Severity: "medium", 296 Message: fmt.Sprintf("%d clearances expire within 30 days, schedule reinvestigations.", v.ClearanceIntelligence.Expiring.Due30), 297 ActionTarget: "mission", RelatedIDs: []string{}, SourceRef: "expiry:due_30", 298 }) 299 } 300 return recs 301} 302 303func pluralize(s string, n int64) string { 304 if n == 1 { 305 return s 306 } 307 return s + "s" 308} 309 310func kpClause(blocks bool) string { 311 if blocks { 312 return "; blocks key personnel" 313 } 314 return "" 315} 316