interface:
  display_name: "IGCE Builder: CR"
  short_description: "Build auditable cost-reimbursement estimates"
  default_prompt: "Use $igce-builder-cr to build an auditable CPFF, CPAF, or CPIF IGCE from my staffing inputs or requirement."

policy:
  allow_implicit_invocation: true

# CR Data Source Operations and Mapping Reference

Use this reference for SOC selection, BLS wage retrieval, CALC+ positioning, and GSA Per Diem. Keep operation names stable and let the host supply any runtime namespace.

## Contents

1. Constants and runtime checks
2. SOC mapping
3. BLS OEWS operations
4. CALC+ operations
5. GSA Per Diem operations
6. Raw-data recording

## 1. Constants and runtime checks

| Item | Current baseline | Required handling |
|---|---:|---|
| Standard work year | 2,080 hours | Convert annual wage to hourly direct labor |
| Default productive hours | 1,880 hours | Editable workbook assumption; derive shift-coverage FTE from this value |
| BLS wage cap | $239,200 annual or $115 hourly | Reconfirm each release and treat capped values as lower bounds |
| BLS vintage | May 2025 | Call `detect_latest_year` before every estimate |
| First and last day M&IE | 75% | Use the MCP's discounted result once |
| City Pair fare | YCA when available | Use only when origin and destination are known |

Treat published constants as baselines. Runtime data controls the estimate.

### 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 any longer server-provided retry interval. If a service reports a rate limit, stop and report it instead of issuing rapid retries. Unkeyed services must still follow their advertised limits and must not be queried in uncontrolled loops.

## 2. SOC mapping

Classify the requirement domain before mapping job titles.

### IT and software

| Common title | SOC | BLS title |
|---|---|---|
| IT Program Manager | 11-3021 | Computer and Information Systems Managers |
| Management Analyst | 13-1111 | Management Analysts |
| Project Manager | 13-1082 | Project Management Specialists |
| Systems Engineer or Analyst, IT | 15-1211 | Computer Systems Analysts |
| Cybersecurity or InfoSec Analyst | 15-1212 | Information Security Analysts |
| Network Architect | 15-1241 | Computer Network Architects |
| Database Administrator | 15-1242 | Database Administrators |
| Systems Administrator | 15-1244 | Network and Computer Systems Administrators |
| Software Developer | 15-1252 | Software Developers |
| QA Tester | 15-1253 | Software QA Analysts and Testers |
| Help Desk or User Support | 15-1232 | Computer User Support Specialists |
| Data Scientist | 15-2051 | Data Scientists |

### Physical and non-IT engineering

Use these codes for physical systems, reactors, aerospace, infrastructure, chemical, mechanical, and hardware integration.

| Common title | SOC | BLS title |
|---|---|---|
| Aerospace Engineer | 17-2011 | Aerospace Engineers |
| Biomedical Engineer | 17-2031 | Bioengineers and Biomedical Engineers |
| Chemical Engineer | 17-2041 | Chemical Engineers |
| Civil Engineer | 17-2051 | Civil Engineers |
| Electrical Engineer | 17-2071 | Electrical Engineers |
| Electronics Engineer | 17-2072 | Electronics Engineers, Except Computer |
| Environmental Engineer | 17-2081 | Environmental Engineers |
| Industrial Engineer | 17-2112 | Industrial Engineers |
| Mechanical Engineer | 17-2141 | Mechanical Engineers |
| Nuclear Engineer | 17-2161 | Nuclear Engineers |
| Petroleum Engineer | 17-2171 | Petroleum Engineers |
| Systems Engineer, non-IT | 17-2199 | Engineers, All Other |

Use 17-2199 when an integration role spans engineering disciplines. Query a specific engineering SOC beside it when useful.

### Science, research, medical, and support

| Common title | SOC | BLS title |
|---|---|---|
| Physicist | 19-2012 | Physicists |
| Chemist | 19-2031 | Chemists |
| Medical Scientist | 19-1042 | Medical Scientists, Except Epidemiologists |
| Registered Nurse | 29-1141 | Registered Nurses |
| Physician Assistant | 29-1071 | Physician Assistants |
| Technical Writer | 27-3042 | Technical Writers |
| Contracting Specialist | 13-1020 | Buyers and Purchasing Agents |

### Program management context

Use the contract domain to map Program Manager:

- Operations or administration: 11-1021, General and Operations Managers
- Physical engineering or R&D: 11-9041, Architectural and Engineering Managers
- IT or software: 11-3021, Computer and Information Systems Managers

Document the choice. Do not default every technical Program Manager to 11-3021.

## 3. BLS OEWS operations

### Runtime vintage

Call `detect_latest_year` before wage retrieval. Record the returned year and use it in Raw Data, the Summary assumption block, and the aging calculation. If it differs from May 2025, use the runtime result.

### Wage retrieval

Call:

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

The requested datatypes provide mean plus P10, P25, P50, P75, and P90. Valid operation datatypes include `01`, `03`, `04`, `08`, `11`, `12`, `13`, `14`, and `15`. Do not send `02` or `05`.

Use `list_common_metros` to resolve common MSAs and renumbering notes. If a metro is absent, resolve its current code from BLS before falling back.

Fallback ladder:

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

Record each fallback and its reason. Do not treat suppressed or unavailable metro data as evidence about market quality.

For a single-category sanity check, `igce_wage_benchmark` may return compact wage and burden information. Use `get_wage_data` for the full workbook so the percentile basis remains visible.

### Seniority convention

When BLS does not distinguish job levels:

- Junior or entry: P25
- Mid or journeyman: P50
- Senior: P50 to P75 based on scope
- Principal, director, or SME: P75 to P90

If no levels are supplied, price all members at P50 and disclose that convention. Do not invent a junior/mid/senior staffing mix.

### Capped and compressed distributions

If a selected percentile hits the BLS cap, treat it as a lower bound. If a selected value is within 10% of the current cap, note that the local market may exceed the reported value and flag the proximity for Contracting Officer review. If P75 is capped:

1. Use the uncapped mean as a senior anchor when appropriate.
2. Cross-reference a separate commercial source if available.
3. Consider national P75 divided by national P50, applied to local P50, and label it as a derivation.
4. Never present the cap as an exact point estimate.

If P75 equals P90 below the cap, disclose a flat upper tail. If P25 equals P10, do not use P25 for a junior anchor without review. A possible derived junior value is national P25 multiplied by local P50 divided by national P50. Label every derived value.

## 4. CALC+ operations

### Silent-wrong-answer signature

The CALC+ keyword route is:

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

The parameter is `keyword=`. Never use `q=`. The wrong parameter can be accepted while returning the unfiltered corpus.

The labor-category discovery response path is:

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

Record enough of the selected buckets and counts to reproduce the pool.

### Discovery-first flow

1. Call `suggest_contains(field="labor_category", term=<LCAT term>)`.
2. If the top exact buckets form an adequate pool, call `exact_search(field="labor_category", value=<bucket>)` for each selected bucket.
3. If buckets are fragmented, call `keyword_search(keyword=<term>)` and document that keyword matching can include other fields.
4. Use `igce_benchmark(labor_category=<title>, experience_min=<years>, education_level=<level>)` for compact percentile statistics.
5. Do not send `page_size=0`. Use at least 1 when an operation requires the parameter.

Expected benchmark fields include count, minimum, maximum, mean, standard deviation, P10, P25, P50, P75, and P90. Use the returned canonical fields instead of probing large raw payloads.

### Tier matching and dual pools

Match the actual level. For a mid-level developer, prefer `Software Developer II` over an all-level Software Developer pool.

For senior categories, present both:

- Title-match pool, discovered through `suggest_contains` and `exact_search`
- Experience-match pool, returned by `igce_benchmark` with an appropriate minimum experience filter

Report both sample sizes and medians. Do not merge them without disclosure.

Useful fragmented-title alternatives:

- SOC Analyst or Cyber Analyst: Information Security Analyst I, II, and III
- Software Engineer: Software Developer I, II, and III
- Data Engineer with a thin pool: Data Scientist with experience filter, clearly labeled as a proxy

### Workflow B shortcut

Call:

```text
price_reasonableness_check(
  labor_category=<title>,
  proposed_rate=<rate>,
  education_level=<level if known>,
  experience_min=<years if known>
)
```

Use the returned count, bounds, z-score, and percentile only as positioning data. Label a pool under about 25 records as directional.

### CR positioning bands

- Within 10% of P50: expected comparison range
- Between 10% and 25% from P50: identify cost-pool, fee, seniority, geography, and pool-composition arithmetic
- More than 25% from P50: show the complete stacked-factor bridge and direct the result to Contracting Officer review
- Below P25: report the position and ask the Contracting Officer to review the input or pool alignment

CALC+ represents awarded MAS ceiling rates with profit embedded. A CR comparison must show both estimated cost and cost plus fee, and must label the structural difference. Do not force a CR rate into an FFP premium band.

Do not translate a band into a fair-and-reasonable conclusion.

## 5. GSA Per Diem operations

### Travel estimate

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>
)
```

Expected fields include nightly lodging, lodging total, daily M&IE, first/last-day M&IE, M&IE total, grand total, and travel days.

Calculate annual travel as:

```text
grand total per trip * trips per year * travelers
```

Use the operation's first/last-day amount once. `get_mie_breakdown` already returns a 75% value. Do not multiply it by 0.75 again.

`estimate_travel_cost` requires at least one night. For a 0-night day trip, do not pass zero to that operation. Call `lookup_city_perdiem` for the locality and fiscal year, use `mie_first_last_day` once, and set lodging to zero. Record one travel day in the workbook.

### Installation to locality crosswalk

GSA uses civilian localities. Translate installations before lookup.

| Installation or site | GSA locality |
|---|---|
| Fort Meade, MD | Annapolis or Anne Arundel County, MD |
| Fort Belvoir, VA | Fairfax or Alexandria, VA |
| Pentagon, VA | Arlington, VA or DC rate |
| Joint Base Andrews, MD | District of Columbia |
| NSA Bethesda or Walter Reed, MD | District of Columbia composite locality |
| Fort Liberty, NC | Fayetteville, NC |
| Peterson SFB, Schriever SFB, or Fort Carson, CO | Colorado Springs, CO |
| Wright-Patterson AFB, OH | Dayton, OH |
| Eglin AFB, FL | Fort Walton Beach, FL |
| JB San Antonio, Randolph, or Lackland, TX | San Antonio, TX |
| Hanscom AFB, MA | Bedford or Boston, MA |
| Redstone Arsenal, AL | Huntsville, AL |
| Offutt AFB, NE | Omaha or Bellevue, NE |
| Cape Canaveral or Patrick SFB, FL | Cocoa Beach or Cape Canaveral, FL |
| Joint Base Lewis-McChord, WA | Tacoma or Pierce County, WA |
| Oak Ridge National Laboratory or Y-12, TN | Knoxville, TN |
| Los Alamos National Laboratory, NM | Santa Fe or Los Alamos County, NM |
| Hanford or PNNL, WA | Richland, WA |
| Sandia National Laboratories, NM | Albuquerque, NM |
| Lawrence Livermore National Laboratory, CA | Livermore or Oakland, CA |
| Idaho National Laboratory, ID | Idaho Falls, ID |
| White Sands Missile Range, NM | Las Cruces, NM |
| NAWS China Lake, CA | Ridgecrest or Kern County, CA |
| Edwards AFB, CA | Lancaster or Palmdale, CA |
| Dugway Proving Ground, UT | Salt Lake City metro standard rate |
| Nellis AFB or Creech AFB, NV | Las Vegas, NV |
| Point Mugu or NBVC, CA | Oxnard or Ventura County, CA |

For another site, call `lookup_city_perdiem` with the nearest civilian locality and verify the returned county.

### Fiscal-year fallback

If the requested fiscal year returns an empty rate list or an error containing `No rates found for FY`, retry the prior fiscal year. Record the requested and fallback years in Methodology. If contract start is within six months of a new fiscal year, query the new year when published or disclose that the current-year baseline must be refreshed.

### Travel interpretation

"Quarterly travel between sites" means four total trips split across destinations unless the user says each way or per destination. Ask when the interpretation materially changes cost.

If origin and destination are in the same metro or within ordinary local-travel distance, flag that lodging per diem may not apply. Use mileage or another user-supplied local-travel basis instead.

## 6. Raw-data recording

Record compact, reproducible inputs and outputs on the Raw Data sheet:

- BLS operation, SOC, scope, area code, datatypes, returned vintage, and selected percentiles
- CALC+ operation, exact buckets or keyword, count, percentiles, and contamination caveat if applicable
- Per Diem locality, state, fiscal year, month, nights, returned lodging, and returned M&IE
- Every fallback, proxy, or derived value

Do not paste full JSON payloads. Record the parameters and summary fields needed to reproduce each call.

# 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.

# Runtime Adaptation

Use host capabilities instead of hardcoded client names.

## Questions

If the host exposes a structured question tool, use it for short mutually exclusive choices. Otherwise present a numbered list in chat and accept a number, label, or free-text answer. Do not refer to a tool name in user-facing prose.

For Stage A, ask only for decomposition confirmation and stop. For Stage B, batch independent missing fields into one response. Ask one field at a time only when an answer changes the next available choices.

## MCP operations

Inspect the available operations and match their server, operation name, description, and schema. Host prefixes and separators are implementation details. For example, look for the `detect_latest_year` operation from the BLS OEWS server rather than a literal generated namespace.

Declared MCP dependencies do not prove that a server is installed, authenticated, or reachable. Keep the pre-flight check.

## Files and Python

Use the host's file-reading, file-writing, and Python capabilities. Follow any authoritative host spreadsheet workflow and its hard stops. Use Python and openpyxl for workbook authoring only when no governing host spreadsheet workflow prohibits that substitute; never guess dependency paths or bypass a host hard stop. Resolve referenced files relative to this skill directory when possible. If the host does not expose the skill directory, report which reference or script cannot be loaded and stop before a step that depends on it.

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 verification

openpyxl writes formulas and reads cached values; it does not calculate Excel formulas. Validation has three layers:

1. Formula-structure audit with openpyxl.
2. Independent recomputation from raw inputs in Python.
3. Formula execution in a real spreadsheet engine when available.

On macOS and Linux, detect `soffice` and use LibreOffice headless for layer 3. Do not automate desktop Excel on macOS. When no engine is available, use the exact disclosure in Step 8.5.

## Delivery

Use the host's artifact presentation capability when available. Otherwise save to the user-supplied directory or current working directory and report the absolute path. Do not assume `/mnt`, `present_files`, shell launch commands, or a particular desktop application exists.

Never claim that saving a workbook caused formulas to execute. Never call a workbook validated unless the claimed validation layers actually ran.

# CR Workbook Validation Gates

Run all applicable gates after workbook creation and before delivery.

## Layer 1: formula-structure audit

The bundled validator checks:

- All seven required sheets exist.
- `IGCE Summary!B11` computes the month gap from `YYYY-MM` text using `VALUE`, `LEFT`, and `MID`, without `DATEDIF` or `YEAR`.
- `IGCE Summary!B12` computes the aging factor from escalation and month gap.
- Every Cost Buildup block follows the 23-row layout.
- Direct labor is the fifth row of each block and cross-sheet formulas do not use the aged-wage row by mistake.
- Fringe, overhead, G&A, FCCM, fee, and price formulas preserve the required bases.
- The workbook contains formulas and no formula-error tokens in formula text.
- Text beginning with formula-trigger characters is escaped.
- Any summary total labeled as a price equals estimated cost plus fee; a price-labeled total that equals the cost subtotal fails.
- The current-assumptions Scenario Analysis row reproduces the summary estimated price within one dollar, proving the scenario sheet uses the same fee-base rule.
- For a multi-period requirement (`assumptions.periods` greater than 1, reconciled with `IGCE Summary!B19`), the summary shows a per-period breakdown of cost, fee, and price for the base and each option period.
- Optional custom formula assertions in the JSON sidecar pass.

## Layer 2: independent recomputation

Create a JSON sidecar from the raw inputs, not from workbook formula results. Minimum shape:

```json
{
  "assumptions": {
    "fee_type": "CPFF",
    "primary_fee_rate": 0.08,
    "fringe_rate": 0.32,
    "overhead_rate": 0.80,
    "ga_rate": 0.12,
    "fccm_rate": 0.0,
    "aging_factor": 1.025,
    "productive_hours": 1880,
    "periods": 3
  },
  "labor_lines": [
    {
      "name": "Research Scientist",
      "annual_wage": 140000,
      "fte": 3,
      "months": 12,
      "workbook_cost_rate_cell": "'Cost Buildup'!B17",
      "workbook_price_rate_cell": "'Cost Buildup'!B21"
    }
  ],
  "non_labor_lines": [
    {
      "name": "Travel",
      "amount": 12000,
      "fee_bearing": false,
      "workbook_total_cell": "'Travel Detail'!B14"
    }
  ],
  "workbook_fee_bearing_cost_cell": "'IGCE Summary'!B28",
  "workbook_total_cost_cell": "'IGCE Summary'!B29",
  "workbook_total_fee_cell": "'IGCE Summary'!B30",
  "workbook_total_price_cell": "'IGCE Summary'!B31"
}
```

For CPAF, add `award_pool_rate` and `assumed_earned`. For CPIF, the primary recomputation verifies the target case; add formula assertions for overrun and underrun shares and bounds in `Scenario Analysis`.

If continuous coverage is present, put `annual_coverage_hours` on the relevant labor line. The recomputation rejects an FTE and productive-hours combination that does not reconcile within 0.5% or one hour.

Run:

```text
python scripts/recompute_expected_values.py validation-input.json
python scripts/validate_workbook.py output.xlsx --expected validation-input.json --engine auto
```

## Layer 3: real spreadsheet engine

With `--engine auto`, the validator looks for LibreOffice. When found, it performs a headless conversion cycle, reopens the calculated workbook with `data_only=True`, checks cached errors, and compares named workbook values against the independent Python result.

If formula execution cannot run, do not call the workbook fully recalculated. Use the exact disclosure required by the core skill.

## CR-specific manual gates

Confirm each item even when a script passes:

- Fee-bearing and non-fee-bearing amounts are visibly separate.
- CPFF form is Completion or Term and the rationale follows FAR 16.306(d).
- CPFF fee does not exceed the applicable ceiling.
- CPAF shows base-only, assumed-earned, and full-pool outcomes.
- CPIF uses separate contractor overrun and underrun shares and applies min/max bounds.
- Audited indirect rates are point estimates with date and authority, not scenario midpoints.
- CALC+ results are neutral positioning and distinguish estimated cost from cost plus fee.
- Day-trip M&IE is discounted once, not twice.
- BLS vintage is runtime-confirmed and aging is cell-referenced.
- No formula range contains text `TBD`.

## Fault injection

At minimum, test copies with these deliberate defects and confirm rejection:

1. Replace `keyword=` with `q=` in a recorded CALC+ query.
2. Change the month-gap formula to `DATEDIF` or `YEAR` on text.
3. Point a summary rate to the aged-annual-wage row instead of Direct Labor.
4. Apply fee to total estimated cost when a non-fee pass-through is present.
5. Remove the FCCM layer or apply it to the wrong base.
6. Change a CPIF underrun formula to use the overrun share.
7. Multiply 4.2 FTE by 1,880 hours against an 8,760-hour coverage requirement.

Document which faults were automated and which were manually inspected in `test.md`.

# CR Workbook Specification

Build one `.xlsx` workbook with seven sheets in this order:

1. `IGCE Summary`
2. `Cost Buildup`
3. `Scenario Analysis`
4. `Rate Validation`
5. `Travel Detail`
6. `Methodology`
7. `Raw Data`

Include `Travel Detail` even when travel is zero. Put `Travel Not Applicable` and numeric zero in the total cell so summary formulas retain a valid target.

### First-view decision dashboard

The first visible area of `IGCE Summary` must state the requirement, estimate purpose, period, fee structure, point estimate and useful range, major cost and fee drivers, the assumptions most likely to move the result, live-source status, and the next acquisition-team action. Keep the dashboard readable without horizontal scrolling and clearly distinguish estimated cost, fee, and estimated price. Do not make a price-reasonableness determination.

Do not deliver a workbook whose sheets merely exist. Populate the summary, cost buildup, fee logic, scenarios, source limitations, and next actions so a reviewer can understand and challenge the estimate without reverse-engineering formulas.

## 1. IGCE Summary

### Assumption block

Use these exact cells so the validator can inspect the aging formula:

| Cell | Label or value |
|---|---|
| A1:B1 | `IGCE Assumptions (Cost-Reimbursement)` |
| A2 / B2 | Fringe Rate / decimal input |
| A3 / B3 | Overhead Rate / decimal input |
| A4 / B4 | G&A Rate / decimal input |
| A5 / B5 | FCCM Rate / decimal input |
| A6 / B6 | Escalation Rate / decimal input |
| A7 / B7 | Productive Hours per Year / numeric input |
| A8 / B8 | Base Year Months / numeric input |
| A9 / B9 | BLS Vintage / `YYYY-MM` text input |
| A10 / B10 | Contract Start / `YYYY-MM` text input |
| A11 / B11 | Months Gap / formula below |
| A12 / B12 | Aging Factor / formula below |
| A13 / B13 | Fee Type / `CPFF`, `CPAF`, or `CPIF` |
| A14 / B14 | Primary Fee Rate / CPFF fixed, CPAF base, or CPIF target |
| A15 / B15 | Award Pool Rate or Contractor Overrun Share |
| A16 / B16 | Assumed Earned Percentage or Contractor Underrun Share |
| A17 / B17 | Minimum Fee Rate, blank when not applicable |
| A18 / B18 | Maximum Fee Rate, blank when not applicable |
| A19 / B19 | Total Periods (Base plus Options) / whole-number input, 1 for a single-period requirement |

Use these formulas:

```excel
B11 =MAX(0,(VALUE(LEFT(B10,4))-VALUE(LEFT(B9,4)))*12+VALUE(MID(B10,6,2))-VALUE(MID(B9,6,2)))
B12 =(1+B6)^(B11/12)
```

Format B12 as `0.0000`. Never use `YEAR(B9)`, `YEAR(B10)`, or `DATEDIF` on text.

### Summary table

Start at row 20. Show each labor category, SOC, location, FTE, hours, base and option-period estimated cost, fee-bearing cost, non-fee-bearing cost, fee, and estimated price. Label every amount as `Estimated Cost`, `Fee`, or `Estimated Price` correctly.

Keep separate rows for:

- Labor and allocable indirects
- Travel
- Fee-bearing ODCs or managed subcontracts
- Non-fee-bearing pass-through ODCs
- Total estimated cost
- Fee-bearing base
- Fee
- Total estimated price

Never apply a fee formula directly to total estimated cost unless that total equals the confirmed fee-bearing base.

Any total whose label contains the word `price` must equal estimated cost plus fee. Never label a cost-only subtotal as a price; a reviewer reading a price-labeled row must see the full Government obligation, not the cost component alone. The validator recomputes and rejects a price-labeled total that equals the cost subtotal instead of cost plus fee.

When the period of performance includes option periods (`Total Periods` greater than 1), the summary must show per-period estimated cost, fee, and estimated price for the base period and each option period, plus the all-periods total. Label the periods explicitly (`Base Year`, `Option Year 1`, and so on). A single compressed multi-year multiplier formula does not satisfy year-by-year Government exposure and the validator rejects it.

## 2. Cost Buildup

Use a fixed 23-row block for every labor category. Block `N` begins at:

```text
base row = 1 + (N - 1) * 23
```

| Offset | Label | Formula or input |
|---:|---|---|
| 0 | `Cost Buildup: <LCAT>` | header |
| 1 | BLS Base Annual Wage | source input |
| 2 | Aging Factor | `='IGCE Summary'!$B$12` |
| 3 | Aged Annual Wage | base wage times aging factor |
| 4 | Direct Labor Rate Hourly | aged wage divided by 2,080 |
| 5 | blank | separator |
| 6 | Fringe Rate | `='IGCE Summary'!$B$2` |
| 7 | Fringe Amount | direct rate times fringe |
| 8 | Labor plus Fringe | direct plus fringe |
| 9 | Overhead Rate | `='IGCE Summary'!$B$3` |
| 10 | Overhead Amount | labor plus fringe times overhead |
| 11 | Subtotal | labor plus fringe plus overhead |
| 12 | G&A Rate | `='IGCE Summary'!$B$4` |
| 13 | G&A Amount | subtotal times G&A |
| 14 | FCCM Rate | `='IGCE Summary'!$B$5` |
| 15 | FCCM Amount | subtotal plus G&A, times FCCM |
| 16 | Estimated Cost Rate | subtotal plus G&A plus FCCM |
| 17 | Fee Type | `='IGCE Summary'!$B$13` |
| 18 | Primary Fee Rate | `='IGCE Summary'!$B$14` |
| 19 | Estimated Fee Rate | fee-type formula |
| 20 | Estimated Price Rate | estimated cost plus estimated fee |
| 21 | Implied Multiplier | estimated price divided by direct hourly |
| 22 | blank | block separator |

For the first block, Direct Labor Rate Hourly is row 5. Cross-sheet formulas must reference row 5, not row 4, which is Aged Annual Wage.

Fee-rate formulas by type:

- CPFF: estimated cost rate times B14.
- CPAF: estimated cost rate times `(B14 + B15 * B16)`.
- CPIF target: estimated cost rate times B14. Put overrun, underrun, and bound mechanics on `Scenario Analysis` rather than hiding them in the labor block.

## 3. Scenario Analysis

Show low, mid, and high indirect-rate cases. Include component assumptions at the top, cost, fee-bearing base, non-fee cost, fee, and estimated price.

Every scenario must apply the same fee-base rule as the summary: fee applies only to the fee-bearing base, never to non-fee-bearing travel or pass-through ODCs the summary excludes from fee. The scenario row that represents current assumptions must reproduce the summary estimated price exactly; the validator cross-checks that row against the summary and rejects any mismatch beyond one dollar.

For CPAF, show within every cost scenario:

- Base fee only
- Base plus assumed-earned award pool
- Base plus full award pool

For CPIF, show a matrix with cost scenarios as columns and underrun, target, and overrun outcomes as rows. Include separate overrun and underrun contractor shares, min and max fee, and the first outcome where a bound applies.

For CPFF, label the scenarios as alternative Government estimate cases. Do not imply the negotiated fixed fee later floats with actual incurred cost.

## 4. Rate Validation

Use columns for labor category, BLS estimated cost rate, BLS estimated cost plus fee, CALC+ P25, P50, P75, P90, sample size, arithmetic divergence, and neutral note.

When a comparison pool is thin or contaminated, add title-match and experience-match columns with separate sample sizes. Do not write `reasonable`, `acceptable`, `competitive`, `outlier`, or a negotiation recommendation.

## 5. Travel Detail

Use a 17-row block per destination and sum each block into the Summary. For a day trip, use zero lodging and one already-discounted first/last-day M&IE amount. For overnight trips, calculate first and last day at 75% and intervening days at the full rate.

Include a `Fee-Bearing?` input for each destination. Default pass-through travel to `No` when the user has not directed otherwise, and disclose the assumption.

## 6. Methodology

Keep the narrative concise and auditable. Include:

- CR type and, for CPFF, Completion or Term form
- Regulatory sources and applicable fee ceiling
- Labor/SOC mapping and seniority convention
- BLS vintage, metro, series inputs, aging, and escalation
- Indirect-rate source, including audited-rate authority and effective date
- FCCM basis or explicit zero
- Fee-bearing classifications
- CPAF or CPIF scenario mechanics when applicable
- CALC+ pool construction and limitations
- Travel assumptions and fiscal year
- Coverage math, if applicable
- Exclusions and user-approved overrides
- Formula-validation layers actually run

Do not state that the workbook proves cost realism, fair and reasonable pricing, or contractor accounting-system adequacy.

## 7. Raw Data

Record compact reproducible parameters and result summaries, not full JSON dumps. Include BLS series inputs and selected percentiles, CALC+ query and pool statistics, Per Diem locality and rates, every fallback, and the source of user-supplied rates.

## Formatting and safety

- Blue font for user-editable inputs; black for formulas.
- Currency: `$#,##0.00` or `$#,##0` consistently.
- Percentage: `0.0%`; aging factor: `0.0000`.
- Freeze panes below assumption and header blocks.
- Escape text beginning with `=`, `+`, `-`, or `@` so Excel does not parse it as a formula.
- Use numeric zero, never text `TBD`, in formula ranges.
- Autosize columns within readable limits and wrap long notes.

### 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.

### Delivery-view gate

Before delivery, recalculate and save the final workbook through a calculation
engine. The delivered file must retain cached values for formula cells on `IGCE
Summary`, including fee-bearing cost, total cost, fee, and total price. Render
the summary and correct blank formula outputs, clipped limitations, unreadable
assumptions, or a summary that requires horizontal scrolling to understand.

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
`IGCE Summary` title, decision dashboard, cost and fee totals, assumptions, and
next action must remain on one page wide when printed or exported; never split
the dashboard horizontally.

# Cost Pool and Fee Reference

Use this reference only after the user confirms the indirect-rate basis and CR type. These are estimating assumptions, not approved contractor rates.

## Indirect-cost scenarios

| Component | Low | Mid | High | Application base |
|---|---:|---:|---:|---|
| Fringe | 25% | 32% | 40% | Direct labor |
| Overhead | 60% | 80% | 120% | Direct labor plus fringe |
| G&A | 8% | 12% | 18% | Labor plus fringe plus overhead |
| FCCM | 0% | 0% | 0.5% | Subtotal plus G&A |

Use FCCM only with a CO-supplied basis tied to FAR 31.205-10 and CAS 414. If the user supplies an FPRA, FPRR, or audited indirect rates, use those rates as the point estimate and document the effective date and approving authority. Do not create artificial low and high offsets around audited rates.

## Fee-bearing classification

Classify every cost element before calculating fee.

| Cost element | Default estimating treatment | Required action |
|---|---|---|
| Contractor labor and allocable indirects | Fee-bearing | Confirm with the selected fee structure |
| Contractor-developed deliverable or managed subcontract | Undetermined | Ask the user |
| Travel reimbursed at government rates | Non-fee-bearing pass-through | Disclose and allow the user to override |
| Commercial license or third-party hardware at cost | Non-fee-bearing pass-through | Disclose and allow the user to override |
| Government-furnished-equivalent material | Non-fee-bearing | Keep outside the fee base |

Never infer that all ODCs bear fee. Keep fee-bearing cost, non-fee-bearing cost, total estimated cost, fee, and total estimated price as separate workbook lines.

## CPFF

Record Completion or Term form.

```text
fixed fee = confirmed fee-bearing estimated cost * fixed-fee rate
total estimated price = total estimated cost + fixed fee
```

The fixed fee is set at award and does not vary with actual incurred cost, except for changes in the work. Workbook low, mid, and high calculations are alternative Government estimate scenarios, not a formula for paying fee as actual cost changes.

Apply the current FAR 15.404-4(c)(4)(i) ceilings:

- 15% for experimental, developmental, or research CPFF work.
- 10% for other CPFF work.
- The separate 6% architect-engineer limitation when applicable.

Do not treat any ceiling as a recommended rate.

## CPAF

```text
base fee = fee-bearing estimated cost * base-fee rate
award pool = fee-bearing estimated cost * award-pool rate
assumed earned award fee = award pool * assumed-earned percentage
estimated fee = base fee + assumed earned award fee
```

Show all three outcomes:

1. Base fee only.
2. Base fee plus assumed-earned pool, default 85% when the user accepts it.
3. Base fee plus full award pool.

Describe the 85% case as an estimating assumption, never as an expected performance judgment.

## CPIF

Keep overrun and underrun contractor shares separate.

```text
target fee = target fee-bearing cost * target-fee rate

overrun fee = target fee
              - (actual fee-bearing cost - target fee-bearing cost)
              * contractor overrun share
overrun fee = max(overrun fee, target fee-bearing cost * minimum-fee rate)

underrun fee = target fee
               + (target fee-bearing cost - actual fee-bearing cost)
               * contractor underrun share
underrun fee = min(underrun fee, target fee-bearing cost * maximum-fee rate)
```

Run at least target, 10% overrun, and 10% underrun. Also run a wide-enough case, often 25%, to show each fee bound if the inputs permit. State the exact cost at which each bound takes effect.

## Scenario rules

- Use low, mid, and high indirect-cost scenarios only when the rates are estimating assumptions.
- When audited rates control, use the audited point estimate across the primary workbook and show sensitivity only if the user requests it.
- Keep travel and pass-through classifications constant across scenarios.
- Recompute fee on each Government estimate scenario using that scenario's confirmed fee-bearing base.
- In every scenario, show both estimated cost and estimated price. Do not call estimated price `cost`.

#!/usr/bin/env python3
"""Independently recompute CR cost, fee, and estimated price from raw 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 when validation inputs are missing or invalid."""


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 a number")
    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 _setting(
    line: dict[str, Any], assumptions: dict[str, Any], key: str, *, minimum: float = 0
) -> float:
    if key in line:
        value = line[key]
    elif key in assumptions:
        value = assumptions[key]
    else:
        raise InputError(f"missing {key} for {line.get('name', '<unnamed>')}")
    return _number(value, f"{line.get('name', '<unnamed>')}.{key}", minimum=minimum)


def _period_count(assumptions: dict[str, Any]) -> int:
    """Validate assumptions.periods, the total period count (base plus options)."""
    periods = assumptions.get("periods", 1)
    if isinstance(periods, bool) or not isinstance(periods, int) or periods < 1:
        raise InputError("assumptions.periods must be a whole number of at least 1")
    return periods


def _fee_rate(assumptions: dict[str, Any]) -> tuple[str, float]:
    raw_type = assumptions.get("fee_type")
    if not isinstance(raw_type, str):
        raise InputError("assumptions.fee_type must be CPFF, CPAF, or CPIF")
    fee_type = raw_type.upper()
    primary = _number(assumptions.get("primary_fee_rate"), "primary_fee_rate", minimum=0)
    if fee_type == "CPFF" or fee_type == "CPIF":
        return fee_type, primary
    if fee_type == "CPAF":
        pool = _number(assumptions.get("award_pool_rate"), "award_pool_rate", minimum=0)
        earned = _number(assumptions.get("assumed_earned"), "assumed_earned", minimum=0)
        if earned > 1:
            raise InputError("assumed_earned must not exceed 1")
        return fee_type, primary + pool * earned
    raise InputError("assumptions.fee_type must be CPFF, CPAF, or CPIF")


def _copy_reference(raw: dict[str, Any], result: dict[str, Any], key: str) -> None:
    if key not in raw:
        return
    value = raw[key]
    if not isinstance(value, str) or not value.strip():
        raise InputError(f"{raw.get('name', '<unnamed>')}.{key} must be a non-empty string")
    result[key] = value


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

    periods = _period_count(assumptions)
    fee_type, effective_fee_rate = _fee_rate(assumptions)
    calculated_labor: list[dict[str, Any]] = []
    fee_bearing_cost = 0.0

    for index, raw in enumerate(labor_lines):
        if not isinstance(raw, dict):
            raise InputError(f"labor_lines[{index}] must be an object")
        name = raw.get("name")
        if not isinstance(name, str) or not name.strip():
            raise InputError(f"labor_lines[{index}].name must be a non-empty string")

        annual_wage = _number(raw.get("annual_wage"), f"{name}.annual_wage", minimum=0)
        aging = _setting(raw, assumptions, "aging_factor")
        fringe = _setting(raw, assumptions, "fringe_rate")
        overhead = _setting(raw, assumptions, "overhead_rate")
        ga = _setting(raw, assumptions, "ga_rate")
        fccm = _setting(raw, assumptions, "fccm_rate")
        hours = _setting(raw, assumptions, "productive_hours")
        fte = _number(raw.get("fte"), f"{name}.fte", minimum=0)
        months = _number(raw.get("months", 12), f"{name}.months", minimum=0)
        period_multiplier = _number(
            raw.get("period_multiplier", 1), f"{name}.period_multiplier", minimum=0
        )

        priced_hours = hours * fte
        if "annual_coverage_hours" in raw:
            coverage = _number(
                raw["annual_coverage_hours"], f"{name}.annual_coverage_hours", minimum=0
            )
            if not math.isclose(priced_hours, coverage, rel_tol=0.005, abs_tol=1.0):
                raise InputError(
                    f"{name}.productive_hours * fte is {priced_hours:.4f}, "
                    f"which does not reconcile to annual_coverage_hours {coverage:.4f}"
                )

        aged_wage = annual_wage * aging
        direct = aged_wage / 2080.0
        fringe_amount = direct * fringe
        labor_fringe = direct + fringe_amount
        overhead_amount = labor_fringe * overhead
        subtotal = labor_fringe + overhead_amount
        ga_amount = subtotal * ga
        fccm_amount = (subtotal + ga_amount) * fccm
        cost_rate = subtotal + ga_amount + fccm_amount
        fee_rate = cost_rate * effective_fee_rate
        price_rate = cost_rate + fee_rate
        period_cost = cost_rate * hours * fte * (months / 12.0) * period_multiplier
        period_fee = period_cost * effective_fee_rate
        period_price = period_cost + period_fee
        fee_bearing_cost += period_cost

        result: dict[str, Any] = {
            "name": name,
            "aged_annual_wage": aged_wage,
            "direct_hourly_rate": direct,
            "estimated_cost_rate": cost_rate,
            "estimated_fee_rate": fee_rate,
            "estimated_price_rate": price_rate,
            "period_cost": period_cost,
            "period_fee": period_fee,
            "period_price": period_price,
        }
        for key in (
            "workbook_cost_rate_cell",
            "workbook_price_rate_cell",
            "workbook_total_cost_cell",
            "workbook_total_price_cell",
        ):
            _copy_reference(raw, result, key)
        calculated_labor.append(result)

    non_fee_cost = 0.0
    calculated_non_labor: list[dict[str, Any]] = []
    for index, raw in enumerate(non_labor_lines):
        if not isinstance(raw, dict):
            raise InputError(f"non_labor_lines[{index}] must be an object")
        name = raw.get("name")
        if not isinstance(name, str) or not name.strip():
            raise InputError(f"non_labor_lines[{index}].name must be a non-empty string")
        amount = _number(raw.get("amount"), f"{name}.amount", minimum=0)
        fee_bearing = raw.get("fee_bearing", False)
        if not isinstance(fee_bearing, bool):
            raise InputError(f"{name}.fee_bearing must be true or false")
        if fee_bearing:
            fee_bearing_cost += amount
        else:
            non_fee_cost += amount
        result = {"name": name, "amount": amount, "fee_bearing": fee_bearing}
        _copy_reference(raw, result, "workbook_total_cell")
        calculated_non_labor.append(result)

    total_cost = fee_bearing_cost + non_fee_cost
    total_fee = fee_bearing_cost * effective_fee_rate
    total_price = total_cost + total_fee
    output: dict[str, Any] = {
        "periods": periods,
        "fee_type": fee_type,
        "effective_fee_rate": effective_fee_rate,
        "labor_lines": calculated_labor,
        "non_labor_lines": calculated_non_labor,
        "fee_bearing_cost": fee_bearing_cost,
        "non_fee_bearing_cost": non_fee_cost,
        "total_estimated_cost": total_cost,
        "total_fee": total_fee,
        "total_estimated_price": total_price,
    }
    for key in (
        "workbook_fee_bearing_cost_cell",
        "workbook_total_cost_cell",
        "workbook_total_fee_cell",
        "workbook_total_price_cell",
    ):
        if key in payload:
            value = payload[key]
            if not isinstance(value, str) or not value.strip():
                raise InputError(f"{key} must be a non-empty string")
            output[key] = value
    return output


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 CR cost and fee totals.")
    parser.add_argument("input", type=Path, help="Validation-input JSON file")
    parser.add_argument("--output", type=Path, help="Optional JSON output 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 a CR workbook structurally and with optional engine execution."""

from __future__ import annotations

import argparse
import json
import math
import re
import shutil
import subprocess
import sys
import tempfile
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


DEFAULT_SHEETS = [
    "IGCE Summary",
    "Cost Buildup",
    "Scenario Analysis",
    "Rate Validation",
    "Travel Detail",
    "Methodology",
    "Raw Data",
]

CELL_REF = re.compile(
    r"^(?:'((?:[^']|'')+)'|([^!]+))!\$?([A-Za-z]{1,3})\$?([1-9][0-9]*)$"
)
COST_B_REF = re.compile(
    r"(?:'Cost Buildup'|Cost Buildup)!\$?B\$?([1-9][0-9]*)",
    re.IGNORECASE,
)
BASE_PERIOD_LABEL = re.compile(r"\bBASE[\s-]*(?:YEAR|PERIOD)\b")
OPTION_PERIOD_LABEL = re.compile(
    r"\bOPTION\s*(?:YEAR|PERIOD)?\s*0*([1-9][0-9]*)\b|\bO[YP]\s*0*([1-9][0-9]*)\b"
)
TOTAL_PERIODS_CELL = "B19"
PRICE_TOLERANCE = 1.0


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("''", "'")
    coordinate = f"{match.group(3).upper()}{match.group(4)}"
    return sheet, coordinate


def cell_value(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],
    sheet: Any,
    coordinate: str,
    *,
    expected: str | None = None,
    contains: list[str] | None = None,
    not_contains: list[str] | None = None,
) -> None:
    value = sheet[coordinate].value
    if not isinstance(value, str) or not value.startswith("="):
        failures.append(f"{sheet.title}!{coordinate} is not a formula")
        return
    normalized = normalize_formula(value)
    if expected is not None and normalized != normalize_formula(expected):
        failures.append(f"{sheet.title}!{coordinate} formula does not match expected structure")
    for item in contains or []:
        if normalize_formula(item) not in normalized:
            failures.append(f"{sheet.title}!{coordinate} formula is missing {item}")
    for item in not_contains or []:
        if normalize_formula(item) in normalized:
            failures.append(f"{sheet.title}!{coordinate} formula contains forbidden text {item}")


def _is_number(value: Any) -> bool:
    return not isinstance(value, bool) and isinstance(value, (int, float))


def _nearest_header(sheet: Any, row: int, column: int, limit: int = 40) -> str:
    """Return the closest non-formula text above a cell in the same column."""
    for row_index in range(row - 1, max(0, row - limit), -1):
        value = sheet.cell(row_index, column).value
        if isinstance(value, str) and value.strip() and not value.startswith("="):
            return value.upper()
    return ""


def _is_price_label(value: Any) -> bool:
    if not isinstance(value, str):
        return False
    text = value.strip()
    if not text or len(text) > 45 or text.endswith(".") or text.startswith("="):
        return False
    return "PRICE" in text.upper()


def effective_periods(workbook: Any, payload_periods: int, failures: list[str]) -> int:
    """Reconcile the Total Periods assumption cell with the validation input."""
    if "IGCE Summary" not in workbook.sheetnames:
        return payload_periods
    value = workbook["IGCE Summary"][TOTAL_PERIODS_CELL].value
    if value is None:
        if payload_periods > 1:
            failures.append(
                f"IGCE Summary!{TOTAL_PERIODS_CELL} must record Total Periods (Base plus "
                f"Options) for a {payload_periods}-period requirement"
            )
        return payload_periods
    if not _is_number(value) or float(value) != int(value) or int(value) < 1:
        failures.append(
            f"IGCE Summary!{TOTAL_PERIODS_CELL} Total Periods (Base plus Options) must be "
            "a whole number of at least 1"
        )
        return payload_periods
    declared = int(value)
    if payload_periods > 1 and declared != payload_periods:
        failures.append(
            f"IGCE Summary!{TOTAL_PERIODS_CELL} Total Periods is {declared} but the "
            f"validation input states {payload_periods} periods"
        )
    return max(declared, payload_periods)


def period_coverage_audit(workbook: Any, periods: int) -> list[str]:
    """Require per-period labels on IGCE Summary for a multi-period requirement."""
    failures: list[str] = []
    if periods <= 1 or "IGCE Summary" not in workbook.sheetnames:
        return failures
    base_found = False
    option_numbers: set[int] = set()
    for row in workbook["IGCE Summary"].iter_rows():
        for cell in row:
            value = cell.value
            if not isinstance(value, str):
                continue
            upper = value.upper()
            if BASE_PERIOD_LABEL.search(upper):
                base_found = True
            for match in OPTION_PERIOD_LABEL.finditer(upper):
                option_numbers.add(int(match.group(1) or match.group(2)))
    if not base_found or len(option_numbers) < periods - 1:
        found = ", ".join(f"Option {n}" for n in sorted(option_numbers)) or "none"
        failures.append(
            f"the stated period of performance has {periods} periods (base plus "
            f"{periods - 1} options) but IGCE Summary shows no per-period breakdown of "
            f"estimated cost, fee, and estimated price (base label found: "
            f"{'yes' if base_found else 'no'}; option-period labels found: {found}); a "
            "single compressed multi-year multiplier row does not show year-by-year "
            "Government exposure"
        )
    return failures


def price_integrity_audit(cached: Any) -> list[str]:
    """Reject price-labeled totals that omit fee, and scenario fee-base drift.

    Works on the cached-value view of the workbook: any total labeled as a price
    must equal estimated cost plus fee, and the Scenario Analysis row built on
    current assumptions must reproduce the summary estimated price.
    """
    failures: list[str] = []
    if "IGCE Summary" not in cached.sheetnames:
        return failures
    summary = cached["IGCE Summary"]

    label_cost: float | None = None
    label_fee: float | None = None
    for row in summary.iter_rows():
        label = next((c.value for c in row if isinstance(c.value, str) and c.value.strip()), None)
        numbers = [float(c.value) for c in row if _is_number(c.value)]
        if label is None or not numbers or len(label) > 45:
            continue
        upper = label.upper()
        if "PRICE" in upper:
            continue
        if "COST" in upper and "TOTAL" in upper and "RATE" not in upper:
            label_cost = numbers[-1]
        elif "FEE" in upper and not any(
            word in upper for word in ("BASE", "BEARING", "RATE", "TYPE")
        ):
            label_fee = numbers[-1]

    summary_cost: float | None = None
    summary_fee: float | None = None
    for row in summary.iter_rows():
        price_label = next((c.value for c in row if _is_price_label(c.value)), None)
        if price_label is None:
            continue
        numeric_cells = [c for c in row if _is_number(c.value)]
        if not numeric_cells:
            continue
        row_cost: float | None = None
        row_fee: float | None = None
        for cell in numeric_cells:
            header = _nearest_header(summary, cell.row, cell.column)
            if "FEE" in header and "PRICE" not in header:
                row_fee = float(cell.value)
            elif "COST" in header and "PRICE" not in header:
                row_cost = float(cell.value)
        cost = row_cost if row_cost is not None else label_cost
        fee = row_fee if row_fee is not None else label_fee
        if cost is None or fee is None or fee <= PRICE_TOLERANCE:
            continue
        summary_cost, summary_fee = cost, fee
        expected_price = cost + fee
        values = [float(c.value) for c in numeric_cells]
        if not any(abs(value - expected_price) <= PRICE_TOLERANCE for value in values):
            detail = (
                " and equals the cost subtotal"
                if any(abs(value - cost) <= PRICE_TOLERANCE for value in values)
                else ""
            )
            failures.append(
                f"IGCE Summary row {numeric_cells[0].row} is labeled "
                f"{price_label.strip()!r} but its total omits the fee{detail}: any total "
                f"labeled as a price must equal estimated cost ({cost:,.2f}) plus fee "
                f"({fee:,.2f}) = {expected_price:,.2f}"
            )

    if summary_cost is None or summary_fee is None or "Scenario Analysis" not in cached.sheetnames:
        return failures
    scenario = cached["Scenario Analysis"]
    header_row = cost_column = price_column = None
    for row in scenario.iter_rows():
        columns = {
            "cost": None,
            "price": None,
        }
        for cell in row:
            value = cell.value
            if not isinstance(value, str):
                continue
            upper = value.upper()
            if "PRICE" in upper:
                columns["price"] = cell.column
            elif "COST" in upper:
                columns["cost"] = cell.column
        if columns["cost"] and columns["price"]:
            header_row, cost_column, price_column = row[0].row, columns["cost"], columns["price"]
            break
    if header_row is None:
        return failures
    expected_price = summary_cost + summary_fee
    for row_index in range(header_row + 1, scenario.max_row + 1):
        cost_value = scenario.cell(row_index, cost_column).value
        price_value = scenario.cell(row_index, price_column).value
        if not _is_number(cost_value) or abs(float(cost_value) - summary_cost) > PRICE_TOLERANCE:
            continue
        if _is_number(price_value) and abs(float(price_value) - expected_price) > PRICE_TOLERANCE:
            failures.append(
                f"Scenario Analysis row {row_index} prices the current-assumptions case at "
                f"{float(price_value):,.2f} but the IGCE Summary estimated price is "
                f"{expected_price:,.2f} (cost {summary_cost:,.2f} plus fee "
                f"{summary_fee:,.2f}); the scenario sheet must apply the same fee-base "
                "rule as the summary"
            )
        break
    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] = []
    required_sheets = payload.get("required_sheets", DEFAULT_SHEETS)
    if not isinstance(required_sheets, list) or not all(
        isinstance(item, str) for item in required_sheets
    ):
        raise InputError("required_sheets must be an array of strings")
    for sheet_name in required_sheets:
        if sheet_name not in workbook.sheetnames:
            failures.append(f"missing required sheet: {sheet_name}")

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

    formula_count = 0
    formula_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) and value.startswith("="):
                    formula_count += 1
                    upper = value.upper()
                    if any(token in upper for token in formula_error_tokens):
                        failures.append(f"{sheet.title}!{cell.coordinate} contains a formula error token")
                elif isinstance(value, str) and 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")

    if "Cost Buildup" in workbook.sheetnames:
        buildup = workbook["Cost Buildup"]
        starts: list[int] = []
        for row_index in range(1, buildup.max_row + 1):
            label = buildup.cell(row_index, 1).value
            if isinstance(label, str) and label.startswith("Cost Buildup:"):
                starts.append(row_index)
        if not starts:
            failures.append("Cost Buildup contains no recognized labor blocks")
        for block_index, base in enumerate(starts):
            expected_base = 1 + block_index * 23
            if base != expected_base:
                failures.append(
                    f"Cost Buildup block {block_index + 1} starts at row {base}, expected {expected_base}"
                )
            if buildup[f"B{base + 5}"].value is not None:
                failures.append(f"Cost Buildup!B{base + 5} must be the blank separator row")
            if buildup[f"B{base + 22}"].value is not None:
                failures.append(f"Cost Buildup!B{base + 22} must be the blank block separator")
            check_formula(
                failures,
                buildup,
                f"B{base + 2}",
                contains=["'IGCE SUMMARY'!$B$12"],
            )
            if buildup[f"B{base + 2}"].number_format != "0.0000":
                failures.append(
                    f"Cost Buildup!B{base + 2} must display the aging factor as 0.0000"
                )
            assumptions = payload.get("assumptions", {})
            fee_type = str(assumptions.get("fee_type", "")).upper()
            if fee_type == "CPAF":
                fee_formula = (
                    f"=B{base + 16}*('IGCE Summary'!$B$14+"
                    "'IGCE Summary'!$B$15*'IGCE Summary'!$B$16)"
                )
            else:
                fee_formula = f"=B{base + 16}*'IGCE Summary'!$B$14"
            formulas = {
                base + 3: f"=B{base + 1}*B{base + 2}",
                base + 4: f"=B{base + 3}/2080",
                base + 6: "='IGCE Summary'!$B$2",
                base + 7: f"=B{base + 4}*B{base + 6}",
                base + 8: f"=B{base + 4}+B{base + 7}",
                base + 9: "='IGCE Summary'!$B$3",
                base + 10: f"=B{base + 8}*B{base + 9}",
                base + 11: f"=B{base + 8}+B{base + 10}",
                base + 12: "='IGCE Summary'!$B$4",
                base + 13: f"=B{base + 11}*B{base + 12}",
                base + 14: "='IGCE Summary'!$B$5",
                base + 15: f"=(B{base + 11}+B{base + 13})*B{base + 14}",
                base + 16: f"=B{base + 11}+B{base + 13}+B{base + 15}",
                base + 17: "='IGCE Summary'!$B$13",
                base + 18: "='IGCE Summary'!$B$14",
                base + 19: fee_formula,
                base + 20: f"=B{base + 16}+B{base + 19}",
                base + 21: f"=B{base + 20}/B{base + 4}",
            }
            for row_number, expected_formula in formulas.items():
                check_formula(
                    failures,
                    buildup,
                    f"B{row_number}",
                    expected=expected_formula,
                )

    for sheet_name in ("IGCE Summary", "Scenario Analysis", "Rate Validation"):
        if sheet_name not in workbook.sheetnames:
            continue
        sheet = workbook[sheet_name]
        for row in sheet.iter_rows():
            for cell in row:
                value = cell.value
                if not isinstance(value, str) or not value.startswith("="):
                    continue
                for match in COST_B_REF.finditer(value):
                    referenced_row = int(match.group(1))
                    if (referenced_row - 4) % 23 == 0:
                        failures.append(
                            f"{sheet_name}!{cell.coordinate} uses Cost Buildup row {referenced_row} "
                            "as a cross-sheet input; that row is Aged Annual Wage, not Direct Labor"
                        )

    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")
        sheet_name, coordinate = parse_cell_ref(assertion["cell"])
        if sheet_name not in workbook.sheetnames:
            failures.append(f"formula assertion references missing sheet: {sheet_name}")
            continue
        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 an array of 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 an array of strings"
            )
        equals = assertion.get("equals")
        if equals is not None and not isinstance(equals, str):
            raise InputError(f"formula_assertions[{index}].equals must be a string")
        check_formula(
            failures,
            workbook[sheet_name],
            coordinate,
            expected=equals,
            contains=contains,
            not_contains=not_contains,
        )
    failures.extend(text_clipping_audit(workbook))
    return failures


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


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


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_results(
    workbook: Any,
    expected: dict[str, Any],
    tolerance: float,
) -> list[str]:
    failures: list[str] = []

    def compare(reference: str, target: float, label: str) -> None:
        try:
            value = cell_value(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")
            return
        if not close_enough(float(value), target, tolerance):
            failures.append(
                f"{label} at {reference} is {float(value):.6f}, expected {target:.6f}"
            )

    for line in expected["labor_lines"]:
        mappings = (
            ("workbook_cost_rate_cell", "estimated_cost_rate", "cost rate"),
            ("workbook_price_rate_cell", "estimated_price_rate", "price rate"),
            ("workbook_total_cost_cell", "period_cost", "period cost"),
            ("workbook_total_price_cell", "period_price", "period price"),
        )
        for reference_key, expected_key, label in mappings:
            if reference_key in line:
                compare(
                    line[reference_key],
                    line[expected_key],
                    f"{line['name']} {label}",
                )
    for line in expected["non_labor_lines"]:
        if "workbook_total_cell" in line:
            compare(
                line["workbook_total_cell"],
                line["amount"],
                f"{line['name']} amount",
            )
    totals = (
        ("workbook_fee_bearing_cost_cell", "fee_bearing_cost", "fee-bearing cost"),
        ("workbook_total_cost_cell", "total_estimated_cost", "total estimated cost"),
        ("workbook_total_fee_cell", "total_fee", "total fee"),
        ("workbook_total_price_cell", "total_estimated_price", "total estimated price"),
    )
    for reference_key, expected_key, label in totals:
        if reference_key in expected:
            compare(expected[reference_key], expected[expected_key], label)
    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 main() -> int:
    parser = argparse.ArgumentParser(
        description="Audit a CR workbook and optionally verify formula execution with LibreOffice."
    )
    parser.add_argument("workbook", type=Path, help="Workbook to validate")
    parser.add_argument("--expected", required=True, type=Path, help="Validation-input JSON")
    parser.add_argument(
        "--engine",
        choices=("auto", "none", "libreoffice"),
        default="auto",
        help="Formula execution engine policy",
    )
    parser.add_argument("--tolerance", type=float, default=0.01, help="Relative comparison tolerance")
    parser.add_argument("--json", action="store_true", help="Emit a JSON result")
    args = parser.parse_args()

    if not math.isfinite(args.tolerance) or args.tolerance < 0:
        print("ERROR: tolerance must be finite and non-negative", file=sys.stderr)
        return 2
    if not args.workbook.is_file():
        print(f"ERROR: workbook not found: {args.workbook}", file=sys.stderr)
        return 2

    try:
        payload = load_payload(args.expected)
        expected = calculate(payload)
        formula_workbook = load_workbook(args.workbook, data_only=False)
        structural_failures = structural_audit(formula_workbook, payload)
        periods = effective_periods(formula_workbook, expected["periods"], structural_failures)
        structural_failures.extend(period_coverage_audit(formula_workbook, periods))
        cached_view = load_workbook(args.workbook, data_only=True)
        structural_failures.extend(price_integrity_audit(cached_view))
        failures = list(structural_failures)
    except (InputError, OSError, ValueError) as exc:
        print(f"ERROR: {exc}", file=sys.stderr)
        return 2

    engine_used: str | None = None
    engine_note: str
    temp: tempfile.TemporaryDirectory[str] | None = None
    try:
        if args.engine == "none":
            engine_note = "Formula execution was not requested."
        else:
            soffice = find_soffice()
            if soffice is None:
                if args.engine == "libreoffice":
                    failures.append("LibreOffice was required but soffice was not found")
                    engine_note = "LibreOffice was required but unavailable."
                else:
                    engine_note = "Formula execution was not independently verified in Excel or LibreOffice."
            else:
                temp, recalculated = recalculate_with_libreoffice(args.workbook, soffice)
                calculated_workbook = load_workbook(recalculated, data_only=True)
                failures.extend(cached_error_audit(calculated_workbook))
                failures.extend(compare_results(calculated_workbook, expected, args.tolerance))
                engine_used = "LibreOffice"
                engine_note = "LibreOffice formula execution and cached-value comparison ran."
    except (InputError, OSError, subprocess.SubprocessError, ValueError) as exc:
        failures.append(str(exc))
        engine_note = "LibreOffice execution failed."
    finally:
        if temp is not None:
            temp.cleanup()

    result = {
        "status": "pass" if not failures else "fail",
        "formula_structure": "pass" if not structural_failures else "fail",
        "independent_recomputation": "pass",
        "independent_total_estimated_price": expected["total_estimated_price"],
        "engine": engine_used,
        "engine_note": engine_note,
        "failures": failures,
    }
    if args.json:
        print(json.dumps(result, indent=2, sort_keys=True))
    else:
        if failures:
            print("VALIDATION FAILED")
            for failure in failures:
                print(f"- {failure}")
        elif engine_used:
            print("Formula structure, independent calculations, and LibreOffice formula execution passed.")
        else:
            print(
                "Formula structure and independent calculations passed. "
                "Formula execution was not independently verified in Excel or LibreOffice."
            )
        print(f"Independent total estimated price: {expected['total_estimated_price']:.2f}")
        print(engine_note)
    return 0 if not failures else 1


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