interface:
  display_name: "OT Cost Analysis"
  short_description: "Build auditable milestone-based OT cost analyses"
  default_prompt: "Use $ot-cost-analysis to build an OT should-cost, contribution model, and funding profile from my approved milestones."

policy:
  allow_implicit_invocation: true

# OT Authority and Cost-Sharing Reference

Use the statute and current DoD guidance as the controlling sources. This reference prevents an analytical workbook from silently making an Agreements Officer decision.

## 1. Authority map

### 10 U.S.C. 4021 Research OT

- Applies to basic, applied, and advanced research projects.
- Under subsection (e)(2), to the extent the Secretary determines practicable, Government funds should not exceed the total amount provided by other parties.
- This is not an automatic 50/50 rule, and it is not authority to default to 100% Government funding.
- Require the user-supplied, approved contribution arrangement and record its rationale and source.

### 10 U.S.C. 4022 Prototype OT

Require the user or Agreements Officer to identify one subsection (d)(1) condition:

| Path | Statutory condition | Workbook handling |
|---|---|---|
| A | At least one nontraditional defense contractor or nonprofit research institution participates to a significant extent | Record the participant and user-supplied basis. Do not decide significance. No statutory ratio follows from Path A alone. |
| B | All significant non-Government participants are small businesses or nontraditional defense contractors | Record the participant list and user-supplied status. No statutory ratio follows from Path B alone. |
| C | At least one-third of total prototype-project cost is paid from non-Federal sources | Require performer ratio `>= 1/3` of total project cost. Reject `0.33` if it rounds below exactly one-third. |
| D | The senior procurement executive determines in writing that exceptional circumstances justify the OT | Record the determination date or `PENDING`; do not invent it. No statutory ratio follows from Path D alone. |

There is no 4022(d)(1)(D) competition-commitment path.

### 10 U.S.C. 4022(f) Follow-On Production

- Section 4022(d) does not apply to a follow-on production contract or transaction under subsection (f).
- Do not carry the prototype ratio automatically. Do not set the production ratio to zero automatically. Use the negotiated arrangement supplied by the user.
- Record, without deciding, whether the user states that competitive procedures selected the prototype participants and that the relevant participant successfully completed the prototype.
- Do not originate the follow-on eligibility finding or characterize a prototype as successfully completed.

## 2. Cost-share denominator and reconciliation

The approved performer ratio applies to total project cost, not only to the Government-requested payment.

```text
total project cost = Government project share + performer project share
performer share    = total project cost * performer ratio
Government share   = total project cost - performer share
```

For 4022 Path C:

```text
performer ratio >= 1 / 3
Government ratio <= 2 / 3
```

Keep cash and in-kind contributions visible. Record the source, valuation method, timing, and tracking responsibility for in-kind contributions. Do not treat an undocumented asserted value as validated.

## 3. Proposed-amount normalization

Confirm one basis for each submitted amount:

- Total project cost
- Requested Government funding
- Fixed milestone payment
- Cost-type estimated cost
- Cost-type ceiling
- Consortium or administrative fee

Normalize before comparison. For example, a Government funding request under a one-third performer share is not comparable to the workbook's total project cost without converting one side.

## 4. Fees

Do not default a consortium or administrative fee. Require:

- Percentage or fixed amount
- Cost base
- Per-milestone, one-time, or other timing
- Whether the fee is Government-paid, shared, or included in total project cost
- Source document or user approval

The workbook's default fee input is zero. A fee added after project-cost shares must not be presented as part of the statutory Path C denominator unless the user supplies that treatment.

## 5. Price-reasonableness boundary

The workbook may report:

- Proposed and normalized amount bases
- Dollar and percentage variances
- BLS and CALC+ benchmark positions
- Analogous-price comparisons
- Low, working, and high scenario positions
- Sensitivity to labor, material, schedule, cost share, and fee assumptions

It may not originate a conclusion that a price is fair, reasonable, acceptable, justified, competitive, aggressive, or premium. The Agreements Officer owns the conclusion and negotiation position.

For controlled memo fill, reproduce the user's rationale and determination verbatim, label it `DRAFT - USER-SUPPLIED DETERMINATION TEXT`, and keep the neutral analysis distinct.

## 6. Primary sources

- 10 U.S.C. 4021, Research projects: transactions other than contracts and grants
- 10 U.S.C. 4022, Authority of the Department of Defense to carry out certain prototype projects
- DoD Other Transaction Guide, OUSD(A&S), July 2023, Version 2.0
- Guide to Research Other Transactions, OUSD(R&E), February 2026

# OT Data Source Operations

Use stable server and operation names. The host may wrap them in a namespace; never copy the wrapper into the workbook.

## 1. Request pacing

Serialize credentialed federal API calls and leave at least three seconds between them, including capability checks. Do not parallelize keyed BLS or Per Diem calls. Honor a longer provider retry interval. Stop and report a rate limit instead of rapidly retrying. Use reasonable pacing for unkeyed services too.

## 2. SOC mapping

Classify the work before selecting a code. Record ambiguous mappings and alternatives queried.

| OT role | Common SOC | BLS title |
|---|---|---|
| IT Program Manager | 11-3021 | Computer and Information Systems Managers |
| Engineering or R&D Manager | 11-9041 | Architectural and Engineering Managers |
| Project Manager | 13-1082 | Project Management Specialists |
| Management Analyst | 13-1111 | Management Analysts |
| Computer Systems Analyst | 15-1211 | Computer Systems Analysts |
| Information Security Analyst | 15-1212 | Information Security Analysts |
| Software Developer | 15-1252 | Software Developers |
| Software QA Analyst or Tester | 15-1253 | Software Quality Assurance Analysts and Testers |
| Data Scientist | 15-2051 | Data Scientists |
| Aerospace Engineer | 17-2011 | Aerospace Engineers |
| Electrical Engineer | 17-2071 | Electrical Engineers |
| Electronics Engineer | 17-2072 | Electronics Engineers, Except Computer |
| Industrial Engineer | 17-2112 | Industrial Engineers |
| Mechanical Engineer | 17-2141 | Mechanical Engineers |
| Multidiscipline physical Systems Engineer | 17-2199 | Engineers, All Other |
| Physicist | 19-2012 | Physicists |
| Chemist | 19-2031 | Chemists |
| Materials Scientist | 19-2032 | Materials Scientists |
| Technical Writer | 27-3042 | Technical Writers |
| Engineering Technician, other | 17-3029 | Engineering Technologists and Technicians, Except Drafters, All Other |

Use 11-1021 for operations management, 11-3021 for IT management, and 11-9041 for physical engineering management unless better evidence supports another code. Do not map every Program Manager to the same SOC.

Robotics, autonomy, artificial intelligence, directed energy, quantum, hypersonics, and similar labels often span disciplines. Price the actual work through supported roles rather than using a fictional specialty SOC. Label every proxy.

## 3. BLS OEWS

### Runtime vintage

Call `detect_latest_year` before wage retrieval. Record the returned year and use it in the Summary, Labor Benchmarking, Methodology, Raw Data, and aging formula. A baseline written in the skill never overrides the runtime result.

### Wage retrieval

Call:

```text
get_wage_data(
  occ_code=<six-character SOC>,
  scope=<metro|state|national>,
  area_code=<MSA or state FIPS>,
  datatypes=["04", "11", "12", "13", "14", "15"]
)
```

The requested data types provide mean plus P10, P25, P50, P75, and P90. Do not send unsupported data types.

Fallback ladder:

1. Metro
2. State, only after the current metro code and series are checked
3. National

Use `list_common_metros` and other server lookup operations to resolve codes. Record every fallback and reason.

When BLS lacks job levels, use a disclosed percentile convention. If no levels are supplied, use P50 and do not invent a junior, mid, and senior mix. Treat any capped percentile as a lower bound after confirming the current reporting cap from the returned data or BLS release metadata.

Keep every performer and location separate. Do not average unlike MSAs unless the user supplies an allocation and approves a weighted calculation.

## 4. CALC+

### Silent-wrong-answer signature

The keyword route is:

```text
/v3/api/ceilingrates/?keyword=<term>
```

Use `keyword=`. Never use `q=`. The wrong parameter may be accepted while returning an unfiltered corpus.

Discovery buckets are found at:

```text
aggregations.labor_category.buckets[*].key
aggregations.labor_category.buckets[*].doc_count
```

### Discovery-first flow

1. Call `suggest_contains(field="labor_category", term=<term>)`.
2. When exact buckets form a usable pool, call `exact_search(field="labor_category", value=<bucket>)`.
3. When buckets are fragmented, call `keyword_search(keyword=<term>)` and disclose that other fields may match.
4. Call `igce_benchmark` or the current equivalent for compact percentiles and sample size.
5. Use `page_size=1` when an operation requires a page size for aggregation retrieval. Never send zero.

For senior roles, keep title-match and experience-match pools separate. Label a small pool as directional. When results are absent, try one broader supported term and then record `No CALC+ data; BLS and other supplied sources only.` Do not invent a defense specialty premium.

CALC+ contains awarded ceiling rates from another market and contract context. It is positioning evidence, not a binding OT rate or a conclusion.

## 5. GSA Per Diem

For one or more nights, call:

```text
estimate_travel_cost(
  city=<civilian locality>,
  state=<state>,
  num_nights=<integer>,
  travel_month=<optional month>,
  fiscal_year=<optional FY>
)
```

Use the returned first/last-day M&IE treatment once. Do not apply 75 percent a second time.

For a day trip, do not pass zero nights to an operation that requires at least one. Use `lookup_city_perdiem`, apply the returned first/last-day M&IE once, and keep lodging at zero.

Translate a military installation or laboratory to the correct civilian GSA locality and record the crosswalk. Verify the returned county when locality boundaries matter.

If the requested fiscal year is not published, retry the most recent available year once, record both years, and flag the data for refresh. Do not silently query a different year.

GSA Per Diem covers CONUS travel. Use OCONUS rates only from a user-supplied approved source.

## 6. Raw-data record

Record compact reproducible fields, not full payloads:

- BLS operation, SOC, scope, area, data types, returned vintage, selected percentile, and fallback
- CALC+ operation, exact bucket or keyword, count, percentiles, query date, and pool limitation
- Per Diem locality, fiscal year, month, nights, lodging, M&IE, and fallback
- Every proxy, derivation, user override, and source date

Do not write API keys, access tokens, user paths, host-generated tool names, or internal prompt text to the workbook.

# OT Labor and Costing Methods

## 1. Labor basis

Use per-category labor as the canonical method:

```text
direct hourly benchmark = aged annual wage / 2,080
burdened benchmark      = direct hourly benchmark * approved burden multiplier
category hours          = supplied hours, or productive hours * FTE * duration / 12
category cost           = category hours * burdened benchmark
milestone labor         = sum(category cost)
```

Do not use 1,880 productive hours to convert annual BLS wages to hourly. Use 2,080 for wage conversion and the user-approved productive-hours value for staffing capacity.

Use a blended labor rate only for an explicitly approved early planning approximation. Preserve the category mix used to calculate it.

## 2. Staffing and milestone duration

Prefer user-supplied hours or milestone staffing. When deriving a model:

- Give every category a workload, deliverable, coverage, system, site, quantity, or schedule basis.
- Keep performer and location tags on every line.
- Show ramp, overlap, transition, and surge separately.
- Reconcile milestone durations to the total period of performance. When the sum differs by more than 10 percent, stop and ask whether gaps are unstaffed or whether staffing spans the full period.
- Never invent a specialty premium, clearance factor, academic billing range, or team-size table as a fact.

For a 24x7x365 seat:

```text
annual coverage hours = seats * 24 * 365
coverage FTE          = annual coverage hours / productive hours per FTE
```

At 1,880 productive hours, one seat is 4.6596 FTE.

## 3. Institutional performers

When a university, FFRDC, UARC, laboratory, or other institution supplies billing rates or an approved indirect structure, use those values as inputs and record the source. BLS remains a market context or proxy when appropriate, not a substitute for an institutional rate.

Do not claim that all such institutions have a common burden range. Do not assume fee or profit treatment.

## 4. Materials and production

Use a bill of material, quote, analogous buy, quantity estimate, engineering build-up, or user-approved planning factor. Record quantity, unit, source date, and escalation basis.

Default time basis:

```text
escalated materials = base materials * (1 + approved annual rate)^(months from project start / 12)
```

Use a learning curve only when the user supplies or approves:

- Curve method, such as unit or cumulative-average
- Slope
- Lot or unit sequence
- Recurring-cost base
- Excluded nonrecurring cost

No universal 95-percent curve exists.

## 5. Cost-type milestone view

For a cost-type milestone, show:

1. Independent should-cost
2. User-approved estimated-cost or ceiling basis
3. Government and performer shares at the should-cost basis
4. Maximum Government and planned performer exposure at the ceiling basis

The ceiling view is a planning maximum, not actual incurred cost. Both shares must branch on the same ceiling basis or they will not reconcile.

## 6. Funding timing

Do not default every OT to obligation-at-start or payment-at-acceptance. Require the user-supplied convention by milestone and fiscal year. Distinguish:

- Planned obligation
- Milestone payment
- Cost reimbursement
- Holdback or retainage
- Fee payment
- Performer contribution timing

The cumulative funding profile sums the Government funding requirement using the approved timing convention.

## 7. Scenarios

Keep scenario assumptions editable and sourced. Vary the components that drive uncertainty:

- Labor burden or approved indirect rates
- Staffing or hours
- Materials quantities and unit prices
- Escalation
- Travel frequency
- Ceiling margin
- Performer ratio when the Agreements Officer specifically requests sensitivity
- Fee basis

Do not label scenario positions with conclusions such as competitive or premium. Report the proposed amount's numerical position in the range.

# Professional product standard

This file is the canonical source for the copy packaged with each 1102tools skill.
The packaged copies must remain identical to this file.

## Product judgment

Produce a finished professional work product, not a record of the process used
to create it. Write for the person who must understand, use, approve, or act on
the result.

Exercise editorial judgment. Include material that improves the reader's
understanding or decision. Omit material merely because it was collected,
available, or easy to generate. Every page, section, table, and visual must earn
its place.

Match the structure, length, voice, and visual treatment to the assignment.
Do not reuse a universal report outline. A short decision card, formal
contract-file document, analytical workbook, landscape, timeline, or longer
consulting report may all be correct products for different requests.

Lead with the useful output. Research mechanics, process narration,
methodology, limitations, and compliance controls are secondary unless the
reader's purpose makes one of them the product.

## Controlled freedom

Route rules define the substantive outcome and genuine formal boundaries. They
do not prescribe identical headings, page counts, layouts, or section order
unless a law, supplied template, calculation model, or downstream interface
requires it.

Choose the clearest form for each idea:

- prose for explanation and judgment;
- tables for real comparison or repeated fields;
- cards, profiles, matrices, timelines, charts, and callouts when they improve
  comprehension;
- appendices only for material the intended reader may reasonably need.

Do not put paragraph-length narrative in narrow table cells. Do not repeat the
same information as a callout, table, and prose section unless each form serves
a different reader need.

Use a restrained, coherent design with clear hierarchy, readable typography,
comfortable spacing, and accessible contrast. Treat examples as quality
references, not templates to copy.

## Paid-value standard

The primary artifact should contain the analysis, comparison, requirements,
model, or operating guidance the customer is paying to receive. Audit material
must remain subordinate.

Before delivery, remove:

- generic background the intended reader already knows;
- duplicated findings or actions;
- query logs, tool operations, sanitized parameters, and internal record
  mechanics;
- generic owners, gates, scenarios, or warnings invented to fill a template;
- boilerplate disclaimers repeated in multiple sections;
- empty sections and tables that merely announce missing content.

When evidence is insufficient for the promised product, say so plainly and
provide the useful narrower result or acquisition plan for the missing evidence.
Do not pad an evidence gap into a document that resembles a completed analysis.

## Reader-facing source citations

Internal evidence identifiers such as `E001` may remain in a private research
record for backward compatibility, but they never appear in a customer-facing
artifact.

Assign each distinct reader-visible source an identifier in order of first
appearance: `S1`, `S2`, `S3`, with no leading zeros. Reuse the same identifier
wherever that source supports another claim. Cite sources beside the supported
claim using forms such as `[S1]`, `[S1, S4]`, or `[S1-S3]`.

End a sourced report with a concise `Source Register`. Each entry uses the
corresponding identifier and provides enough information to verify the source:
publisher or organization, title or record identity, relevant date, and a
clickable public URL or supplied-document locator. Deduplicate sources. Do not
display internal source-class tokens or a query-by-query research log.

Native legal and acquisition citations such as `FAR 10.001`, a docket number,
PIID, UEI, or document section remain in their ordinary form. Add an `S` citation
when the artifact also needs a link to the supporting source; do not replace the
native citation with an opaque source number.

For workbooks, source notes and benchmark rows may use `S` identifiers that
resolve to a Sources or Raw Data register. Formula cells and internal validation
IDs are not reader-facing source citations.

## Proportionate boundaries

Accuracy, authority boundaries, unresolved decisions, and limitations remain
mandatory when material. Present them once, in the least intrusive form that
keeps the product honest. A concise note or callout is preferable to a recurring
legalistic section when the reader needs the answer more than a compliance
lecture.

Do not weaken a formal SOW/PWS, OT project description, acquisition-policy
status analysis, or auditable cost model for stylistic reasons. Formal and
mathematical requirements remain hard constraints. Apply taste to hierarchy,
selection, explanation, and delivery view, not to the removal of necessary
substance.

## Final editorial review

After technical validation and rendering, review the complete artifact as a
demanding customer:

- Is the useful result apparent immediately?
- Did the author select and synthesize rather than dump everything collected?
- Does each section materially advance the reader's work?
- Are sources integrated credibly without dominating the product?
- Are limitations accurate and proportionate?
- Does the artifact feel composed for this assignment rather than populated
  from a universal template?
- Is this work product an experienced professional could confidently sell?

Revise until the answer to every applicable question is yes. Structural
validation is a technical floor, not the release decision.

# OT Cost Analysis Runtime Adaptation

## Structured questions

Use a structured input tool for short mutually exclusive choices when the host exposes one. Otherwise present numbered choices, accept a number, label, or correction, and preserve every stop-and-wait gate. Do not mention a host-specific function name.

## MCP discovery

Match stable server and operation names semantically:

- `bls-oews`: `detect_latest_year`, `get_wage_data`, metro and SOC lookup operations
- `gsa-calc`: `suggest_contains`, `exact_search`, `keyword_search`, `igce_benchmark`
- `gsa-perdiem`: `estimate_travel_cost`, `lookup_city_perdiem`, `get_mie_breakdown`

If the stable operation is absent, inspect the same server for an equivalent schema. Stop when the required capability is unavailable. Do not replace it with undocumented API code.

## Workbook authoring

Follow the host's authoritative spreadsheet instructions before choosing an authoring route. Use a host spreadsheet workflow when it preserves exact formulas, formats, names, and source notes. Use Python and openpyxl only when the host does not provide a governing spreadsheet workflow that prohibits or hard-stops that substitute. Never bypass a host spreadsheet hard stop by guessing dependency paths or switching authoring libraries. Resolve bundled scripts relative to the skill directory. Require Python 3.10 or later and openpyxl for deterministic validation.

Before the first artifact-specific approval, state whether full workbook mode is available. If it is not, preserve all approved inputs and offer the workbook specification as structured JSON plus Markdown or CSV tables, or ask the user to continue in a maintained client surface that supports workbook generation. Do not label the fallback as a completed workbook.

## Formula execution

Openpyxl reads and writes formulas but does not calculate them.

1. Run structural validation.
2. Independently recompute from raw validation inputs.
3. Detect `soffice` or the LibreOffice application executable.
4. Recalculate a temporary copy, never the delivered original.
5. Reopen the calculated copy with `data_only=True` and compare cached results.

If no engine is available, disclose the missing layer exactly as the core instructs.

## File delivery

Choose a user-supplied destination, then a host output directory, then the current working directory. Use the host's artifact-presentation capability when available; otherwise return the absolute path. Do not assume `/mnt`, `/tmp`, a named presentation function, or one operating system.

Confirm the file exists before reporting success. Do not overwrite a user file unless requested.

# OT Cost Analysis Validation Gates

Run all available layers against the final workbook and rerun after any change.

Delivery gate: the workbook must pass the `validate_workbook.py` structural
audit with the canonical seven-sheet layout before delivery. A bespoke sheet
set is not permitted even when its content is correct. A validator failure of
any kind, including missing canonical sheets or missing print setup, blocks
delivery; fix and revalidate instead of delivering with caveats.

## 1. Validation input

Create temporary JSON from the same raw objects used to build the workbook. Minimal shape:

```json
{
  "assumptions": {
    "authority": "prototype",
    "authority_path": "C",
    "performer_share_ratio": 0.3333333333333333,
    "fee_rate": 0.02,
    "burden_multiplier": 2.0,
    "labor_aging_factor": 1.03,
    "labor_escalation_rate": 0.025,
    "materials_escalation_rate": 0.03,
    "cost_type_ceiling_margin": 0.15
  },
  "milestones": [
    {
      "id": "M1",
      "payment_type": "Fixed",
      "months_from_start": 0,
      "labor_lines": [
        {"name": "Software Developer", "annual_wage": 132000, "hours": 1880}
      ],
      "materials": 100000,
      "travel": 10000,
      "odcs": 5000,
      "proposed_amount": null,
      "workbook_project_cost_cell": "'OT Cost Summary'!D19",
      "workbook_government_funding_cell": "'OT Cost Summary'!I19"
    }
  ],
  "workbook_total_project_cost_cell": "'OT Cost Summary'!D20",
  "workbook_total_government_funding_cell": "'OT Cost Summary'!I20",
  "formula_assertions": [
    {
      "cell": "'OT Cost Summary'!B8",
      "contains": ["VALUE(LEFT(B7,4))", "VALUE(MID(B7,6,2))"],
      "not_contains": ["DATEDIF", "YEAR("]
    }
  ]
}
```

For production, set `production_ratio_source` to a nonempty user-supplied source. Do not label the ratio inherited. For Path D, set `exceptional_circumstances_confirmed` only from user-supplied facts.

## 2. Formula structure

Run:

```text
python scripts/validate_workbook.py workbook.xlsx --expected inputs.json --engine none
```

The audit checks:

- Seven required sheets
- Summary B8/B9 aging formulas and forbidden `DATEDIF`/`YEAR(` patterns
- Formula presence and formula-error tokens
- Cost-share and fee formulas supplied as assertions
- Blank pre-solicitation proposed cells and conditional variance formulas
- Cost-type Government and performer branches using the same ceiling basis
- No stale `competition commitment` Path D language
- No automatic 100% Government funding claim for 4021 or 4022(f)
- No generated price-reasonableness conclusion
- Every Milestone Detail labor line priced from `Labor Benchmarking` matches its own benchmark row or carries a Basis naming the proxy source
- No category repeats identical hours across milestones without an `Hours basis:` reconciliation note
- Milestone labor hours are derived, not asserted: every priced labor row exposes FTE loading, weeks or duration, and hours-per-FTE-week input cells; its Hours cell is a formula referencing those cells; each milestone carries a formula reconciliation rendering `OK` or `MISMATCH` rather than prose; and no other canonical sheet restates milestone hours as literals
- Narrative columns (Description, Basis, Source note) wrap text and meet the 28-character width floor
- Every canonical sheet has an explicit print area, enables fitToPage scaling (`ws.sheet_properties.pageSetUpPr.fitToPage`), and uses landscape orientation when it spans more than 8 used columns
- For recost workbooks: no benchmark rows for roles absent from Milestone Detail, no lump-sum labor deltas without Hours and Rate cells, and a delta row for every element listed in the optional `recost_register_elements` payload array (list every cost element the change register names; a $0 delta needs a one-line justification)
- A readable first-view dashboard states the prototype, analysis purpose, milestone basis, funding planning range, contribution treatment, key drivers, source limitations, and next action without horizontal scrolling

## 3. Independent recomputation

Run:

```text
python scripts/recompute_expected_values.py inputs.json
```

The script independently computes aged labor, burdened labor, materials escalation, total project cost, contribution shares, fee, cost-type ceiling view, and Government funding requirement. It also validates authority-path arithmetic.

Do not derive expected values from workbook formula results.

## 4. Real-engine verification

Run:

```text
python scripts/validate_workbook.py workbook.xlsx --expected inputs.json --engine auto
```

The validator recalculates a temporary copy through LibreOffice when available and reopens cached values. It scans every cell on every recalculated worksheet for cached spreadsheet errors before comparing referenced cells with the independent computation. Use `--engine libreoffice` to require this layer.

## 5. Named regression gates

### Authority and cost share

- Path D means written exceptional circumstances, never competition commitment.
- Path C performer share is at least exact one-third of total project cost.
- Research ratio is user supplied; no automatic zero or 50 percent.
- Production ratio is user supplied; no automatic zero and no inherited prototype ratio.
- Government and performer project shares reconcile before separately treated fees.

### Proposed amount

- Amount basis is explicit.
- Comparison uses the matching workbook basis.
- A blank proposed amount yields a blank variance, not text or a formula error.

### Formula behavior

- Milestone labor hours are a formula over driver input cells; an asserted hours literal is a delivery failure even when the number is right.
- The per-milestone hours reconciliation is a formula with an `OK` or `MISMATCH` result, not a sentence asserting that the hours reconcile.
- Sheets that restate milestone hours link to `Milestone Detail` instead of duplicating literals.
- BLS aging is cell referenced.
- Materials escalation uses milestone-start months.
- Cost-type Government and performer values use the same ceiling basis.
- Fee uses an approved base and defaults to zero.
- Cumulative Government funding is a running formula.
- Dynamic Milestone Detail blocks do not rely on a fixed row stride.
- Summary milestone and total references resolve to the actual dynamic block total rows; displayed zeroes caused by off-by-one or missing cached references are a delivery failure.

### Data sources and pacing

- CALC+ keyword calls use `keyword=`, never `q=`.
- Discovery records `aggregations.labor_category.buckets` keys and counts.
- `detect_latest_year` controls the BLS vintage.
- Keyed calls are serialized with at least three seconds between them.

### AI boundary

- Workflow B first response stops at Option A or B without tool calls.
- Option A contains neutral evidence and no verdict.
- Option B conclusion text is verbatim user text and visibly marked DRAFT.

## 6. Result language

When all three layers pass, state:

> Formula structure, independent calculations, and LibreOffice formula execution passed.

Without a real engine, state exactly:

> Formula structure and independent calculations passed. Formula execution was not independently verified in Excel or LibreOffice.

# OT Cost Analysis Workbook Specification

Build one `.xlsx` with exactly these seven sheets:

1. `OT Cost Summary`
2. `Milestone Detail`
3. `Scenario Analysis`
4. `Labor Benchmarking`
5. `Cost Share & Funding`
6. `Methodology`
7. `Raw Data`

This sheet set is the delivery contract. A bespoke sheet layout is not permitted
even when its content is correct; `validate_workbook.py` rejects any workbook
missing a canonical sheet, and a rejected workbook is not deliverable.

## 1. OT Cost Summary

### First-view decision dashboard

The first visible area must be a decision dashboard, not a wall of assumptions.
Show the prototype objective, analysis purpose, selected milestone basis,
Government funding planning range, performer contribution treatment, three key
drivers, and next required action. Keep formulas and editable assumptions below
the dashboard. Use a route-specific workbook title, for example "Independent
Prototype Cost Model" or "Recosting Decision Book."

### Assumptions

Keep these cells stable so validators and downstream users can audit formulas:

| Cell | Label | Handling |
|---|---|---|
| B2 | Working burden multiplier | Blue user input; no universal default |
| B3 | Annual labor escalation | Blue user input |
| B4 | Performer share of total project cost | Blue user input; Path C must be at least `1/3` |
| B5 | Government-paid fee rate | Blue user input; zero unless supplied |
| B6 | BLS vintage | `YYYY-MM` text from runtime data |
| B7 | Agreement start | `YYYY-MM` text |
| B8 | Months gap | Formula using `VALUE(LEFT(...))` and `VALUE(MID(...))` |
| B9 | Labor aging factor | Formula `=(1+B3)^(B8/12)` |
| B10 | Materials escalation | Blue user input |
| B11 | Cost-type ceiling margin | Blue user input; zero or blank when not applicable |
| B12 | Productive hours/year | Blue user input |
| B13 | Authority | Text input: `4021 Research`, `4022 Prototype`, or `4022(f) Production` |
| B14 | 4022(d) path | A, B, C, D, or `N/A` |
| B15 | Proposed amount basis | Total project cost, Government request, milestone payment, ceiling, or `None` |

Required month-gap formula pattern in B8:

```text
=(VALUE(LEFT(B7,4))-VALUE(LEFT(B6,4)))*12
 +VALUE(MID(B7,6,2))-VALUE(MID(B6,6,2))
```

Do not use `YEAR()` on text or `DATEDIF`.

### Milestone summary

Start the header at row 18:

| Column | Field |
|---|---|
| A | Milestone ID |
| B | Description |
| C | Payment Type |
| D | Should-Cost |
| E | Ceiling Basis |
| F | Government Project Share |
| G | Performer Project Share |
| H | Fee |
| I | Government Funding Requirement |
| J | Proposed Amount |
| K | Variance Dollars |
| L | Variance Percent |

Use formulas starting at row 19. For a fixed milestone, Ceiling Basis equals Should-Cost. For a cost-type milestone, Ceiling Basis equals Should-Cost multiplied by `(1 + $B$11)` unless the user supplies a fixed ceiling.

Minimum formulas for row 19:

```text
E19 =IF(C19="Cost-Type",D19*(1+$B$11),D19)
F19 =E19*(1-$B$4)
G19 =E19*$B$4
H19 =E19*$B$5
I19 =F19+H19
K19 =IF(J19="","",J19-D19)
L19 =IF(OR(J19="",D19=0),"",(J19-D19)/D19)
```

The proposed-amount comparison formula must match the confirmed basis. If J contains a Government funding request, compare it to I instead of D and label the header accordingly. Never compare unlike bases.

Use a totals row with `SUM` formulas. Keep proposed cells blank in pre-solicitation mode.

## 2. Milestone Detail

Use one dynamic block per milestone. Compute block positions before writing cross-sheet formulas or use defined names. A fixed row stride is not permitted because labor, materials, travel, and ODC line counts vary.

Each block contains:

- Milestone metadata, dates, duration, completion evidence, payment type, and funding convention
- Labor lines: performer, location, category, SOC, hours, burdened-rate reference, and formula cost
- Materials: item, quantity, unit, source, base unit cost, escalation, and formula cost
- Travel: destination, trips, travelers, nights, source FY, and formula cost
- ODC lines and source
- Formula subtotals and total project cost
- Formula Government and performer shares
- Formula fee and Government funding requirement
- For cost-type milestones, the parallel ceiling view

Labor rates reference `Labor Benchmarking`. Do not hardcode a burdened rate in a Milestone Detail cost formula.

Every labor category priced in Milestone Detail MUST either have its own
SOC-matched benchmark row on `Labor Benchmarking`, or carry a Basis cell that
explicitly names the proxy benchmark used and why it is reasonable (the Basis
text must contain the word `proxy`). Never silently reuse another category's
rate under a generic basis such as "Prior bounded source benchmark".

Milestone labor hours MUST be DERIVED in the workbook, never asserted. A
hardcoded hours figure is not auditable: a reviewer who changes staffing or
duration must see the estimate move. Each priced labor row therefore exposes
its drivers as input cells on the row itself and computes hours from them:

| Column role | Cell type | Header text the validator recognizes |
|---|---|---|
| FTE loading | Blue numeric input | a header containing `FTE` |
| Weeks or duration | Blue numeric input | `Weeks`, or a header containing `duration` |
| Hours per FTE-week | Blue numeric input | a header naming hours per week or per FTE |
| Hours | Formula over the three driver cells | exactly `Hours` |

The hours cell is a real formula referencing those cells on its own row, for
example `=C15*D15*E15`. Never write the product as a literal and never move
the FTE and duration arithmetic into Basis prose; the Basis cell explains the
staffing judgment, it does not carry the math.

Each milestone block also carries a visible reconciliation that is itself a
formula, not a sentence. It compares the block's derived hours to the
milestone duration and staffing and renders a clear state, for example:

```text
=IF(ROUND(F20-SUM(C15:C19)*$B$5*E15,4)=0,"OK","MISMATCH")
```

A prose note beginning `Hours basis:` may accompany the check as narrative,
but it does not satisfy it. Do not repeat identical hours per category across
milestones of different durations.

Any sheet that restates milestone hours, `Scenario Analysis` above all, must
reference the `Milestone Detail` hours cells by formula. Repeating the hours
figures as literals on a second sheet breaks the link the workbook exists to
provide.

## 3. Scenario Analysis

Show user-approved low, working, and high assumptions. Include total project cost, Government project share, performer project share, fee, and Government funding requirement for each scenario. Use formulas referencing scenario inputs and milestone totals.

Describe the proposed amount numerically against the range without a verdict.

## 4. Labor Benchmarking

One row per performer, location, labor category, SOC, and level:

- Direct BLS selected percentile
- Runtime BLS vintage
- Aging factor linked to Summary B9
- Aged direct hourly benchmark
- Approved burden multiplier
- Formula burdened benchmark
- CALC+ P25, P50, P75, sample size, query type, and date
- Proxy or fallback note

Keep institutional billing rates distinct from BLS-derived rates and label their source.

## 5. Cost Share & Funding

Per milestone show:

- Total project-cost basis
- Government share dollars and percent
- Performer cash contribution
- Performer in-kind contribution
- Total performer contribution and percent
- Fee and fee treatment
- Government funding requirement
- Planned obligation or payment period
- Cumulative Government funding

Government project share plus total performer contribution must reconcile to total project cost before separately treated fees. The cumulative column uses a running `SUM` formula.

For cost-type milestones, show should-cost and ceiling rows separately. Label ceiling rows `maximum exposure planning view`.

## 6. Methodology

Use sections:

1. Authority and user-supplied eligibility facts
2. Analysis purpose and proposed-amount basis
3. Milestone and should-cost methodology
4. Labor benchmarking
5. Materials, travel, and ODC bases
6. Contribution and fee treatment
7. Neutral comparison and scenarios
8. Data sources and as-of dates (compact evidence table, not raw payloads)
9. Limitations, open decisions, and refresh needs

Do not call the workbook an IGCE or a FAR 15.404 analysis. Do not state a price-reasonableness conclusion. For Workflow B Option B, place only the user's exact text in a separate block titled `DRAFT - USER-SUPPLIED DETERMINATION TEXT`.

## 7. Raw Data

Record compact reproducible inputs and outputs from BLS, CALC+, Per Diem, analogous prices, quotes, and user overrides. Do not paste full payloads or credentials.

## Recosting workbooks

A recosting workbook (any output whose titles use "Recost" or "Recosting")
additionally MUST:

- Carry `Labor Benchmarking` rows only for roles that appear in its own
  Milestone Detail delta rows. Never list benchmark roles, or rates, that are
  tied to nothing in the package.
- Decompose every labor delta as hours x rate per affected labor category. A
  labor delta row carries Hours and Rate cells and computes its delta by
  formula; a single lump-sum labor delta is not acceptable, especially when the
  sheet's own management question asks whether hours and roles are
  proportionate.
- Include a delta row for every cost element the change register names (labor,
  materials, travel, ODCs, or others). When the register directs repricing of
  an element and the analysis concludes no change, carry an explicit $0 delta
  row with a one-line justification, never a silent omission.

## Formula and presentation rules

- Blue font for user-adjustable inputs; black font for formulas.
- Currency: `$#,##0.00;($#,##0.00)`.
- Percentage: `0.0%`.
- Multipliers: `0.0000`.
- Real Excel formulas for every derived value.
- Freeze panes below assumptions and header rows.
- Light header fills, filters, explicit widths, and readable source notes.
- Narrative columns (the Summary milestone Description column, Milestone Detail
  Basis cells, and source-note text) must have wrap text enabled on every
  populated cell and an explicit column width of at least 28 characters so no
  text clips mid-word in the rendered or printed view.
- No formula-error tokens, prompt text, local paths, tool namespaces, keys, or internal instructions.
- Set workbook calculation mode to automatic when supported, but do not claim this evaluates formulas.
- Use differentiated visual treatment by route: independent analysis favors a
  neutral benchmark dashboard; recosting foregrounds before/after deltas and
  changed assumptions. Do not reuse the same title and hierarchy for every output.
- Before delivery, calculate and save the final `.xlsx` through a spreadsheet
  engine so the delivered file carries cached values for every formula displayed
  on `OT Cost Summary`, including each milestone, total should-cost, ceiling
  basis, and Government project share. Render the delivered summary sheet and
  reject blank formula outputs, zeroes that result from missing cached values,
  clipped titles, or a dashboard that requires horizontal scrolling to read.
- Configure the populated print area on every sheet to fit one page wide with no
  fixed page-height limit; use landscape orientation for wide tables. The complete
  `OT Cost Summary` title, decision dashboard, milestone totals, contribution
  treatment, and next action must remain on one page wide when printed or exported.
  Never split the dashboard horizontally.
- Print setup is validated, not optional. Every canonical sheet must carry an
  explicit print area over the populated range (`ws.print_area`) and enable
  fit-to-page scaling (`ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True)`
  in openpyxl; the property object must exist with `fitToPage` true). Any sheet
  wider than 8 used columns must set `ws.page_setup.orientation = "landscape"`.
  `validate_workbook.py` fails the workbook when any canonical sheet misses any
  of these.

### Column widths and text clipping

Excel and LibreOffice let a text cell overflow into the next cell **only when
that neighbour is empty**. The moment the cell to the right of a label carries a
value or a formula, the label is cut off at its own column boundary in the
printed and rendered output, however much white space appears to follow it on
screen. Setting a column width without accounting for this is what produces
labels such as `Government sh`, `0047900 Washington-Arlin`, and
`Scenario Analysis: Senior Software En` in a delivered workbook.

Every populated text cell must therefore satisfy at least one of the following,
and `validate_workbook.py` fails the workbook when none of them holds:

- **Fit.** The label fits inside its own column width.
- **Wrap.** The cell sets `wrap_text=True` and the row height is tall enough to
  show every wrapped line.
- **Free overflow.** Every cell on the label's overflow side in the same row is
  empty. That is the right-hand neighbour for left-aligned and general text, the
  left-hand neighbour for right-aligned text, and both neighbours for centred
  text.
- **Merge.** The label is merged across the columns it needs. The merged span,
  not the anchor column alone, is what has to fit.

Section headers and block titles are the common failure. A title such as
`Scenario Analysis: Senior Software Engineer` must be merged across the block it
introduces, placed on a row whose neighbouring cells are left empty, or shortened
until it fits. Never rely on visual overflow for a block title that sits beside a
populated cell.

Concrete generator guidance:

- Compute each column width from the **longest label actually written to that
  column**, not from the header text or a fixed guess. Walk the column after the
  data is written, take the widest populated string, and set
  `width = min(cap, needed + 2)`.
- Estimate a label in column-width units rather than characters. One unit is
  about one digit at Calibri 11. Lowercase letters run about 0.96 units,
  uppercase about 1.05, `i`/`l`/`j` and spaces and most punctuation about 0.45,
  `m` and `w` about 1.66, and bold text is about 14 percent wider overall.
- Prefer a merged header cell for every block title, and reserve narrow columns
  for short codes, dates, and numbers.
- Where a column must stay narrow, move the long text into a wrapped narrative
  column with an explicit width and an adequate row height. Cap runaway widths
  in the 60 to 90 unit range and wrap instead of widening past that.
- The audit allows roughly one column-width unit of slack, so a label that is
  genuinely borderline will not fail. Do not aim for the tolerance; aim for the
  fit.

The narrative wrap rule above and this clipping rule are one system. Narrative
columns wrap and carry a width floor; every other populated text cell is checked
for clipping. The clipping audit skips any cell the narrative audit has already
reported, so a missing wrap is reported once, not twice.

#!/usr/bin/env python3
"""Independently recompute OT milestone costs from raw validation inputs."""

from __future__ import annotations

import argparse
import json
import math
import sys
from pathlib import Path
from typing import Any


class InputError(ValueError):
    """Raised for invalid validation inputs."""


def _number(value: Any, label: str, *, minimum: float | None = None) -> float:
    if isinstance(value, bool) or not isinstance(value, (int, float)):
        raise InputError(f"{label} must be numeric")
    result = float(value)
    if not math.isfinite(result):
        raise InputError(f"{label} must be finite")
    if minimum is not None and result < minimum:
        raise InputError(f"{label} must be at least {minimum}")
    return result


def _ratio(value: Any, label: str) -> float:
    result = _number(value, label, minimum=0)
    if result > 1:
        raise InputError(f"{label} must not exceed 1")
    return result


def _text(value: Any, label: str) -> str:
    if not isinstance(value, str) or not value.strip():
        raise InputError(f"{label} must be a non-empty string")
    return value.strip()


def _authority(assumptions: dict[str, Any]) -> tuple[str, str]:
    raw = _text(assumptions.get("authority"), "assumptions.authority").lower()
    aliases = {
        "research": "research",
        "4021": "research",
        "4021 research": "research",
        "prototype": "prototype",
        "4022": "prototype",
        "4022 prototype": "prototype",
        "production": "production",
        "4022(f)": "production",
        "4022(f) production": "production",
    }
    if raw not in aliases:
        raise InputError("assumptions.authority must identify research, prototype, or production")
    authority = aliases[raw]
    path = str(assumptions.get("authority_path", "N/A")).strip().upper()
    if authority == "prototype":
        if path not in {"A", "B", "C", "D"}:
            raise InputError("prototype authority_path must be A, B, C, or D")
    elif path not in {"", "N/A", "NA", "NONE"}:
        raise InputError("authority_path applies only to prototype OTs")
    return authority, path or "N/A"


def validate_authority(assumptions: dict[str, Any], performer_ratio: float) -> tuple[str, str]:
    authority, path = _authority(assumptions)
    if authority == "prototype" and path == "C" and performer_ratio + 1e-12 < (1 / 3):
        raise InputError("4022(d)(1)(C) performer share must be at least one-third")
    if authority == "prototype" and path == "D":
        status = _text(
            assumptions.get("exceptional_circumstances_status"),
            "assumptions.exceptional_circumstances_status",
        )
        if "competition" in status.lower() and "exception" not in status.lower():
            raise InputError("4022(d)(1)(D) is exceptional circumstances, not competition commitment")
    if authority == "research":
        _text(assumptions.get("research_ratio_source"), "assumptions.research_ratio_source")
    if authority == "production":
        source = _text(
            assumptions.get("production_ratio_source"),
            "assumptions.production_ratio_source",
        )
        if "inherit" in source.lower():
            raise InputError("production performer ratio must not be automatically inherited")
    return authority, path


def calculate(payload: dict[str, Any]) -> dict[str, Any]:
    assumptions = payload.get("assumptions")
    milestones = payload.get("milestones")
    if not isinstance(assumptions, dict):
        raise InputError("assumptions must be an object")
    if not isinstance(milestones, list) or not milestones:
        raise InputError("milestones must be a non-empty array")

    performer_ratio = _ratio(
        assumptions.get("performer_share_ratio"),
        "assumptions.performer_share_ratio",
    )
    authority, path = validate_authority(assumptions, performer_ratio)
    fee_rate = _ratio(assumptions.get("fee_rate", 0), "assumptions.fee_rate")
    burden = _number(
        assumptions.get("burden_multiplier"),
        "assumptions.burden_multiplier",
        minimum=0,
    )
    aging = _number(
        assumptions.get("labor_aging_factor"),
        "assumptions.labor_aging_factor",
        minimum=0,
    )
    labor_escalation = _ratio(
        assumptions.get("labor_escalation_rate", 0),
        "assumptions.labor_escalation_rate",
    )
    materials_escalation = _ratio(
        assumptions.get("materials_escalation_rate", 0),
        "assumptions.materials_escalation_rate",
    )
    ceiling_margin = _ratio(
        assumptions.get("cost_type_ceiling_margin", 0),
        "assumptions.cost_type_ceiling_margin",
    )

    outputs: list[dict[str, Any]] = []
    total_project_cost = 0.0
    total_government_funding = 0.0
    for index, raw in enumerate(milestones):
        if not isinstance(raw, dict):
            raise InputError(f"milestones[{index}] must be an object")
        milestone_id = _text(raw.get("id"), f"milestones[{index}].id")
        payment_type = _text(
            raw.get("payment_type"),
            f"milestones[{index}].payment_type",
        ).lower()
        if payment_type not in {"fixed", "cost-type"}:
            raise InputError(f"{milestone_id}.payment_type must be Fixed or Cost-Type")
        months = _number(
            raw.get("months_from_start", 0),
            f"{milestone_id}.months_from_start",
            minimum=0,
        )
        labor_factor = aging * ((1 + labor_escalation) ** (months / 12))
        material_factor = (1 + materials_escalation) ** (months / 12)

        labor_lines = raw.get("labor_lines")
        if not isinstance(labor_lines, list) or not labor_lines:
            raise InputError(f"{milestone_id}.labor_lines must be a non-empty array")
        labor_total = 0.0
        calculated_labor: list[dict[str, Any]] = []
        for line_index, line in enumerate(labor_lines):
            if not isinstance(line, dict):
                raise InputError(f"{milestone_id}.labor_lines[{line_index}] must be an object")
            name = _text(line.get("name"), f"{milestone_id}.labor_lines[{line_index}].name")
            annual_wage = _number(line.get("annual_wage"), f"{milestone_id}.{name}.annual_wage", minimum=0)
            hours = _number(line.get("hours"), f"{milestone_id}.{name}.hours", minimum=0)
            line_burden = _number(
                line.get("burden_multiplier", burden),
                f"{milestone_id}.{name}.burden_multiplier",
                minimum=0,
            )
            aged_direct_hourly = (annual_wage / 2080) * labor_factor
            burdened_rate = aged_direct_hourly * line_burden
            cost = burdened_rate * hours
            labor_total += cost
            calculated_labor.append(
                {
                    "name": name,
                    "aged_direct_hourly": aged_direct_hourly,
                    "burdened_rate": burdened_rate,
                    "hours": hours,
                    "cost": cost,
                }
            )

        materials_base = _number(raw.get("materials", 0), f"{milestone_id}.materials", minimum=0)
        materials = materials_base * material_factor
        travel = _number(raw.get("travel", 0), f"{milestone_id}.travel", minimum=0)
        odcs = _number(raw.get("odcs", 0), f"{milestone_id}.odcs", minimum=0)
        project_cost = labor_total + materials + travel + odcs
        ceiling = project_cost * (1 + ceiling_margin) if payment_type == "cost-type" else project_cost
        government_share = ceiling * (1 - performer_ratio)
        performer_share = ceiling * performer_ratio
        fee = ceiling * fee_rate
        government_funding = government_share + fee
        total_project_cost += project_cost
        total_government_funding += government_funding

        output: dict[str, Any] = {
            "id": milestone_id,
            "payment_type": payment_type,
            "labor_lines": calculated_labor,
            "labor_total": labor_total,
            "materials": materials,
            "travel": travel,
            "odcs": odcs,
            "project_cost": project_cost,
            "ceiling_basis": ceiling,
            "government_project_share": government_share,
            "performer_project_share": performer_share,
            "fee": fee,
            "government_funding": government_funding,
        }
        for key in (
            "workbook_project_cost_cell",
            "workbook_government_funding_cell",
            "workbook_ceiling_cell",
            "workbook_performer_share_cell",
        ):
            if key in raw:
                output[key] = _text(raw[key], f"{milestone_id}.{key}")
        outputs.append(output)

    result: dict[str, Any] = {
        "authority": authority,
        "authority_path": path,
        "milestones": outputs,
        "total_project_cost": total_project_cost,
        "total_government_funding": total_government_funding,
    }
    for key in (
        "workbook_total_project_cost_cell",
        "workbook_total_government_funding_cell",
    ):
        if key in payload:
            result[key] = _text(payload[key], key)
    return result


def load_payload(path: Path) -> dict[str, Any]:
    try:
        payload = json.loads(path.read_text(encoding="utf-8"))
    except OSError as exc:
        raise InputError(f"cannot read {path}: {exc}") from exc
    except json.JSONDecodeError as exc:
        raise InputError(f"invalid JSON in {path}: {exc}") from exc
    if not isinstance(payload, dict):
        raise InputError("top-level JSON value must be an object")
    return payload


def main() -> int:
    parser = argparse.ArgumentParser(description="Recompute expected OT milestone values.")
    parser.add_argument("input", type=Path)
    parser.add_argument("--output", type=Path)
    args = parser.parse_args()
    try:
        result = calculate(load_payload(args.input))
    except InputError as exc:
        print(f"ERROR: {exc}", file=sys.stderr)
        return 2
    rendered = json.dumps(result, indent=2, sort_keys=True) + "\n"
    if args.output:
        try:
            args.output.write_text(rendered, encoding="utf-8")
        except OSError as exc:
            print(f"ERROR: cannot write {args.output}: {exc}", file=sys.stderr)
            return 2
    else:
        print(rendered, end="")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

#!/usr/bin/env python3
"""Validate OT workbook structure and compare calculated values."""

from __future__ import annotations

import argparse
import json
import math
import re
import shutil
import subprocess
import sys
import tempfile
import zipfile
from pathlib import Path
from typing import Any

from openpyxl import load_workbook
from openpyxl.utils import column_index_from_string, get_column_letter

from recompute_expected_values import InputError, calculate, load_payload


REQUIRED_SHEETS = [
    "OT Cost Summary",
    "Milestone Detail",
    "Scenario Analysis",
    "Labor Benchmarking",
    "Cost Share & Funding",
    "Methodology",
    "Raw Data",
]
CELL_REF = re.compile(r"^(?:'((?:[^']|'')+)'|([^!]+))!\$?([A-Za-z]{1,3})\$?([1-9][0-9]*)$")


def normalize_formula(value: Any) -> str:
    return re.sub(r"\s+", "", str(value)).upper()


def parse_cell_ref(reference: str) -> tuple[str, str]:
    match = CELL_REF.fullmatch(reference.strip())
    if not match:
        raise InputError(f"invalid workbook cell reference: {reference}")
    sheet = (match.group(1) or match.group(2)).replace("''", "'")
    return sheet, f"{match.group(3).upper()}{match.group(4)}"


def value_at(workbook: Any, reference: str) -> Any:
    sheet, coordinate = parse_cell_ref(reference)
    if sheet not in workbook.sheetnames:
        raise InputError(f"cell reference uses missing sheet: {reference}")
    return workbook[sheet][coordinate].value


def check_formula(
    failures: list[str],
    workbook: Any,
    reference: str,
    *,
    expected: str | None = None,
    contains: list[str] | None = None,
    not_contains: list[str] | None = None,
) -> None:
    try:
        value = value_at(workbook, reference)
    except InputError as exc:
        failures.append(str(exc))
        return
    if not isinstance(value, str) or not value.startswith("="):
        failures.append(f"{reference} is not a formula")
        return
    normalized = normalize_formula(value)
    if expected is not None and normalized != normalize_formula(expected):
        failures.append(f"{reference} formula does not match expected structure")
    for item in contains or []:
        if normalize_formula(item) not in normalized:
            failures.append(f"{reference} formula is missing {item}")
    for item in not_contains or []:
        if normalize_formula(item) in normalized:
            failures.append(f"{reference} formula contains forbidden text {item}")


BENCHMARK_REF = re.compile(r"'?LABOR\s?BENCHMARKING'?!\$?[A-Z]{1,3}\$?([1-9][0-9]*)", re.I)
NARRATIVE_HEADERS = {"description", "basis", "source note"}
MIN_NARRATIVE_WIDTH = 28.0


def _row_strings(row: Any) -> list[str]:
    return [
        cell.value
        for cell in row
        if isinstance(cell.value, str) and not cell.value.startswith("=")
    ]


def _row_benchmark_row_number(row: Any) -> int | None:
    for cell in row:
        value = cell.value
        if isinstance(value, str) and value.startswith("="):
            match = BENCHMARK_REF.search(value)
            if match:
                return int(match.group(1))
    return None


def labor_benchmark_audit(workbook: Any) -> list[str]:
    """Every priced labor line matches its own benchmark row or names a proxy."""
    failures: list[str] = []
    if "Milestone Detail" not in workbook.sheetnames or "Labor Benchmarking" not in workbook.sheetnames:
        return failures
    benchmarks = workbook["Labor Benchmarking"]
    for row in workbook["Milestone Detail"].iter_rows():
        benchmark_row = _row_benchmark_row_number(row)
        if benchmark_row is None:
            continue
        texts = _row_strings(row)
        if not texts:
            continue
        category = texts[0].strip()
        benchmark_value = benchmarks.cell(row=benchmark_row, column=1).value
        benchmark_name = benchmark_value.strip() if isinstance(benchmark_value, str) else ""
        lowered_category = category.lower()
        lowered_benchmark = benchmark_name.lower()
        matched = bool(lowered_benchmark) and (
            lowered_category in lowered_benchmark or lowered_benchmark in lowered_category
        )
        has_proxy = any("proxy" in text.lower() for text in texts[1:])
        if not matched and not has_proxy:
            failures.append(
                f"Milestone Detail!{row[0].coordinate} prices '{category}' from the benchmark row for "
                f"'{benchmark_name or 'unknown'}' without its own benchmark row or a Basis naming the proxy source"
            )
    return failures


def hours_reconciliation_audit(workbook: Any) -> list[str]:
    """Identical per-category hours across milestones need an hours-basis note."""
    failures: list[str] = []
    if "Milestone Detail" not in workbook.sheetnames:
        return failures
    detail = workbook["Milestone Detail"]
    has_note = any(
        isinstance(cell.value, str) and "hours basis" in cell.value.lower()
        for row in detail.iter_rows()
        for cell in row
    )
    if has_note:
        return failures
    hours_by_category: dict[str, list[float]] = {}
    for row in detail.iter_rows():
        if _row_benchmark_row_number(row) is None:
            continue
        texts = _row_strings(row)
        numbers = [
            float(cell.value)
            for cell in row
            if isinstance(cell.value, (int, float)) and not isinstance(cell.value, bool)
        ]
        if not texts or len(numbers) != 1:
            continue
        hours_by_category.setdefault(texts[0].strip().lower(), []).append(numbers[0])
    for category, hours in sorted(hours_by_category.items()):
        if len(hours) >= 2 and len(set(hours)) == 1:
            failures.append(
                f"Milestone Detail repeats identical hours ({hours[0]:g}) for '{category}' across "
                f"{len(hours)} milestones with no hours-basis note reconciling hours to duration and staffing"
            )
    return failures


def narrative_format_audit(workbook: Any, reported: set[str] | None = None) -> list[str]:
    """Narrative columns must wrap text and meet the width floor.

    Cells reported here are recorded in ``reported`` so the clipping audit does
    not raise a second finding against the same unwrapped narrative cell.
    """
    failures: list[str] = []
    seen: set[tuple[str, str]] = set()
    for sheet_name in ("OT Cost Summary", "Milestone Detail"):
        if sheet_name not in workbook.sheetnames:
            continue
        sheet = workbook[sheet_name]
        for row in sheet.iter_rows():
            for cell in row:
                if not isinstance(cell.value, str):
                    continue
                header = re.sub(r"\s+", " ", cell.value).strip().lower()
                if header not in NARRATIVE_HEADERS:
                    continue
                letter = cell.column_letter
                if (sheet_name, letter) in seen:
                    continue
                seen.add((sheet_name, letter))
                width = sheet.column_dimensions[letter].width
                if width is None or width < MIN_NARRATIVE_WIDTH:
                    failures.append(
                        f"{sheet_name} narrative column {letter} ('{cell.value.strip()}') width "
                        f"{width or 0:g} is below the {MIN_NARRATIVE_WIDTH:g} floor"
                    )
                for row_number in range(cell.row + 1, sheet.max_row + 1):
                    below = sheet.cell(row=row_number, column=cell.column)
                    value = below.value
                    if (
                        isinstance(value, str)
                        and value.strip()
                        and not value.startswith("=")
                        and not below.alignment.wrap_text
                    ):
                        failures.append(
                            f"{sheet_name}!{below.coordinate} narrative cell under "
                            f"'{cell.value.strip()}' does not have wrap text enabled"
                        )
                        if reported is not None:
                            reported.add(f"{sheet_name}!{below.coordinate}")
                        break
    return failures


MAX_PORTRAIT_COLUMNS = 8


def _used_column_count(sheet: Any) -> int:
    widest = 0
    for row in sheet.iter_rows():
        for cell in row:
            if cell.value is not None and cell.column > widest:
                widest = cell.column
    return widest


def print_setup_audit(workbook: Any) -> list[str]:
    """Every canonical sheet must print cleanly: print area, fitToPage, landscape when wide."""
    failures: list[str] = []
    for sheet_name in REQUIRED_SHEETS:
        if sheet_name not in workbook.sheetnames:
            continue
        sheet = workbook[sheet_name]
        if not sheet.print_area:
            failures.append(f"{sheet_name} has no print area set over the populated range")
        page_setup_properties = sheet.sheet_properties.pageSetUpPr
        if page_setup_properties is None or not page_setup_properties.fitToPage:
            failures.append(f"{sheet_name} does not enable fitToPage scaling")
        used_columns = _used_column_count(sheet)
        if used_columns > MAX_PORTRAIT_COLUMNS and sheet.page_setup.orientation != "landscape":
            failures.append(
                f"{sheet_name} uses {used_columns} columns in portrait orientation; sheets wider "
                f"than {MAX_PORTRAIT_COLUMNS} used columns must print landscape"
            )
    return failures


def is_recost_workbook(workbook: Any) -> bool:
    for sheet in workbook.worksheets:
        for row in sheet.iter_rows(min_row=1, max_row=3):
            for cell in row:
                if isinstance(cell.value, str) and "recost" in cell.value.lower():
                    return True
    return False


def _header_map(row: Any) -> dict[str, int]:
    return {
        re.sub(r"\s+", " ", cell.value).strip().lower(): cell.column
        for cell in row
        if isinstance(cell.value, str)
    }


def recost_audit(workbook: Any, payload: dict[str, Any]) -> list[str]:
    """Recost-specific gates: no orphan benchmarks, decomposed labor deltas, register coverage."""
    failures: list[str] = []
    if "Milestone Detail" not in workbook.sheetnames:
        return failures
    detail = workbook["Milestone Detail"]
    detail_text = "\n".join(
        cell.value.lower()
        for row in detail.iter_rows()
        for cell in row
        if isinstance(cell.value, str)
    )

    if "Labor Benchmarking" in workbook.sheetnames:
        benchmarks = workbook["Labor Benchmarking"]
        header_columns: dict[str, int] = {}
        header_row_number = 0
        for row in benchmarks.iter_rows():
            columns = _header_map(row)
            if "role" in columns or "labor category" in columns:
                header_columns = columns
                header_row_number = row[0].row
                break
        role_column = header_columns.get("role") or header_columns.get("labor category")
        if role_column:
            for row_number in range(header_row_number + 1, benchmarks.max_row + 1):
                value = benchmarks.cell(row=row_number, column=role_column).value
                if not isinstance(value, str) or not value.strip():
                    continue
                role = value.strip()
                if role.lower() not in detail_text:
                    failures.append(
                        f"Labor Benchmarking row {row_number} lists role '{role}' that appears in no "
                        "Milestone Detail row; recost benchmarks must cover only roles priced in the package"
                    )

    element_columns: dict[str, int] = {}
    element_header_row = 0
    for row in detail.iter_rows():
        columns = _header_map(row)
        if "cost element" in columns:
            element_columns = columns
            element_header_row = row[0].row
            break
    if element_columns:
        has_hours = "hours" in element_columns
        has_rate = "rate" in element_columns
        for row_number in range(element_header_row + 1, detail.max_row + 1):
            element = detail.cell(row=row_number, column=element_columns["cost element"]).value
            if not isinstance(element, str) or "labor" not in element.lower():
                continue
            hours_value = detail.cell(row=row_number, column=element_columns["hours"]).value if has_hours else None
            rate_value = detail.cell(row=row_number, column=element_columns["rate"]).value if has_rate else None
            if hours_value is None or rate_value is None:
                failures.append(
                    f"Milestone Detail row {row_number} carries labor delta '{element.strip()}' as a lump sum; "
                    "every recost labor delta must decompose as hours x rate per affected category"
                )

    register_elements = payload.get("recost_register_elements", [])
    if not isinstance(register_elements, list) or not all(
        isinstance(item, str) for item in register_elements
    ):
        raise InputError("recost_register_elements must be an array of strings")
    for element in register_elements:
        if element.strip().lower() not in detail_text:
            failures.append(
                f"change register names cost element '{element.strip()}' but the recost carries no "
                "matching delta row; carry a $0 delta with a one-line justification instead of omitting it"
            )
    return failures


CATEGORY_HEADERS = {"labor category", "category", "role"}
HOURS_DRIVERS = (
    ("fte", "FTE loading"),
    ("duration", "weeks or duration"),
    ("hours_per_period", "hours per FTE-week"),
)
RESTATED_HOURS_EXEMPT_SHEETS = {"Milestone Detail", "Labor Benchmarking"}
RESTATED_HOURS_SKIP = ("productive", "annual", "per year", "/year")


def _normalized_header(value: Any) -> str:
    return re.sub(r"\s+", " ", value).strip().lower() if isinstance(value, str) else ""


def _hours_header_columns(row: Any) -> dict[str, int]:
    """Classify a Milestone Detail labor header row into hours and driver columns."""
    columns: dict[str, int] = {}
    for cell in row:
        header = _normalized_header(cell.value)
        if not header:
            continue
        mentions_hours = "hour" in header or "hrs" in header
        if header in CATEGORY_HEADERS:
            columns.setdefault("category", cell.column)
        elif header == "hours":
            columns.setdefault("hours", cell.column)
        elif mentions_hours and ("week" in header or "fte" in header or "day" in header):
            columns.setdefault("hours_per_period", cell.column)
        elif "fte" in header:
            columns.setdefault("fte", cell.column)
        elif "week" in header or "duration" in header or "month" in header:
            columns.setdefault("duration", cell.column)
    return columns


def _labor_block_rows(sheet: Any, header_row: int, category_column: int) -> list[int]:
    rows: list[int] = []
    for row_number in range(header_row + 1, sheet.max_row + 1):
        label = sheet.cell(row=row_number, column=category_column).value
        if not isinstance(label, str) or not label.strip():
            break
        if "subtotal" in label.lower() or "total" in label.lower():
            break
        rows.append(row_number)
    return rows


def _reconciliation_regions(header_rows: list[int], max_row: int) -> list[tuple[int, int]]:
    regions: list[tuple[int, int]] = []
    for index, header_row in enumerate(header_rows):
        start = 1 if index == 0 else (header_rows[index - 1] + header_row) // 2
        end = max_row if index == len(header_rows) - 1 else (header_row + header_rows[index + 1]) // 2
        regions.append((start, end))
    return regions


def _restated_hours_failures(workbook: Any) -> list[str]:
    failures: list[str] = []
    for sheet_name in REQUIRED_SHEETS:
        if sheet_name in RESTATED_HOURS_EXEMPT_SHEETS or sheet_name not in workbook.sheetnames:
            continue
        sheet = workbook[sheet_name]
        for row in sheet.iter_rows():
            for cell in row:
                header = _normalized_header(cell.value)
                if "hour" not in header or any(token in header for token in RESTATED_HOURS_SKIP):
                    continue
                seen_value = False
                for row_number in range(cell.row + 1, sheet.max_row + 1):
                    below = sheet.cell(row=row_number, column=cell.column)
                    value = below.value
                    if value is None:
                        if seen_value:
                            break
                        continue
                    seen_value = True
                    if isinstance(value, bool) or not isinstance(value, (int, float)):
                        continue
                    failures.append(
                        f"{sheet_name}!{below.coordinate} restates milestone hours as the constant "
                        f"{float(value):g} under '{str(cell.value).strip()}'; a sheet that repeats Milestone "
                        "Detail hours must reference those cells by formula"
                    )
                    break
    return failures


def hours_derivation_audit(workbook: Any) -> list[str]:
    """Milestone labor hours must be derived from driver input cells, not asserted."""
    failures: list[str] = []
    if "Milestone Detail" not in workbook.sheetnames:
        return failures
    detail = workbook["Milestone Detail"]

    blocks: list[tuple[int, dict[str, int]]] = []
    for row in detail.iter_rows():
        columns = _hours_header_columns(row)
        if "hours" in columns and "category" in columns:
            blocks.append((row[0].row, columns))
    if not blocks:
        return failures

    regions = _reconciliation_regions([header_row for header_row, _ in blocks], detail.max_row)
    for (header_row, columns), (region_start, region_end) in zip(blocks, regions):
        data_rows = _labor_block_rows(detail, header_row, columns["category"])
        missing_drivers = [
            label for key, label in HOURS_DRIVERS if key not in columns
        ]
        if missing_drivers and data_rows:
            failures.append(
                f"Milestone Detail labor block at row {header_row} exposes no "
                f"{', '.join(missing_drivers)} input column; every priced labor row must carry its hours "
                "drivers as input cells"
            )

        asserted: list[int] = []
        for row_number in data_rows:
            hours_cell = detail.cell(row=row_number, column=columns["hours"])
            value = hours_cell.value
            if value is None:
                continue
            if not (isinstance(value, str) and value.startswith("=")):
                asserted.append(row_number)
                continue
            normalized = normalize_formula(value)
            for key, label in HOURS_DRIVERS:
                if key not in columns:
                    continue
                driver = detail.cell(row=row_number, column=columns[key])
                if isinstance(driver.value, bool) or not isinstance(driver.value, (int, float)):
                    failures.append(
                        f"Milestone Detail!{driver.coordinate} {label} driver is not a numeric input cell; "
                        "the hours formula has nothing to recompute from"
                    )
                elif normalize_formula(driver.coordinate) not in normalized:
                    failures.append(
                        f"Milestone Detail!{hours_cell.coordinate} hours formula does not reference its "
                        f"{label} driver cell {driver.coordinate}"
                    )
        if asserted:
            first = detail.cell(row=asserted[0], column=columns["hours"])
            category = detail.cell(row=asserted[0], column=columns["category"]).value
            failures.append(
                f"Milestone Detail!{first.coordinate} asserts hours for "
                f"'{str(category).strip()}' as the constant {first.value} "
                f"({len(asserted)} of {len(data_rows)} labor rows in this block); milestone labor hours must be "
                "a formula over the FTE loading, duration, and hours-per-FTE-week input cells"
            )

        has_reconciliation = False
        has_prose_note = False
        for row in detail.iter_rows(min_row=region_start, max_row=region_end):
            for cell in row:
                value = cell.value
                if not isinstance(value, str):
                    continue
                if value.startswith("="):
                    upper = value.upper()
                    if "MISMATCH" in upper and "OK" in upper:
                        has_reconciliation = True
                elif "hours basis" in value.lower():
                    has_prose_note = True
        if not has_reconciliation:
            prose = " The 'Hours basis:' note is prose, not a check." if has_prose_note else ""
            failures.append(
                f"Milestone Detail rows {region_start}-{region_end} carry no formula reconciling derived "
                "hours to milestone duration and staffing; the reconciliation must be a formula rendering an "
                f"OK or MISMATCH state.{prose}"
            )

    failures.extend(_restated_hours_failures(workbook))
    return failures


# --- Rendered-text clipping audit -------------------------------------------
# A text cell overflows into the next cell only when that neighbour is empty.
# When the neighbour is occupied the label is cut off in the printed workbook,
# so every such label must fit its column, wrap, or be merged across the block.

CLIPPING_GLYPH_WIDTHS = {
    " ": 0.45,
    ".": 0.45,
    ",": 0.45,
    ";": 0.45,
    ":": 0.45,
    "'": 0.35,
    "`": 0.45,
    "!": 0.45,
    "|": 0.45,
    "(": 0.55,
    ")": 0.55,
    "[": 0.55,
    "]": 0.55,
    "{": 0.60,
    "}": 0.60,
    "-": 0.60,
    "/": 0.55,
    "\\": 0.55,
    '"': 0.60,
    "%": 1.50,
    "@": 1.70,
    "$": 1.00,
}
CLIPPING_LOWER_NARROW = "ijl"
CLIPPING_LOWER_SEMI = "frt"
CLIPPING_LOWER_WIDE = "mw"
CLIPPING_UPPER_NARROW = "I"
CLIPPING_UPPER_WIDE = "MW"
CLIPPING_BOLD_FACTOR = 1.14
CLIPPING_ABSOLUTE_TOLERANCE = 0.75
CLIPPING_RELATIVE_TOLERANCE = 0.04
CLIPPING_DEFAULT_WIDTH = 8.43
CLIPPING_MESSAGE_TEXT_LIMIT = 120


def glyph_width(character: str) -> float:
    """Width of one glyph in Excel column-width units (1.0 = one digit)."""
    if character in CLIPPING_GLYPH_WIDTHS:
        return CLIPPING_GLYPH_WIDTHS[character]
    if character in CLIPPING_LOWER_NARROW:
        return 0.48
    if character in CLIPPING_LOWER_SEMI:
        return 0.59
    if character in CLIPPING_LOWER_WIDE:
        return 1.66
    if character in CLIPPING_UPPER_NARROW:
        return 0.45
    if character in CLIPPING_UPPER_WIDE:
        return 1.55
    if character.islower():
        return 0.96
    if character.isupper():
        return 1.05
    return 1.0


def estimated_text_width(text: str, font: Any = None) -> float:
    """Estimated rendered width of a label in column-width units."""
    lines = str(text).split("\n")
    units = max((sum(glyph_width(character) for character in line) for line in lines), default=0.0)
    size = getattr(font, "size", None) or 11.0
    if float(size) != 11.0:
        units *= float(size) / 11.0
    if getattr(font, "bold", False):
        units *= CLIPPING_BOLD_FACTOR
    return units


def column_width_map(sheet: Any) -> tuple[dict[int, float], float]:
    """Explicit column widths by index plus the sheet default width."""
    widths: dict[int, float] = {}
    for letter, dimension in sheet.column_dimensions.items():
        if dimension.width is None:
            continue
        # In-memory dimensions created by a generator carry no min/max, so fall
        # back to the column the dimension is keyed under.
        try:
            own = column_index_from_string(letter)
        except ValueError:
            own = None
        first = dimension.min or own or 1
        last = dimension.max or own or first
        for index in range(first, last + 1):
            widths[index] = float(dimension.width)
    default = sheet.sheet_format.defaultColWidth or CLIPPING_DEFAULT_WIDTH
    return widths, float(default)


def merged_ranges_by_anchor(sheet: Any) -> tuple[dict[tuple[int, int], Any], set[tuple[int, int]]]:
    """Merged-range lookup keyed by anchor cell, plus every covered cell."""
    anchors: dict[tuple[int, int], Any] = {}
    covered: set[tuple[int, int]] = set()
    for merged in sheet.merged_cells.ranges:
        anchors[(merged.min_row, merged.min_col)] = merged
        for row in range(merged.min_row, merged.max_row + 1):
            for column in range(merged.min_col, merged.max_col + 1):
                covered.add((row, column))
    return anchors, covered


def _is_blank(value: Any) -> bool:
    if value is None:
        return True
    return isinstance(value, str) and not value.strip()


def text_clipping_audit(
    workbook: Any,
    *,
    sheets: list[str] | None = None,
    exempt: set[str] | None = None,
) -> list[str]:
    """Flag text that is cut off in print because an occupied neighbour blocks overflow."""
    failures: list[str] = []
    skipped = exempt or set()
    for sheet in workbook.worksheets:
        if sheets is not None and sheet.title not in sheets:
            continue
        widths, default_width = column_width_map(sheet)
        anchors, covered = merged_ranges_by_anchor(sheet)
        max_column = sheet.max_column
        for row in sheet.iter_rows():
            for cell in row:
                value = cell.value
                if not isinstance(value, str) or not value.strip() or value.startswith("="):
                    continue
                position = (cell.row, cell.column)
                if position in covered and position not in anchors:
                    continue
                alignment = cell.alignment
                if alignment.wrap_text or alignment.horizontal in {"fill", "distributed"}:
                    continue
                if f"{sheet.title}!{cell.coordinate}" in skipped:
                    continue
                merged = anchors.get(position)
                first_column = cell.column
                last_column = merged.max_col if merged is not None else cell.column
                available = sum(
                    widths.get(index, default_width)
                    for index in range(first_column, last_column + 1)
                )
                needed = estimated_text_width(value, cell.font)
                tolerance = max(
                    CLIPPING_ABSOLUTE_TOLERANCE,
                    CLIPPING_RELATIVE_TOLERANCE * available,
                )
                if needed <= available + tolerance:
                    continue
                blockers = []
                if alignment.horizontal in {"right", "center", "centerContinuous"}:
                    left = first_column - 1
                    if left < 1:
                        blockers.append("the left sheet edge")
                    elif not _is_blank(sheet.cell(row=cell.row, column=left).value):
                        blockers.append(sheet.cell(row=cell.row, column=left).coordinate)
                if alignment.horizontal != "right":
                    right = last_column + 1
                    if right <= max_column and not _is_blank(
                        sheet.cell(row=cell.row, column=right).value
                    ):
                        blockers.append(sheet.cell(row=cell.row, column=right).coordinate)
                if not blockers:
                    continue
                shown = value.strip()
                if len(shown) > CLIPPING_MESSAGE_TEXT_LIMIT:
                    shown = shown[: CLIPPING_MESSAGE_TEXT_LIMIT - 3] + "..."
                span = (
                    cell.column_letter
                    if merged is None
                    else f"{cell.column_letter}:{get_column_letter(last_column)}"
                )
                failures.append(
                    f"{sheet.title}!{cell.coordinate} is clipped in print: '{shown}' needs about "
                    f"{needed:.1f} column-width units but column {span} gives {available:g} and "
                    f"{', '.join(blockers)} blocks the overflow; widen the column to at least "
                    f"{math.ceil(needed):g}, enable wrap text with adequate row height, merge the "
                    f"label across the block, or shorten it"
                )
    return failures


def structural_audit(workbook: Any, payload: dict[str, Any]) -> list[str]:
    failures: list[str] = []
    for sheet_name in payload.get("required_sheets", REQUIRED_SHEETS):
        if sheet_name not in workbook.sheetnames:
            failures.append(f"missing required sheet: {sheet_name}")

    if "OT Cost Summary" in workbook.sheetnames:
        check_formula(
            failures,
            workbook,
            "'OT Cost Summary'!B8",
            contains=["VALUE(LEFT(B7,4))", "VALUE(LEFT(B6,4))", "VALUE(MID(B7,6,2))", "VALUE(MID(B6,6,2))"],
            not_contains=["DATEDIF", "YEAR("],
        )
        check_formula(
            failures,
            workbook,
            "'OT Cost Summary'!B9",
            contains=["B3", "B8", "^"],
        )
        summary = workbook["OT Cost Summary"]
        if summary["B9"].number_format != "0.0000":
            failures.append("OT Cost Summary!B9 must display the aging factor as 0.0000")

    formula_count = 0
    all_text: list[str] = []
    error_tokens = ("#REF!", "#NAME?", "#VALUE!", "#DIV/0!")
    for sheet in workbook.worksheets:
        for row in sheet.iter_rows():
            for cell in row:
                value = cell.value
                if isinstance(value, str):
                    all_text.append(value)
                    if value.startswith("="):
                        formula_count += 1
                        if any(token in value.upper() for token in error_tokens):
                            failures.append(f"{sheet.title}!{cell.coordinate} contains a formula error token")
                    elif value[:1] in {"+", "-", "@"}:
                        failures.append(f"{sheet.title}!{cell.coordinate} starts with a formula-trigger character")
    if formula_count == 0:
        failures.append("workbook contains no formulas")

    joined = "\n".join(all_text)
    if re.search(r"4022\s*\(d\)\s*\(1\)\s*\(D\).{0,80}competition commitment", joined, re.I | re.S):
        failures.append("workbook misstates 4022(d)(1)(D) as competition commitment")
    if re.search(r"4021.{0,100}(?:100%|fully)\s+Government funded", joined, re.I | re.S):
        failures.append("workbook automatically states 4021 is fully Government funded")
    if re.search(r"4022\s*\(f\).{0,100}(?:100%|fully)\s+Government funded", joined, re.I | re.S):
        failures.append("workbook automatically states 4022(f) is fully Government funded")

    failures.extend(labor_benchmark_audit(workbook))
    failures.extend(hours_reconciliation_audit(workbook))
    failures.extend(hours_derivation_audit(workbook))
    narrative_reported: set[str] = set()
    failures.extend(narrative_format_audit(workbook, narrative_reported))
    failures.extend(text_clipping_audit(workbook, exempt=narrative_reported))
    failures.extend(print_setup_audit(workbook))
    if is_recost_workbook(workbook):
        failures.extend(recost_audit(workbook, payload))

    assertions = payload.get("formula_assertions", [])
    if not isinstance(assertions, list):
        raise InputError("formula_assertions must be an array")
    for index, assertion in enumerate(assertions):
        if not isinstance(assertion, dict) or not isinstance(assertion.get("cell"), str):
            raise InputError(f"formula_assertions[{index}] must contain a cell string")
        contains = assertion.get("contains", [])
        not_contains = assertion.get("not_contains", [])
        if not isinstance(contains, list) or not all(isinstance(item, str) for item in contains):
            raise InputError(f"formula_assertions[{index}].contains must be strings")
        if not isinstance(not_contains, list) or not all(isinstance(item, str) for item in not_contains):
            raise InputError(f"formula_assertions[{index}].not_contains must be strings")
        expected = assertion.get("equals")
        if expected is not None and not isinstance(expected, str):
            raise InputError(f"formula_assertions[{index}].equals must be a string")
        check_formula(
            failures,
            workbook,
            assertion["cell"],
            expected=expected,
            contains=contains,
            not_contains=not_contains,
        )
    return failures


def find_soffice() -> Path | None:
    command = shutil.which("soffice")
    if command:
        return Path(command)
    mac = Path("/Applications/LibreOffice.app/Contents/MacOS/soffice")
    return mac if mac.is_file() else None


def recalculate(source: Path, executable: Path) -> tuple[tempfile.TemporaryDirectory[str], Path]:
    temporary = tempfile.TemporaryDirectory(prefix="ot-workbook-validation-")
    root = Path(temporary.name)
    input_dir = root / "input"
    output_dir = root / "output"
    input_dir.mkdir()
    output_dir.mkdir()
    copied = input_dir / source.name
    shutil.copy2(source, copied)
    completed = subprocess.run(
        [str(executable), "--headless", "--convert-to", "xlsx", "--outdir", str(output_dir), str(copied)],
        capture_output=True,
        text=True,
        timeout=120,
        check=False,
    )
    result = output_dir / source.name
    if completed.returncode != 0 or not result.is_file():
        temporary.cleanup()
        detail = (completed.stderr or completed.stdout).strip()
        raise InputError(f"LibreOffice calculation failed: {detail or 'no output file'}")
    return temporary, result


def close_enough(actual: float, expected: float, tolerance: float) -> bool:
    return math.isclose(actual, expected, rel_tol=tolerance, abs_tol=max(0.01, tolerance))


def compare(workbook: Any, expected: dict[str, Any], tolerance: float) -> list[str]:
    failures: list[str] = []

    def one(reference: str, target: float, label: str) -> None:
        try:
            value = value_at(workbook, reference)
        except InputError as exc:
            failures.append(str(exc))
            return
        if isinstance(value, bool) or not isinstance(value, (int, float)):
            failures.append(f"{label} at {reference} has no calculated numeric value")
        elif not close_enough(float(value), target, tolerance):
            failures.append(f"{label} at {reference} is {float(value):.6f}, expected {target:.6f}")

    for milestone in expected["milestones"]:
        mapping = {
            "workbook_project_cost_cell": ("project_cost", "project cost"),
            "workbook_government_funding_cell": ("government_funding", "Government funding"),
            "workbook_ceiling_cell": ("ceiling_basis", "ceiling basis"),
            "workbook_performer_share_cell": ("performer_project_share", "performer share"),
        }
        for reference_key, (value_key, label) in mapping.items():
            if reference_key in milestone:
                one(milestone[reference_key], milestone[value_key], f"{milestone['id']} {label}")
    if "workbook_total_project_cost_cell" in expected:
        one(expected["workbook_total_project_cost_cell"], expected["total_project_cost"], "total project cost")
    if "workbook_total_government_funding_cell" in expected:
        one(
            expected["workbook_total_government_funding_cell"],
            expected["total_government_funding"],
            "total Government funding",
        )
    return failures


def cached_error_audit(workbook: Any) -> list[str]:
    failures: list[str] = []
    for sheet in workbook.worksheets:
        for row in sheet.iter_rows():
            for cell in row:
                value = cell.value
                if isinstance(value, str) and value.startswith("#"):
                    failures.append(f"{sheet.title}!{cell.coordinate} has cached error {value}")
    return failures


def run(path: Path, expected_path: Path, engine: str, tolerance: float) -> dict[str, Any]:
    if not zipfile.is_zipfile(path):
        return {"status": "fail", "failures": ["file is not a valid XLSX ZIP"]}
    payload = load_payload(expected_path)
    expected = calculate(payload)
    workbook = load_workbook(path, data_only=False)
    failures = structural_audit(workbook, payload)
    engine_used = "none"
    temporary: tempfile.TemporaryDirectory[str] | None = None
    try:
        executable = find_soffice() if engine in {"auto", "libreoffice"} else None
        if engine == "libreoffice" and executable is None:
            failures.append("LibreOffice was required but no executable was found")
        elif executable is not None:
            try:
                temporary, calculated_path = recalculate(path, executable)
                calculated = load_workbook(calculated_path, data_only=True)
                failures.extend(cached_error_audit(calculated))
                failures.extend(compare(calculated, expected, tolerance))
                engine_used = "libreoffice"
            except InputError as exc:
                failures.append(str(exc))
    finally:
        if temporary is not None:
            temporary.cleanup()
    return {
        "status": "pass" if not failures else "fail",
        "engine": engine_used,
        "formula_count": sum(
            1
            for sheet in workbook.worksheets
            for row in sheet.iter_rows()
            for cell in row
            if isinstance(cell.value, str) and cell.value.startswith("=")
        ),
        "failures": failures,
    }


def main() -> int:
    parser = argparse.ArgumentParser(description="Validate an OT Cost Analysis workbook.")
    parser.add_argument("workbook", type=Path)
    parser.add_argument("--expected", required=True, type=Path)
    parser.add_argument("--engine", choices=("none", "auto", "libreoffice"), default="auto")
    parser.add_argument("--tolerance", type=float, default=0.01)
    parser.add_argument("--json", action="store_true")
    args = parser.parse_args()
    if not args.workbook.is_file() or not args.expected.is_file():
        print("ERROR: workbook or expected-input file not found", file=sys.stderr)
        return 2
    try:
        result = run(args.workbook, args.expected, args.engine, args.tolerance)
    except (InputError, OSError, ValueError, zipfile.BadZipFile) as exc:
        print(f"ERROR: {exc}", file=sys.stderr)
        return 2
    if args.json:
        print(json.dumps(result, indent=2, sort_keys=True))
    elif result["status"] == "pass":
        print(f"OT workbook validation passed; formula engine: {result['engine']}.")
    else:
        print("VALIDATION FAILED")
        for failure in result["failures"]:
            print(f"- {failure}")
    return 0 if result["status"] == "pass" else 1


if __name__ == "__main__":
    raise SystemExit(main())