#4983 Evaluating "Pain", "Broke", "Would be Nice" Opportunities

#4983 Evaluating "Pain", "Broke", "Would be Nice" Opportunities

#4983 Given a list of legitimate opportunities to provide innovative solutions to "pain", "broke" and "would be nice" in industry and user groups, describe the sequence of innovative thinking methods should be applied to understand the system, product or service, understand the "pain", "broke" and "would be nice", recommend fix(es) and/or improvement(s), and describe implementation of each.

Transforming a raw list of "pain" (friction, cost, risk), "broke" (dysfunctional components or broken workflows), and "would be nice" (latent desires or efficiency gains) opportunities into deployed solutions requires an end-to-end innovation pipeline. Rather than jumping straight to brainstorming, an effective sequence moves systematically from system deconstruction and human/root-cause diagnosis to contradiction-free ideation (TRIZ IFR) and validated execution.

1.Map the System & Value Architecture:Baseline before diagnosing flaws.

Before analyzing individual opportunities, establish how the current system, product, or service operates as a whole.

  • Function Analysis & Component Modeling: Map every element in the system, its super-system (environment/adjacent tools), and the user, defining whether each interaction is useful, insufficient, excessive, or harmful.

  • Service Blueprinting & Value Stream Mapping: Trace the end-to-end user journey alongside backstage processes, data flows, and handoffs to locate where value is created versus where bottlenecks accumulate.

  • Jobs-to-Be-Done (JTBD) Framing: Define the core functional, emotional, and social progress the user is trying to make—stripping away assumptions tied to existing implementations.

2.Diagnose & Triage Pain, Broke, and Would-Be-Nice:Match analytical lenses to opportunity type.

Each opportunity category stems from a different underlying mechanism and requires a distinct diagnostic method:

  • For "Broke" (Failure Modes & Root Causes): Apply 5 Whys, Ishikawa (Fishbone) Diagrams, and Failure Mode and Effects Analysis (FMEA) to isolate physical, logical, or process failure points rather than treating surface symptoms.

  • For "Pain" (Friction, Risk & High Cost): Conduct Contextual Inquiry (observing users in their real environment) and Journey Friction Auditing to quantify time lost, cognitive load, workarounds, and financial or safety penalties.

  • For "Would Be Nice" (Latent Desires & Delighters): Use Kano Model Classification and Outcome-Driven Innovation (ODI) to separate low-impact vanity requests from underserved outcomes that unlock disproportionate value.

3.Reframe with TRIZ Ideal Final Result (IFR):Eliminate trade-offs without adding complexity.

Once the core problem is verified, avoid conventional compromise-heavy brainstorming by defining the ideal state using TRIZ Ideal Final Result (IFR):

  • Define the Primary Useful Function (PUF): State strictly what action must happen on the target object (e.g., "seal the joint" rather than "add a thicker gasket").

  • Formulate IFR Archetypes: Push the design toward Self-Service (the object performs the function itself), Environmental Leverage (ambient forces do the work), Harm Transformation (waste or friction is converted into a useful output), or Disappearance (the mechanism vanishes while its function remains).

  • Audit System Resources: Inventory free or underutilized substances, fields (gravity, thermal gradients, pressure, data exhaust), space, and idle time windows already inside the operational zone.

  • Resolve Contradictions: Identify where improving one parameter degrades another, and apply Separation Principles (in time, space, scale, or upon condition) to achieve both simultaneously.

4.Synthesize & Prioritize Solution Recommendations:Filter concepts by feasibility, impact, and risk.

Translate ideal directions into concrete fixes (for broke), friction removals (for pain), and value enhancers (for would be nice), then rank them rigorously:

  • Morphological Analysis & Concept Blending: Combine the strongest resource-leveraging mechanisms into coherent solution architectures.

  • Impact vs. Effort / RICE Scoring: Evaluate every recommendation against Reach, Impact, Confidence, and Effort (or technical feasibility vs. business/user value) to distinguish immediate quick wins from structural architectural upgrades.

  • Pre-Mortem & Second-Order Risk Check: Stress-test top candidates to ensure solving one pain point does not introduce new failure modes or downstream maintenance debt.

5.Validate & Execute Implementation:De-risk assumptions before full-scale rollout.

Move from validated recommendation to production through iterative, evidence-gated execution:

  • Assumption Mapping & Rapid Prototyping: Identify the riskiest technical or behavioral assumption behind each recommendation and test it with low-fidelity prototypes, digital twins, or Wizard-of-Oz simulations.

  • Controlled Pilot & A/B Rollout: Deploy fixes and improvements to a bounded cohort or single production line, measuring baseline vs. post-change KPIs (defect rate, cycle time, adoption, satisfaction).

  • Standardization & Continuous Telemetry: Codify validated changes into Standard Operating Procedures (SOPs), automated tests, or product specs, and instrument ongoing monitoring so newly surfaced "would be nice" insights feed back into Step 1.

How the Methods Align Across Opportunity Types

Opportunity TypePrimary Diagnostic MethodCore Ideation LensTypical Deliverable
"Broke"FMEA, 5 Whys, Fault Tree AnalysisTRIZ Harm Transformation & Root EliminationStructural fix, fail-safe mechanism, or workflow redesign
"Pain"Contextual Inquiry, Value Stream & Friction MappingTRIZ Self-Service & Separation PrinciplesAutomation, step removal, or zero-friction interface
"Would Be Nice"Kano Model, Outcome-Driven Innovation (ODI)TRIZ Environmental & Resource LeverageHigh-leverage feature, predictive capability, or delighter


Create a weighted scoring rubric and template to evaluate and prioritize my list of pain, broke, and would-be-nice opportunities.

A two-tier Weighted Opportunity Scoring Rubric balances urgent remediation (broke and pain) against high-leverage innovation (would be nice), while pairing value potential with execution feasibility and risk.

1. Scoring Dimensions & Weights (1–10 Scale)

Every opportunity is rated from 1 (lowest) to 10 (highest) across six criteria. Notice that Complexity and Risk are inverted during calculation so that a higher composite score always indicates a stronger candidate.

DimensionWeightWhat It Measures1–3 (Low)4–7 (Moderate)8–10 (High)
Severity & Urgency25%Cost of inaction, safety/compliance hazard, or workflow stoppage (Broke / Pain).Minor annoyance; easy workaround exists.Frequent friction; measurable time or cost drain.Mission-critical failure, safety risk, or hard blocker.
Reach & Frequency15%Share of users/systems affected and how often the event occurs.Edge case; affects less than 5% of runs/users.Affects a core cohort weekly or per project.Universal bottleneck occurring daily or continuously.
Benefit & Value Lift20%Upside gain in throughput, quality, revenue, or user delight (Would Be Nice / Pain).Marginal convenience; minimal measurable lift.Clear efficiency or quality improvement (15–40%).Step-change breakthrough (greater than 2x lift or new capability).
Practicality & Strategic Fit15%Alignment with existing architecture, domain priorities, and operational reality.Requires unnatural process change or off-mission pivot.Fits current workflow with minor process adjustment.Seamless fit; leverages existing assets and resources.
Feasibility (Ease of Build)15%Technical readiness and low resource/cost burden to prototype and deploy.Unproven tech; heavy capex or multi-quarter build.Known engineering path; moderate effort/cost.Off-the-shelf or lightweight build; fast turnaround.
Risk & Complexity (Inverted)10%Absence of technical debt, integration fragility, or second-order failure modes.High regression risk, regulatory hurdles, or brittle dependencies.Manageable edge cases; standard testing required.Self-contained, deterministic, and easily reversible.

2. Composite Score Formula & Dynamic Category Calibration

Compute the base Weighted Innovation Score ($S$) out of 10.0:

S = 0.25(Severity) + 0.15(Reach) + 0.20(Benefit) + 0.15(Practicality) + 0.15(Feasibility) + 0.10(Low Risk)

Because "Broke", "Pain", and "Would Be Nice" serve different strategic roles, apply a Category Multiplier or evaluate them against category-adjusted weight profiles when comparing within the same backlog:

  • Broke (1.10x Priority Boost if Severity >= 8): Prevents active bleed or failure propagation; elevates critical structural fixes above speculative features.

  • Pain (1.00x Baseline): Standard balanced weighting between friction removal and implementation effort.

  • Would Be Nice (0.95x Baseline, unless Benefit >= 9 and Feasibility >= 7): Prevents low-impact "nice-to-haves" from crowding out core fixes while still allowing asymmetric Quick Wins and Moonshots to surface.

3. Decision Quadrants & Action Routing

Plot each scored opportunity by Value Score (Severity + Reach + Benefit, normalized 1–10) against Execution Score (Practicality + Feasibility + Low Risk, normalized 1–10):

  • Quadrant I — Quick Wins (Value >= 7.0, Execution >= 7.0): Immediate execution. Assign to next sprint or rapid prototype cycle.

  • Quadrant II — Strategic Bets / Moonshots (Value >= 7.0, Execution less than 7.0): High payoff but complex or risky. Apply TRIZ Ideal Final Result (IFR) to eliminate contradictions and reduce complexity before committing full resources.

  • Quadrant III — Fillers / Incremental Polish (Value less than 7.0, Execution >= 7.0): Low-effort cleanups. Batch together or automate opportunistically when touching adjacent components.

  • Quadrant IV — Distractions / Defer (Value less than 7.0, Execution less than $7.0): Archive or reframe; do not allocate active engineering capacity.

4. Ready-to-Use Evaluation Template (CSV / Markdown)

Copy this table directly into your tracker, spreadsheet, or pipeline schema:

IDOpportunity TitleCategory (Broke / Pain / Would Be Nice)Target System / User GroupSeverity (25%)Reach (15%)Benefit (20%)Practicality (15%)Feasibility (15%)Low Risk (10%)Weighted Score (1–10)QuadrantRecommended Next Action
OPP-01Example: Sensor calibration drift under thermal loadBrokeField Hardware / Ops9788677.75 (8.53 adj.)Quick WinApply TRIZ thermal compensation; pilot fix
OPP-02Example: Manual CSV reformatting between toolsPainData Analysts6979997.85Quick WinAutomate schema bridge script
OPP-03Example: Predictive anomaly pre-alert dashboardWould Be NiceEngineering Leads3697565.85Strategic BetPrototype lightweight alert rule first
OPP-04
OPP-05   


Write a Python script using Pydantic v2 that ingests a CSV of opportunities, validates the 1-10 scores, computes weighted scores and quadrants, and outputs a ranked report.

Below is a complete, standalone Python 3.10+ script using Pydantic v2 (pydantic>=2.0) that ingests an opportunities CSV, strictly validates all 1–10 ratings and categories, computes the base and category-adjusted weighted scores, assigns each opportunity to a decision quadrant, and outputs both a ranked CSV and a formatted Markdown executive report.

score_opportunities.py

#!/usr/bin/env python3
"""
Opportunity Scoring & Prioritization Engine
Ingests a CSV of Pain / Broke / Would-Be-Nice opportunities, validates inputs
via Pydantic v2, calculates composite weighted scores and decision quadrants,
and outputs a ranked CSV and Markdown briefing report.
"""

from __future__ import annotations

import argparse
import csv
from enum import Enum
from pathlib import Path
from typing import Annotated, Literal

from pydantic import BaseModel, Field, ValidationError, computed_field, field_validator


class OpportunityCategory(str, Enum):
    BROKE = "Broke"
    PAIN = "Pain"
    WOULD_BE_NICE = "Would Be Nice"


class DecisionQuadrant(str, Enum):
    QUICK_WIN = "Quick Win"
    STRATEGIC_BET = "Strategic Bet"
    FILLER = "Filler"
    DISTRACTION = "Distraction"


class ScoringWeights(BaseModel):
    """Configurable rubric weights (must sum to 1.0)."""

    severity: float = Field(default=0.25, gt=0, lt=1)
    reach: float = Field(default=0.15, gt=0, lt=1)
    benefit: float = Field(default=0.20, gt=0, lt=1)
    practicality: float = Field(default=0.15, gt=0, lt=1)
    feasibility: float = Field(default=0.15, gt=0, lt=1)
    low_risk: float = Field(default=0.10, gt=0, lt=1)

    def validate_sum(self) -> None:
        total = (
            self.severity
            + self.reach
            + self.benefit
            + self.practicality
            + self.feasibility
            + self.low_risk
        )
        if not (0.999 <= total <= 1.001):
            raise ValueError(f"Scoring weights must sum to 1.0 (got {total:.4f})")


ScoreRating = Annotated[int, Field(ge=1, le=10)]


class OpportunityInput(BaseModel):
    """Schema validating each CSV row."""

    id: str = Field(..., min_length=1, description="Unique identifier (e.g., OPP-01)")
    title: str = Field(..., min_length=3, description="Concise opportunity summary")
    category: OpportunityCategory
    target_system: str = Field(..., min_length=1, description="Affected system or user group")
    severity: ScoreRating
    reach: ScoreRating
    benefit: ScoreRating
    practicality: ScoreRating
    feasibility: ScoreRating
    low_risk: ScoreRating
    recommended_action: str = Field(default="", description="Optional next-step note")

    @field_validator("category", mode="before")
    @classmethod
    def normalize_category(cls, value: str) -> str:
        if not isinstance(value, str):
            return value
        cleaned = value.strip().lower().replace("-", " ").replace("_", " ")
        mapping = {
            "broke": OpportunityCategory.BROKE.value,
            "broken": OpportunityCategory.BROKE.value,
            "pain": OpportunityCategory.PAIN.value,
            "pain point": OpportunityCategory.PAIN.value,
            "would be nice": OpportunityCategory.WOULD_BE_NICE.value,
            "nice to have": OpportunityCategory.WOULD_BE_NICE.value,
            "wbn": OpportunityCategory.WOULD_BE_NICE.value,
        }
        return mapping.get(cleaned, value.strip())


class ScoredOpportunity(OpportunityInput):
    """Enriched opportunity model with computed scores, multipliers, and quadrant routing."""

    weights: ScoringWeights = Field(default_factory=ScoringWeights, exclude=True)

    @computed_field
    @property
    def base_score(self) -> float:
        raw = (
            self.weights.severity * self.severity
            + self.weights.reach * self.reach
            + self.weights.benefit * self.benefit
            + self.weights.practicality * self.practicality
            + self.weights.feasibility * self.feasibility
            + self.weights.low_risk * self.low_risk
        )
        return round(raw, 2)

    @computed_field
    @property
    def category_multiplier(self) -> float:
        if self.category == OpportunityCategory.BROKE and self.severity >= 8:
            return 1.10
        if self.category == OpportunityCategory.WOULD_BE_NICE:
            # Preserve asymmetric quick wins / moonshots without penalty
            if self.benefit >= 9 and self.feasibility >= 7:
                return 1.00
            return 0.95
        return 1.00

    @computed_field
    @property
    def adjusted_score(self) -> float:
        # Cap at 10.00 to preserve 1-10 scale interpretability
        return round(min(10.0, self.base_score * self.category_multiplier), 2)

    @computed_field
    @property
    def value_axis(self) -> float:
        """Normalized 1-10 Value axis (Severity + Reach + Benefit)."""
        val_weight = self.weights.severity + self.weights.reach + self.weights.benefit
        score = (
            self.weights.severity * self.severity
            + self.weights.reach * self.reach
            + self.weights.benefit * self.benefit
        ) / val_weight
        return round(score, 2)

    @computed_field
    @property
    def execution_axis(self) -> float:
        """Normalized 1-10 Execution axis (Practicality + Feasibility + Low Risk)."""
        exec_weight = self.weights.practicality + self.weights.feasibility + self.weights.low_risk
        score = (
            self.weights.practicality * self.practicality
            + self.weights.feasibility * self.feasibility
            + self.weights.low_risk * self.low_risk
        ) / exec_weight
        return round(score, 2)

    @computed_field
    @property
    def quadrant(self) -> DecisionQuadrant:
        high_val = self.value_axis >= 7.0
        high_exec = self.execution_axis >= 7.0
        if high_val and high_exec:
            return DecisionQuadrant.QUICK_WIN
        if high_val and not high_exec:
            return DecisionQuadrant.STRATEGIC_BET
        if not high_val and high_exec:
            return DecisionQuadrant.FILLER
        return DecisionQuadrant.DISTRACTION

    @computed_field
    @property
    def routing_guidance(self) -> str:
        if self.recommended_action.strip():
            return self.recommended_action.strip()
        guidance = {
            DecisionQuadrant.QUICK_WIN: "Execute immediately in next sprint / rapid prototype",
            DecisionQuadrant.STRATEGIC_BET: "Apply TRIZ IFR to reduce complexity before full build",
            DecisionQuadrant.FILLER: "Batch with adjacent maintenance or automate opportunistically",
            DecisionQuadrant.DISTRACTION: "Archive or reframe; do not allocate active capacity",
        }
        return guidance[self.quadrant]


def ingest_and_score_csv(
    csv_path: Path, weights: ScoringWeights | None = None
) -> tuple[list[ScoredOpportunity], list[str]]:
    """Read CSV rows, validate with Pydantic v2, and return sorted results + row errors."""
    active_weights = weights or ScoringWeights()
    active_weights.validate_sum()

    scored: list[ScoredOpportunity] = []
    errors: list[str] = []

    with csv_path.open(mode="r", encoding="utf-8-sig", newline="") as f:
        reader = csv.DictReader(f)
        if not reader.fieldnames:
            raise ValueError(f"CSV file '{csv_path}' is empty or missing headers.")

        # Normalize header keys to lowercase snake_case
        for line_num, raw_row in enumerate(reader, start=2):
            cleaned_row = {
                k.strip().lower().replace(" ", "_"): (v.strip() if v is not None else "")
                for k, v in raw_row.items()
                if k is not None
            }
            # Skip completely blank lines
            if not any(cleaned_row.values()):
                continue

            try:
                validated = OpportunityInput.model_validate(cleaned_row)
                enriched = ScoredOpportunity(
                    **validated.model_dump(),
                    weights=active_weights,
                )
                scored.append(enriched)
            except ValidationError as exc:
                err_msgs = "; ".join(
                    f"{'.'.join(str(loc) for loc in e['loc'])}: {e['msg']}"
                    for e in exc.errors()
                )
                errors.append(f"Row {line_num} ({cleaned_row.get('id', 'UNKNOWN')}): {err_msgs}")

    # Sort descending by adjusted_score, then base_score, then severity
    scored.sort(key=lambda x: (x.adjusted_score, x.base_score, x.severity), reverse=True)
    return scored, errors


def write_ranked_csv(opportunities: list[ScoredOpportunity], output_csv: Path) -> None:
    """Export ranked opportunities with computed fields to CSV."""
    output_csv.parent.mkdir(parents=True, exist_ok=True)
    fieldnames = [
        "rank",
        "id",
        "title",
        "category",
        "target_system",
        "severity",
        "reach",
        "benefit",
        "practicality",
        "feasibility",
        "low_risk",
        "base_score",
        "category_multiplier",
        "adjusted_score",
        "value_axis",
        "execution_axis",
        "quadrant",
        "routing_guidance",
    ]
    with output_csv.open(mode="w", encoding="utf-8", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=fieldnames)
        writer.writeheader()
        for rank, opp in enumerate(opportunities, start=1):
            row = opp.model_dump()
            row["rank"] = rank
            row["category"] = opp.category.value
            row["quadrant"] = opp.quadrant.value
            writer.writerow({k: row[k] for k in fieldnames})


def write_markdown_report(
    opportunities: list[ScoredOpportunity],
    errors: list[str],
    output_md: Path,
) -> None:
    """Generate a ranked executive Markdown report grouped by rank and quadrant."""
    output_md.parent.mkdir(parents=True, exist_ok=True)
    lines: list[str] = [
        "# Ranked Opportunity Evaluation Report",
        "",
        f"- **Total Validated Opportunities:** {len(opportunities)}",
        f"- **Validation Errors Skipped:** {len(errors)}",
        "",
        "## Master Ranked Leaderboard",
        "",
        "| Rank | ID | Category | Opportunity | Target System | Base | Adj. Score | Value / Exec | Quadrant | Recommended Action |",
        "| :---: | :--- | :--- | :--- | :--- | :---: | :---: | :---: | :--- | :--- |",
    ]

    for rank, opp in enumerate(opportunities, start=1):
        lines.append(
            f"| **{rank}** | `{opp.id}` | {opp.category.value} | {opp.title} | "
            f"{opp.target_system} | {opp.base_score:.2f} | **{opp.adjusted_score:.2f}** | "
            f"{opp.value_axis:.1f} / {opp.execution_axis:.1f} | **{opp.quadrant.value}** | "
            f"{opp.routing_guidance} |"
        )

    lines.extend(["", "## Quadrant Breakdown", ""])
    for quad in DecisionQuadrant:
        items = [o for o in opportunities if o.quadrant == quad]
        lines.append(f"### {quad.value} ({len(items)})")
        if not items:
            lines.append("- *No opportunities in this quadrant.*")
        else:
            for item in items:
                lines.append(
                    f"- **`{item.id}` ({item.adjusted_score:.2f}) — {item.title}** "
                    f"[{item.category.value} | {item.target_system}]: {item.routing_guidance}"
                )
        lines.append("")

    if errors:
        lines.extend(["## Validation Warnings / Rejected Rows", ""])
        for err in errors:
            lines.append(f"- `{err}`")
        lines.append("")

    output_md.write_text("\n".join(lines), encoding="utf-8")


def create_sample_csv(sample_path: Path) -> None:
    """Write a starter CSV template if requested."""
    sample_path.parent.mkdir(parents=True, exist_ok=True)
    rows = [
        {
            "id": "OPP-01",
            "title": "Sensor calibration drift under thermal load",
            "category": "Broke",
            "target_system": "Field Hardware",
            "severity": 9,
            "reach": 7,
            "benefit": 8,
            "practicality": 8,
            "feasibility": 6,
            "low_risk": 7,
            "recommended_action": "Apply TRIZ thermal compensation; pilot fix",
        },
        {
            "id": "OPP-02",
            "title": "Manual CSV reformatting between tools",
            "category": "Pain",
            "target_system": "Data Pipeline",
            "severity": 6,
            "reach": 9,
            "benefit": 7,
            "practicality": 9,
            "feasibility": 9,
            "low_risk": 9,
            "recommended_action": "Automate schema bridge script",
        },
        {
            "id": "OPP-03",
            "title": "Predictive anomaly pre-alert dashboard",
            "category": "Would Be Nice",
            "target_system": "Ops Telemetry",
            "severity": 3,
            "reach": 6,
            "benefit": 9,
            "practicality": 7,
            "feasibility": 5,
            "low_risk": 6,
            "recommended_action": "",
        },
    ]
    with sample_path.open("w", encoding="utf-8", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=list(rows[0].keys()))
        writer.writeheader()
        writer.writerows(rows)


def main() -> None:
    parser = argparse.ArgumentParser(description="Score and rank opportunities from CSV.")
    parser.add_argument(
        "input_csv",
        type=Path,
        nargs="?",
        default=Path("opportunities.csv"),
        help="Path to input CSV file (default: opportunities.csv)",
    )
    parser.add_argument(
        "--out-csv",
        type=Path,
        default=Path("output/ranked_opportunities.csv"),
        help="Path for ranked output CSV",
    )
    parser.add_argument(
        "--out-md",
        type=Path,
        default=Path("output/ranked_report.md"),
        help="Path for Markdown report",
    )
    parser.add_argument(
        "--init-sample",
        action="store_true",
        help="Generate a sample input CSV at input_csv and exit",
    )
    args = parser.parse_args()

    if args.init_sample:
        create_sample_csv(args.input_csv)
        print(f"Sample CSV created at: {args.input_csv}")
        return

    if not args.input_csv.exists():
        raise FileNotFoundError(
            f"Input CSV '{args.input_csv}' not found. Run with --init-sample to create one."
        )

    opportunities, errors = ingest_and_score_csv(args.input_csv)
    write_ranked_csv(opportunities, args.out_csv)
    write_markdown_report(opportunities, errors, args.out_md)

    print(f"Processed {len(opportunities)} valid opportunities ({len(errors)} errors).")
    print(f"Ranked CSV : {args.out_csv}")
    print(f"MD Report  : {args.out_md}")


if __name__ == "__main__":
    main()

Usage & Expected CSV Schema

  1. Install dependency:

    pip install "pydantic>=2.0"
    
  2. Generate a starter CSV & run the scorer:

    python score_opportunities.py opportunities.csv --init-sample
    python score_opportunities.py opportunities.csv
    
  3. Required CSV Columns:

    • id (str) — Unique identifier (e.g., OPP-01)

    • title (str) — Short description of the issue or opportunity

    • category (str) — Broke, Pain, or Would Be Nice (case-insensitive; normalizes common variants like pain point or nice-to-have)

    • target_system (str) — Affected subsystem, workflow, or user group

    • severity, reach, benefit, practicality, feasibility, low_risk (int, 1..10) — Validated strictly within bounds; out-of-range rows are logged in the report's Validation Warnings section without crashing the batch

    • recommended_action (optional str) — If left blank, the script automatically populates quadrant-specific routing guidance (including TRIZ IFR prompts for Strategic Bet items)


Extend this Python script so that top-ranked Strategic Bet and Quick Win opportunities are automatically passed through a structured Pydantic v2 TRIZ Ideal Final Result (IFR) analysis step via local Ollama or Gemini.

Below is the extended Python script (score_opportunities.py) that adds an automated TRIZ Ideal Final Result (IFR) analysis stage for top-ranked Strategic Bet and Quick Win opportunities.

What Was Added

  1. Strict Pydantic v2 TRIZ IFR Schema (TRIZIdealFinalResultAnalysis): Enforces structured outputs covering the Target Object & Primary Useful Function (PUF), all four IFR Archetypes (Self-Service, Environmental Leverage, Harm Transformation, Disappearance), an Operational Zone / Operational Time (OZ/OT) Resource Audit, Physical Contradiction & Separation Principle, and 2–3 concrete non-compromising engineering concepts.

  2. Dual Local-First & Cloud Backend (--provider ollama|gemini):

    • Ollama (Default — Offline / Local): Uses ollama Python SDK (or native HTTP fallback) with Pydantic JSON Schema enforcement (format=TRIZIdealFinalResultAnalysis.model_json_schema()) and temperature=0.2 (defaults to gpt-oss:20b or gemma4:31b).

    • Google GenAI SDK (google-genai): Uses client.models.generate_content with response_schema=TRIZIdealFinalResultAnalysis and response_mime_type="application/json".

  3. Structured JSON + Enriched Markdown Export: Saves full machine-readable TRIZ artifacts to output/triz_ifr_analyses.json and appends formatted TRIZ IFR design briefs directly into output/ranked_report.md.

score_opportunities.py

#!/usr/bin/env python3
"""
Opportunity Scoring & Automated TRIZ Ideal Final Result (IFR) Pipeline
1. Ingests a CSV of Pain / Broke / Would-Be-Nice opportunities.
2. Validates 1-10 ratings via Pydantic v2, computes weighted scores and quadrants.
3. Passes top-ranked Strategic Bet and Quick Win opportunities through a structured
   TRIZ Ideal Final Result (IFR) analysis via local Ollama or Google Gemini.
4. Outputs a ranked CSV, structured JSON artifacts, and a Markdown executive report.
"""

from __future__ import annotations

import argparse
import csv
import json
import os
import urllib.request
from enum import Enum
from pathlib import Path
from typing import Annotated, Literal

from pydantic import BaseModel, Field, ValidationError, computed_field, field_validator


# ==============================================================================
# 1. SCORING & QUADRANT SCHEMAS
# ==============================================================================


class OpportunityCategory(str, Enum):
    BROKE = "Broke"
    PAIN = "Pain"
    WOULD_BE_NICE = "Would Be Nice"


class DecisionQuadrant(str, Enum):
    QUICK_WIN = "Quick Win"
    STRATEGIC_BET = "Strategic Bet"
    FILLER = "Filler"
    DISTRACTION = "Distraction"


class ScoringWeights(BaseModel):
    """Configurable rubric weights (must sum to 1.0)."""

    severity: float = Field(default=0.25, gt=0, lt=1)
    reach: float = Field(default=0.15, gt=0, lt=1)
    benefit: float = Field(default=0.20, gt=0, lt=1)
    practicality: float = Field(default=0.15, gt=0, lt=1)
    feasibility: float = Field(default=0.15, gt=0, lt=1)
    low_risk: float = Field(default=0.10, gt=0, lt=1)

    def validate_sum(self) -> None:
        total = (
            self.severity
            + self.reach
            + self.benefit
            + self.practicality
            + self.feasibility
            + self.low_risk
        )
        if not (0.999 <= total <= 1.001):
            raise ValueError(f"Scoring weights must sum to 1.0 (got {total:.4f})")


ScoreRating = Annotated[int, Field(ge=1, le=10)]


class OpportunityInput(BaseModel):
    """Schema validating each CSV row."""

    id: str = Field(..., min_length=1, description="Unique identifier (e.g., OPP-01)")
    title: str = Field(..., min_length=3, description="Concise opportunity summary")
    category: OpportunityCategory
    target_system: str = Field(..., min_length=1, description="Affected system or user group")
    severity: ScoreRating
    reach: ScoreRating
    benefit: ScoreRating
    practicality: ScoreRating
    feasibility: ScoreRating
    low_risk: ScoreRating
    recommended_action: str = Field(default="", description="Optional next-step note")

    @field_validator("category", mode="before")
    @classmethod
    def normalize_category(cls, value: str) -> str:
        if not isinstance(value, str):
            return value
        cleaned = value.strip().lower().replace("-", " ").replace("_", " ")
        mapping = {
            "broke": OpportunityCategory.BROKE.value,
            "broken": OpportunityCategory.BROKE.value,
            "pain": OpportunityCategory.PAIN.value,
            "pain point": OpportunityCategory.PAIN.value,
            "would be nice": OpportunityCategory.WOULD_BE_NICE.value,
            "nice to have": OpportunityCategory.WOULD_BE_NICE.value,
            "wbn": OpportunityCategory.WOULD_BE_NICE.value,
        }
        return mapping.get(cleaned, value.strip())


class ScoredOpportunity(OpportunityInput):
    """Enriched opportunity model with computed scores, multipliers, and quadrant routing."""

    weights: ScoringWeights = Field(default_factory=ScoringWeights, exclude=True)

    @computed_field
    @property
    def base_score(self) -> float:
        raw = (
            self.weights.severity * self.severity
            + self.weights.reach * self.reach
            + self.weights.benefit * self.benefit
            + self.weights.practicality * self.practicality
            + self.weights.feasibility * self.feasibility
            + self.weights.low_risk * self.low_risk
        )
        return round(raw, 2)

    @computed_field
    @property
    def category_multiplier(self) -> float:
        if self.category == OpportunityCategory.BROKE and self.severity >= 8:
            return 1.10
        if self.category == OpportunityCategory.WOULD_BE_NICE:
            if self.benefit >= 9 and self.feasibility >= 7:
                return 1.00
            return 0.95
        return 1.00

    @computed_field
    @property
    def adjusted_score(self) -> float:
        return round(min(10.0, self.base_score * self.category_multiplier), 2)

    @computed_field
    @property
    def value_axis(self) -> float:
        val_weight = self.weights.severity + self.weights.reach + self.weights.benefit
        score = (
            self.weights.severity * self.severity
            + self.weights.reach * self.reach
            + self.weights.benefit * self.benefit
        ) / val_weight
        return round(score, 2)

    @computed_field
    @property
    def execution_axis(self) -> float:
        exec_weight = self.weights.practicality + self.weights.feasibility + self.weights.low_risk
        score = (
            self.weights.practicality * self.practicality
            + self.weights.feasibility * self.feasibility
            + self.weights.low_risk * self.low_risk
        ) / exec_weight
        return round(score, 2)

    @computed_field
    @property
    def quadrant(self) -> DecisionQuadrant:
        high_val = self.value_axis >= 7.0
        high_exec = self.execution_axis >= 7.0
        if high_val and high_exec:
            return DecisionQuadrant.QUICK_WIN
        if high_val and not high_exec:
            return DecisionQuadrant.STRATEGIC_BET
        if not high_val and high_exec:
            return DecisionQuadrant.FILLER
        return DecisionQuadrant.DISTRACTION

    @computed_field
    @property
    def routing_guidance(self) -> str:
        if self.recommended_action.strip():
            return self.recommended_action.strip()
        guidance = {
            DecisionQuadrant.QUICK_WIN: "Execute immediately in next sprint / rapid prototype",
            DecisionQuadrant.STRATEGIC_BET: "Apply TRIZ IFR to reduce complexity before full build",
            DecisionQuadrant.FILLER: "Batch with adjacent maintenance or automate opportunistically",
            DecisionQuadrant.DISTRACTION: "Archive or reframe; do not allocate active capacity",
        }
        return guidance[self.quadrant]


# ==============================================================================
# 2. TRIZ IDEAL FINAL RESULT (IFR) PYDANTIC V2 SCHEMAS
# ==============================================================================


class IFRArchetypes(BaseModel):
    """The four radical TRIZ Ideal Final Result formulations."""

    self_service: str = Field(
        ...,
        description="How the target object or substance performs the useful function by itself.",
    )
    environmental_leverage: str = Field(
        ...,
        description="How the external environment or ambient medium delivers the useful function.",
    )
    harm_transformation: str = Field(
        ...,
        description="How the harmful factor, defect, or waste is converted to perform the function.",
    )
    disappearance: str = Field(
        ...,
        description="How the mechanism vanishes or requires zero footprint while the function remains.",
    )


class ResourceAudit(BaseModel):
    """Latent, free, or underutilized resources in the Operational Zone (OZ) & Time (OT)."""

    substances: list[str] = Field(
        ..., description="Existing feedstocks, debris, air, coatings, data exhaust, or materials."
    )
    fields: list[str] = Field(
        ..., description="Gravity, thermal gradients, pressure, friction, EM fields, or kinetic energy."
    )
    space_and_time: list[str] = Field(
        ..., description="Gaps, micro-voids, idle cycle windows, or transition phases."
    )


class ContradictionResolution(BaseModel):
    """Physical contradiction and TRIZ separation principle resolution."""

    physical_contradiction: str = Field(
        ...,
        description="Parameter X must be high to achieve benefit A, yet low to avoid harm B.",
    )
    separation_principle: Literal[
        "Separation in Time",
        "Separation in Space",
        "Separation between System and Parts (Scale)",
        "Separation upon Condition",
    ]
    resolution_mechanism: str = Field(
        ..., description="Exact mechanism applying the separation principle without compromise."
    )


class ConcreteConcept(BaseModel):
    """Actionable, non-compromising engineering or workflow concept inspired by IFR."""

    concept_name: str = Field(..., description="Short technical title for the concept.")
    mechanism_summary: str = Field(
        ..., description="How it achieves the PUF using free OZ/OT resources without added complexity."
    )
    ideality_gain: str = Field(
        ..., description="Why this increases Useful Functions while cutting Cost or Harm."
    )
    first_validation_step: str = Field(
        ..., description="Concrete 72-hour test, simulation, or prototype to verify feasibility."
    )


class TRIZIdealFinalResultAnalysis(BaseModel):
    """Complete structured TRIZ IFR analysis for a high-priority opportunity."""

    opportunity_id: str
    target_object: str = Field(
        ..., description="Specific physical or logical object receiving the primary action."
    )
    primary_useful_function: str = Field(
        ..., description="Core action stripped of legacy mechanisms (what must happen, not how)."
    )
    ifr_archetypes: IFRArchetypes
    resource_audit: ResourceAudit
    contradiction: ContradictionResolution
    concrete_concepts: list[ConcreteConcept] = Field(..., min_length=2, max_length=3)


# ==============================================================================
# 3. LLM BACKEND ADAPTERS (LOCAL OLLAMA & GOOGLE GEMINI)
# ==============================================================================


TRIZ_SYSTEM_PROMPT = """You are a Principal Systems Architect and TRIZ Master.
Analyze the provided engineering/operational opportunity using the TRIZ Ideal Final Result (IFR) methodology:
1. Strip away existing mechanisms to isolate the Target Object and Primary Useful Function (PUF).
2. Formulate all four radical IFR archetypes (Self-Service, Environmental Leverage, Harm Transformation, Disappearance) where the system disappears yet its function is performed.
3. Audit free, latent resources already inside the Operational Zone (OZ) and Operational Time (OT).
4. Identify the core Physical Contradiction and resolve it via one of the four TRIZ Separation Principles.
5. Synthesize 2 to 3 concrete, non-compromising engineering or software concepts with an immediate validation step.
Return strictly valid JSON matching the requested schema."""


def build_triz_user_prompt(opp: ScoredOpportunity) -> str:
    return (
        f"Opportunity ID: {opp.id}\n"
        f"Title: {opp.title}\n"
        f"Category: {opp.category.value}\n"
        f"Target System / Group: {opp.target_system}\n"
        f"Decision Quadrant: {opp.quadrant.value} (Adjusted Score: {opp.adjusted_score}/10.0)\n"
        f"Scores -> Severity: {opp.severity}/10, Reach: {opp.reach}/10, Benefit: {opp.benefit}/10, "
        f"Practicality: {opp.practicality}/10, Feasibility: {opp.feasibility}/10, Low Risk: {opp.low_risk}/10\n"
        f"Existing Action Note: {opp.routing_guidance}\n\n"
        "Generate a rigorous, domain-specific TRIZ Ideal Final Result (IFR) analysis for this opportunity."
    )


def run_triz_via_ollama(
    opp: ScoredOpportunity,
    model_name: str = "gpt-oss:20b",
    host: str = "http://localhost:11434",
) -> TRIZIdealFinalResultAnalysis:
    """Invoke local Ollama with Pydantic v2 JSON Schema enforcement."""
    schema = TRIZIdealFinalResultAnalysis.model_json_schema()
    user_prompt = build_triz_user_prompt(opp)

    try:
        import ollama  # type: ignore

        client = ollama.Client(host=host)
        response = client.chat(
            model=model_name,
            messages=[
                {"role": "system", "content": TRIZ_SYSTEM_PROMPT},
                {"role": "user", "content": user_prompt},
            ],
            format=schema,
            options={"temperature": 0.2},
        )
        raw_content = response["message"]["content"]
    except ImportError:
        # Zero-dependency fallback to Ollama REST API
        payload = json.dumps(
            {
                "model": model_name,
                "messages": [
                    {"role": "system", "content": TRIZ_SYSTEM_PROMPT},
                    {"role": "user", "content": user_prompt},
                ],
                "format": schema,
                "stream": False,
                "options": {"temperature": 0.2},
            }
        ).encode("utf-8")
        req = urllib.request.Request(
            f"{host.rstrip('/')}/api/chat",
            data=payload,
            headers={"Content-Type": "application/json"},
        )
        with urllib.request.urlopen(req, timeout=180) as resp:
            body = json.loads(resp.read().decode("utf-8"))
            raw_content = body["message"]["content"]

    analysis = TRIZIdealFinalResultAnalysis.model_validate_json(raw_content)
    analysis.opportunity_id = opp.id
    return analysis


def run_triz_via_gemini(
    opp: ScoredOpportunity,
    model_name: str = "gemini-2.5-flash",
) -> TRIZIdealFinalResultAnalysis:
    """Invoke Google GenAI SDK with native Pydantic v2 structured output."""
    from google import genai
    from google.genai import types

    api_key = os.environ.get("GEMINI_API_KEY")
    if not api_key:
        raise EnvironmentError("GEMINI_API_KEY environment variable is required for --provider gemini.")

    client = genai.Client(api_key=api_key)
    response = client.models.generate_content(
        model=model_name,
        contents=build_triz_user_prompt(opp),
        config=types.GenerateContentConfig(
            system_instruction=TRIZ_SYSTEM_PROMPT,
            response_mime_type="application/json",
            response_schema=TRIZIdealFinalResultAnalysis,
            temperature=0.2,
        ),
    )
    if response.parsed is not None:
        analysis = response.parsed
        analysis.opportunity_id = opp.id
        return analysis
    analysis = TRIZIdealFinalResultAnalysis.model_validate_json(response.text)
    analysis.opportunity_id = opp.id
    return analysis


# ==============================================================================
# 4. CSV INGESTION, SCORING & REPORT GENERATION
# ==============================================================================


def ingest_and_score_csv(
    csv_path: Path, weights: ScoringWeights | None = None
) -> tuple[list[ScoredOpportunity], list[str]]:
    """Read CSV rows, validate with Pydantic v2, and return sorted results + row errors."""
    active_weights = weights or ScoringWeights()
    active_weights.validate_sum()

    scored: list[ScoredOpportunity] = []
    errors: list[str] = []

    with csv_path.open(mode="r", encoding="utf-8-sig", newline="") as f:
        reader = csv.DictReader(f)
        if not reader.fieldnames:
            raise ValueError(f"CSV file '{csv_path}' is empty or missing headers.")

        for line_num, raw_row in enumerate(reader, start=2):
            cleaned_row = {
                k.strip().lower().replace(" ", "_"): (v.strip() if v is not None else "")
                for k, v in raw_row.items()
                if k is not None
            }
            if not any(cleaned_row.values()):
                continue

            try:
                validated = OpportunityInput.model_validate(cleaned_row)
                enriched = ScoredOpportunity(
                    **validated.model_dump(),
                    weights=active_weights,
                )
                scored.append(enriched)
            except ValidationError as exc:
                err_msgs = "; ".join(
                    f"{'.'.join(str(loc) for loc in e['loc'])}: {e['msg']}"
                    for e in exc.errors()
                )
                errors.append(f"Row {line_num} ({cleaned_row.get('id', 'UNKNOWN')}): {err_msgs}")

    scored.sort(key=lambda x: (x.adjusted_score, x.base_score, x.severity), reverse=True)
    return scored, errors


def run_triz_stage(
    opportunities: list[ScoredOpportunity],
    provider: Literal["ollama", "gemini", "none"],
    model_name: str,
    ollama_host: str,
    top_k: int,
) -> dict[str, TRIZIdealFinalResultAnalysis]:
    """Filter top-K Strategic Bets and Quick Wins and run structured TRIZ IFR analysis."""
    if provider == "none" or top_k <= 0:
        return {}

    eligible = [
        opp
        for opp in opportunities
        if opp.quadrant in (DecisionQuadrant.STRATEGIC_BET, DecisionQuadrant.QUICK_WIN)
    ][:top_k]

    analyses: dict[str, TRIZIdealFinalResultAnalysis] = {}
    for idx, opp in enumerate(eligible, start=1):
        print(
            f"[{idx}/{len(eligible)}] Running TRIZ IFR ({provider}:{model_name}) "
            f"for {opp.id} ({opp.quadrant.value})..."
        )
        try:
            if provider == "ollama":
                result = run_triz_via_ollama(opp, model_name=model_name, host=ollama_host)
            else:
                result = run_triz_via_gemini(opp, model_name=model_name)
            analyses[opp.id] = result
        except Exception as exc:
            print(f"  [!] TRIZ analysis failed for {opp.id}: {exc}")

    return analyses


def write_ranked_csv(opportunities: list[ScoredOpportunity], output_csv: Path) -> None:
    """Export ranked opportunities with computed fields to CSV."""
    output_csv.parent.mkdir(parents=True, exist_ok=True)
    fieldnames = [
        "rank",
        "id",
        "title",
        "category",
        "target_system",
        "severity",
        "reach",
        "benefit",
        "practicality",
        "feasibility",
        "low_risk",
        "base_score",
        "category_multiplier",
        "adjusted_score",
        "value_axis",
        "execution_axis",
        "quadrant",
        "routing_guidance",
    ]
    with output_csv.open(mode="w", encoding="utf-8", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=fieldnames)
        writer.writeheader()
        for rank, opp in enumerate(opportunities, start=1):
            row = opp.model_dump()
            row["rank"] = rank
            row["category"] = opp.category.value
            row["quadrant"] = opp.quadrant.value
            writer.writerow({k: row[k] for k in fieldnames})


def write_triz_json(
    analyses: dict[str, TRIZIdealFinalResultAnalysis], output_json: Path
) -> None:
    """Export structured TRIZ IFR analyses to JSON."""
    output_json.parent.mkdir(parents=True, exist_ok=True)
    serialized = [item.model_dump() for item in analyses.values()]
    output_json.write_text(json.dumps(serialized, indent=2), encoding="utf-8")


def write_markdown_report(
    opportunities: list[ScoredOpportunity],
    triz_analyses: dict[str, TRIZIdealFinalResultAnalysis],
    errors: list[str],
    output_md: Path,
) -> None:
    """Generate a ranked executive Markdown report with embedded TRIZ IFR briefs."""
    output_md.parent.mkdir(parents=True, exist_ok=True)
    lines: list[str] = [
        "# Ranked Opportunity Evaluation & TRIZ IFR Report",
        "",
        f"- **Total Validated Opportunities:** {len(opportunities)}",
        f"- **TRIZ IFR Deep-Dives Generated:** {len(triz_analyses)}",
        f"- **Validation Errors Skipped:** {len(errors)}",
        "",
        "## Master Ranked Leaderboard",
        "",
        "| Rank | ID | Category | Opportunity | Target System | Base | Adj. Score | Value / Exec | Quadrant | Recommended Action |",
        "| :---: | :--- | :--- | :--- | :--- | :---: | :---: | :---: | :--- | :--- |",
    ]

    for rank, opp in enumerate(opportunities, start=1):
        lines.append(
            f"| **{rank}** | `{opp.id}` | {opp.category.value} | {opp.title} | "
            f"{opp.target_system} | {opp.base_score:.2f} | **{opp.adjusted_score:.2f}** | "
            f"{opp.value_axis:.1f} / {opp.execution_axis:.1f} | **{opp.quadrant.value}** | "
            f"{opp.routing_guidance} |"
        )

    lines.extend(["", "## Quadrant Breakdown", ""])
    for quad in DecisionQuadrant:
        items = [o for o in opportunities if o.quadrant == quad]
        lines.append(f"### {quad.value} ({len(items)})")
        if not items:
            lines.append("- *No opportunities in this quadrant.*")
        else:
            for item in items:
                lines.append(
                    f"- **`{item.id}` ({item.adjusted_score:.2f}) — {item.title}** "
                    f"[{item.category.value} | {item.target_system}]: {item.routing_guidance}"
                )
        lines.append("")

    if triz_analyses:
        lines.extend(["---", "", "## TRIZ Ideal Final Result (IFR) Deep-Dive Analyses", ""])
        opp_lookup = {o.id: o for o in opportunities}
        for opp_id, triz in triz_analyses.items():
            opp = opp_lookup[opp_id]
            lines.extend(
                [
                    f"### `{opp.id}` — {opp.title} ({opp.quadrant.value} | Score: {opp.adjusted_score:.2f})",
                    "",
                    f"- **Target Object:** {triz.target_object}",
                    f"- **Primary Useful Function (PUF):** {triz.primary_useful_function}",
                    "",
                    "#### 1. Four IFR Archetypes",
                    f"- **Self-Service:** {triz.ifr_archetypes.self_service}",
                    f"- **Environmental Leverage:** {triz.ifr_archetypes.environmental_leverage}",
                    f"- **Harm Transformation:** {triz.ifr_archetypes.harm_transformation}",
                    f"- **Disappearance:** {triz.ifr_archetypes.disappearance}",
                    "",
                    "#### 2. Operational Zone & Time (OZ/OT) Resource Audit",
                    f"- **Substances:** {', '.join(triz.resource_audit.substances)}",
                    f"- **Fields:** {', '.join(triz.resource_audit.fields)}",
                    f"- **Space & Time:** {', '.join(triz.resource_audit.space_and_time)}",
                    "",
                    "#### 3. Contradiction & Separation Resolution",
                    f"- **Physical Contradiction:** {triz.contradiction.physical_contradiction}",
                    f"- **Separation Principle:** `{triz.contradiction.separation_principle}`",
                    f"- **Resolution Mechanism:** {triz.contradiction.resolution_mechanism}",
                    "",
                    "#### 4. Non-Compromising Solution Concepts",
                ]
            )
            for c_idx, concept in enumerate(triz.concrete_concepts, start=1):
                lines.extend(
                    [
                        f"{c_idx}. **{concept.concept_name}**",
                        f"   - *Mechanism:* {concept.mechanism_summary}",
                        f"   - *Ideality Gain:* {concept.ideality_gain}",
                        f"   - *First Validation Step:* {concept.first_validation_step}",
                    ]
                )
            lines.append("")

    if errors:
        lines.extend(["## Validation Warnings / Rejected Rows", ""])
        for err in errors:
            lines.append(f"- `{err}`")
        lines.append("")

    output_md.write_text("\n".join(lines), encoding="utf-8")


def create_sample_csv(sample_path: Path) -> None:
    """Write a starter CSV template."""
    sample_path.parent.mkdir(parents=True, exist_ok=True)
    rows = [
        {
            "id": "OPP-01",
            "title": "Sensor calibration drift under thermal load",
            "category": "Broke",
            "target_system": "Field Hardware",
            "severity": 9,
            "reach": 7,
            "benefit": 8,
            "practicality": 8,
            "feasibility": 6,
            "low_risk": 7,
            "recommended_action": "Apply TRIZ thermal compensation; pilot fix",
        },
        {
            "id": "OPP-02",
            "title": "Manual CSV reformatting between tools",
            "category": "Pain",
            "target_system": "Data Pipeline",
            "severity": 6,
            "reach": 9,
            "benefit": 7,
            "practicality": 9,
            "feasibility": 9,
            "low_risk": 9,
            "recommended_action": "Automate schema bridge script",
        },
        {
            "id": "OPP-03",
            "title": "Predictive anomaly pre-alert dashboard",
            "category": "Would Be Nice",
            "target_system": "Ops Telemetry",
            "severity": 3,
            "reach": 6,
            "benefit": 9,
            "practicality": 7,
            "feasibility": 5,
            "low_risk": 6,
            "recommended_action": "",
        },
    ]
    with sample_path.open("w", encoding="utf-8", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=list(rows[0].keys()))
        writer.writeheader()
        writer.writerows(rows)


def main() -> None:
    parser = argparse.ArgumentParser(
        description="Score opportunities from CSV and run TRIZ IFR analysis on top candidates."
    )
    parser.add_argument(
        "input_csv",
        type=Path,
        nargs="?",
        default=Path("opportunities.csv"),
        help="Path to input CSV file (default: opportunities.csv)",
    )
    parser.add_argument(
        "--provider",
        choices=["ollama", "gemini", "none"],
        default="ollama",
        help="LLM backend for TRIZ IFR analysis (default: ollama)",
    )
    parser.add_argument(
        "--model",
        type=str,
        default=None,
        help="Model name (defaults to 'gpt-oss:20b' for ollama, 'gemini-2.5-flash' for gemini)",
    )
    parser.add_argument(
        "--ollama-host",
        type=str,
        default="http://localhost:11434",
        help="Local Ollama endpoint URL (default: http://localhost:11434)",
    )
    parser.add_argument(
        "--top-k",
        type=int,
        default=5,
        help="Max number of top Strategic Bet / Quick Win items to analyze via TRIZ IFR (default: 5)",
    )
    parser.add_argument(
        "--out-csv",
        type=Path,
        default=Path("output/ranked_opportunities.csv"),
        help="Path for ranked output CSV",
    )
    parser.add_argument(
        "--out-json",
        type=Path,
        default=Path("output/triz_ifr_analyses.json"),
        help="Path for structured TRIZ IFR JSON output",
    )
    parser.add_argument(
        "--out-md",
        type=Path,
        default=Path("output/ranked_report.md"),
        help="Path for Markdown report",
    )
    parser.add_argument(
        "--init-sample",
        action="store_true",
        help="Generate a sample input CSV at input_csv and exit",
    )
    args = parser.parse_args()

    if args.init_sample:
        create_sample_csv(args.input_csv)
        print(f"Sample CSV created at: {args.input_csv}")
        return

    if not args.input_csv.exists():
        raise FileNotFoundError(
            f"Input CSV '{args.input_csv}' not found. Run with --init-sample to create one."
        )

    default_model = "gpt-oss:20b" if args.provider == "ollama" else "gemini-2.5-flash"
    active_model = args.model or default_model

    opportunities, errors = ingest_and_score_csv(args.input_csv)
    triz_analyses = run_triz_stage(
        opportunities=opportunities,
        provider=args.provider,
        model_name=active_model,
        ollama_host=args.ollama_host,
        top_k=args.top_k,
    )

    write_ranked_csv(opportunities, args.out_csv)
    write_triz_json(triz_analyses, args.out_json)
    write_markdown_report(opportunities, triz_analyses, errors, args.out_md)

    print(f"\nProcessed {len(opportunities)} valid opportunities ({len(errors)} validation errors).")
    print(f"TRIZ IFR Analyses : {len(triz_analyses)} generated")
    print(f"Ranked CSV        : {args.out_csv}")
    print(f"TRIZ JSON         : {args.out_json}")
    print(f"MD Report         : {args.out_md}")


if __name__ == "__main__":
    main()

Quick-Start Commands

  • 1. Offline Local Execution via Ollama (default gpt-oss:20b):

    pip install "pydantic>=2.0" ollama
    python score_opportunities.py opportunities.csv --init-sample
    python score_opportunities.py opportunities.csv --provider ollama --model gpt-oss:20b --top-k 5
    

    (Note: Even if the ollama Python package is not installed, the script falls back automatically to urllib.request against http://localhost:11434/api/chat.)

  • 2. Cloud Execution via Google GenAI SDK (google-genai):

    pip install "pydantic>=2.0" google-genai
    export GEMINI_API_KEY="your-api-key"
    python score_opportunities.py opportunities.csv --provider gemini --model gemini-2.5-flash --top-k 5
    
  • 3. Scoring-Only Mode (Skip LLM Stage):

    python score_opportunities.py opportunities.csv --provider none


Add a second-stage Critic pass using Pydantic v2 that audits each generated TRIZ concept for second-order failure modes, cost creep, and implementation risks.

Below is the updated Python pipeline (score_opportunities.py) incorporating a second-stage FMEA / Ideality Critic pass using Pydantic v2.

What Was Added in the Critic Stage

  1. Strict Pydantic v2 Audit Schema (TRIZCriticReport & ConceptAudit):

    • Second-Order Failure Modes (FailureModeRisk): Identifies new defects, unintended side effects, or edge-case regressions introduced by each TRIZ concept, scored by severity (1–10) and likelihood (1–10) with an auto-computed Risk Priority (severity * likelihood) and concrete mitigation.

    • Ideality & Cost-Creep Audit (CostCreepAudit): Checks whether the concept genuinely leverages free Operational Zone/Time (OZ/OT) resources or secretly reintroduces hidden hardware, maintenance, calibration, or operational complexity (none | low | moderate | severe).

    • Implementation & Integration Risks: Captures deployment bottlenecks, safety/compliance hurdles, or legacy interface friction.

    • Deterministic Gate & Verdict (APPROVE, REVISE, REJECT): Computes a net_ideality_score (1–10), assigns a disposition verdict, and selects the single highest-leverage surviving concept (recommended_concept_name) with concrete revision directives when needed.

  2. Seamless Dual-Backend Execution: Runs automatically right after the TRIZ IFR generator for each analyzed opportunity via either local Ollama (--provider ollama) or Google GenAI SDK (--provider gemini), outputting both output/triz_critic_audits.json and embedded audit tables inside output/ranked_report.md.

score_opportunities.py

#!/usr/bin/env python3
"""
Opportunity Scoring, Automated TRIZ IFR Generation & FMEA Critic Pipeline
1. Ingests a CSV of Pain / Broke / Would-Be-Nice opportunities.
2. Validates 1-10 ratings via Pydantic v2, computes weighted scores and quadrants.
3. Passes top-ranked Strategic Bet and Quick Win opportunities through a structured
   TRIZ Ideal Final Result (IFR) generator (Stage 1).
4. Audits every generated TRIZ concept through a second-stage FMEA & Ideality Critic
   pass (Stage 2) for second-order failure modes, cost creep, and implementation risks.
5. Outputs a ranked CSV, structured JSON artifacts, and a Markdown executive report.
"""

from __future__ import annotations

import argparse
import csv
import json
import os
import urllib.request
from enum import Enum
from pathlib import Path
from typing import Annotated, Literal

from pydantic import BaseModel, Field, ValidationError, computed_field, field_validator


# ==============================================================================
# 1. SCORING & QUADRANT SCHEMAS
# ==============================================================================


class OpportunityCategory(str, Enum):
    BROKE = "Broke"
    PAIN = "Pain"
    WOULD_BE_NICE = "Would Be Nice"


class DecisionQuadrant(str, Enum):
    QUICK_WIN = "Quick Win"
    STRATEGIC_BET = "Strategic Bet"
    FILLER = "Filler"
    DISTRACTION = "Distraction"


class ScoringWeights(BaseModel):
    """Configurable rubric weights (must sum to 1.0)."""

    severity: float = Field(default=0.25, gt=0, lt=1)
    reach: float = Field(default=0.15, gt=0, lt=1)
    benefit: float = Field(default=0.20, gt=0, lt=1)
    practicality: float = Field(default=0.15, gt=0, lt=1)
    feasibility: float = Field(default=0.15, gt=0, lt=1)
    low_risk: float = Field(default=0.10, gt=0, lt=1)

    def validate_sum(self) -> None:
        total = (
            self.severity
            + self.reach
            + self.benefit
            + self.practicality
            + self.feasibility
            + self.low_risk
        )
        if not (0.999 <= total <= 1.001):
            raise ValueError(f"Scoring weights must sum to 1.0 (got {total:.4f})")


ScoreRating = Annotated[int, Field(ge=1, le=10)]


class OpportunityInput(BaseModel):
    """Schema validating each CSV row."""

    id: str = Field(..., min_length=1, description="Unique identifier (e.g., OPP-01)")
    title: str = Field(..., min_length=3, description="Concise opportunity summary")
    category: OpportunityCategory
    target_system: str = Field(..., min_length=1, description="Affected system or user group")
    severity: ScoreRating
    reach: ScoreRating
    benefit: ScoreRating
    practicality: ScoreRating
    feasibility: ScoreRating
    low_risk: ScoreRating
    recommended_action: str = Field(default="", description="Optional next-step note")

    @field_validator("category", mode="before")
    @classmethod
    def normalize_category(cls, value: str) -> str:
        if not isinstance(value, str):
            return value
        cleaned = value.strip().lower().replace("-", " ").replace("_", " ")
        mapping = {
            "broke": OpportunityCategory.BROKE.value,
            "broken": OpportunityCategory.BROKE.value,
            "pain": OpportunityCategory.PAIN.value,
            "pain point": OpportunityCategory.PAIN.value,
            "would be nice": OpportunityCategory.WOULD_BE_NICE.value,
            "nice to have": OpportunityCategory.WOULD_BE_NICE.value,
            "wbn": OpportunityCategory.WOULD_BE_NICE.value,
        }
        return mapping.get(cleaned, value.strip())


class ScoredOpportunity(OpportunityInput):
    """Enriched opportunity model with computed scores, multipliers, and quadrant routing."""

    weights: ScoringWeights = Field(default_factory=ScoringWeights, exclude=True)

    @computed_field
    @property
    def base_score(self) -> float:
        raw = (
            self.weights.severity * self.severity
            + self.weights.reach * self.reach
            + self.weights.benefit * self.benefit
            + self.weights.practicality * self.practicality
            + self.weights.feasibility * self.feasibility
            + self.weights.low_risk * self.low_risk
        )
        return round(raw, 2)

    @computed_field
    @property
    def category_multiplier(self) -> float:
        if self.category == OpportunityCategory.BROKE and self.severity >= 8:
            return 1.10
        if self.category == OpportunityCategory.WOULD_BE_NICE:
            if self.benefit >= 9 and self.feasibility >= 7:
                return 1.00
            return 0.95
        return 1.00

    @computed_field
    @property
    def adjusted_score(self) -> float:
        return round(min(10.0, self.base_score * self.category_multiplier), 2)

    @computed_field
    @property
    def value_axis(self) -> float:
        val_weight = self.weights.severity + self.weights.reach + self.weights.benefit
        score = (
            self.weights.severity * self.severity
            + self.weights.reach * self.reach
            + self.weights.benefit * self.benefit
        ) / val_weight
        return round(score, 2)

    @computed_field
    @property
    def execution_axis(self) -> float:
        exec_weight = self.weights.practicality + self.weights.feasibility + self.weights.low_risk
        score = (
            self.weights.practicality * self.practicality
            + self.weights.feasibility * self.feasibility
            + self.weights.low_risk * self.low_risk
        ) / exec_weight
        return round(score, 2)

    @computed_field
    @property
    def quadrant(self) -> DecisionQuadrant:
        high_val = self.value_axis >= 7.0
        high_exec = self.execution_axis >= 7.0
        if high_val and high_exec:
            return DecisionQuadrant.QUICK_WIN
        if high_val and not high_exec:
            return DecisionQuadrant.STRATEGIC_BET
        if not high_val and high_exec:
            return DecisionQuadrant.FILLER
        return DecisionQuadrant.DISTRACTION

    @computed_field
    @property
    def routing_guidance(self) -> str:
        if self.recommended_action.strip():
            return self.recommended_action.strip()
        guidance = {
            DecisionQuadrant.QUICK_WIN: "Execute immediately in next sprint / rapid prototype",
            DecisionQuadrant.STRATEGIC_BET: "Apply TRIZ IFR to reduce complexity before full build",
            DecisionQuadrant.FILLER: "Batch with adjacent maintenance or automate opportunistically",
            DecisionQuadrant.DISTRACTION: "Archive or reframe; do not allocate active capacity",
        }
        return guidance[self.quadrant]


# ==============================================================================
# 2. STAGE 1: TRIZ IDEAL FINAL RESULT (IFR) PYDANTIC V2 SCHEMAS
# ==============================================================================


class IFRArchetypes(BaseModel):
    """The four radical TRIZ Ideal Final Result formulations."""

    self_service: str = Field(
        ...,
        description="How the target object or substance performs the useful function by itself.",
    )
    environmental_leverage: str = Field(
        ...,
        description="How the external environment or ambient medium delivers the useful function.",
    )
    harm_transformation: str = Field(
        ...,
        description="How the harmful factor, defect, or waste is converted to perform the function.",
    )
    disappearance: str = Field(
        ...,
        description="How the mechanism vanishes or requires zero footprint while the function remains.",
    )


class ResourceAudit(BaseModel):
    """Latent, free, or underutilized resources in the Operational Zone (OZ) & Time (OT)."""

    substances: list[str] = Field(
        ..., description="Existing feedstocks, debris, air, coatings, data exhaust, or materials."
    )
    fields: list[str] = Field(
        ..., description="Gravity, thermal gradients, pressure, friction, EM fields, or kinetic energy."
    )
    space_and_time: list[str] = Field(
        ..., description="Gaps, micro-voids, idle cycle windows, or transition phases."
    )


class ContradictionResolution(BaseModel):
    """Physical contradiction and TRIZ separation principle resolution."""

    physical_contradiction: str = Field(
        ...,
        description="Parameter X must be high to achieve benefit A, yet low to avoid harm B.",
    )
    separation_principle: Literal[
        "Separation in Time",
        "Separation in Space",
        "Separation between System and Parts (Scale)",
        "Separation upon Condition",
    ]
    resolution_mechanism: str = Field(
        ..., description="Exact mechanism applying the separation principle without compromise."
    )


class ConcreteConcept(BaseModel):
    """Actionable, non-compromising engineering or workflow concept inspired by IFR."""

    concept_name: str = Field(..., description="Short technical title for the concept.")
    mechanism_summary: str = Field(
        ..., description="How it achieves the PUF using free OZ/OT resources without added complexity."
    )
    ideality_gain: str = Field(
        ..., description="Why this increases Useful Functions while cutting Cost or Harm."
    )
    first_validation_step: str = Field(
        ..., description="Concrete 72-hour test, simulation, or prototype to verify feasibility."
    )


class TRIZIdealFinalResultAnalysis(BaseModel):
    """Complete structured TRIZ IFR analysis for a high-priority opportunity."""

    opportunity_id: str
    target_object: str = Field(
        ..., description="Specific physical or logical object receiving the primary action."
    )
    primary_useful_function: str = Field(
        ..., description="Core action stripped of legacy mechanisms (what must happen, not how)."
    )
    ifr_archetypes: IFRArchetypes
    resource_audit: ResourceAudit
    contradiction: ContradictionResolution
    concrete_concepts: list[ConcreteConcept] = Field(..., min_length=2, max_length=3)


# ==============================================================================
# 3. STAGE 2: FMEA & IDEALITY CRITIC PYDANTIC V2 SCHEMAS
# ==============================================================================


class CriticVerdict(str, Enum):
    APPROVE = "APPROVE"
    REVISE = "REVISE"
    REJECT = "REJECT"


class FailureModeRisk(BaseModel):
    """Second-order failure mode introduced by implementing the proposed concept."""

    failure_mode: str = Field(
        ..., description="Specific new fault, side effect, or edge-case regression introduced."
    )
    trigger_condition: str = Field(
        ..., description="Operating state or boundary condition that triggers this failure."
    )
    severity: ScoreRating = Field(..., description="Impact if failure occurs (1-10).")
    likelihood: ScoreRating = Field(..., description="Probability of occurrence (1-10).")
    mitigation: str = Field(
        ..., description="Low-cost fail-safe or design guard that neutralizes the failure mode."
    )

    @computed_field
    @property
    def criticality_index(self) -> int:
        """Severity x Likelihood risk score (1-100)."""
        return self.severity * self.likelihood


class CostCreepAudit(BaseModel):
    """Audit of hidden hardware, maintenance, compute, or operational overhead."""

    creep_level: Literal["none", "low", "moderate", "severe"]
    hidden_cost_drivers: list[str] = Field(
        ...,
        description="Unstated CapEx, OpEx, calibration, licensing, or maintenance burdens.",
    )
    true_resource_dependency: str = Field(
        ...,
        description="Whether it genuinely uses free OZ/OT resources or smuggles in new components.",
    )


class ConceptAudit(BaseModel):
    """Rigorous engineering & FMEA critique of a single TRIZ concept."""

    concept_name: str = Field(..., description="Must match the audited ConcreteConcept name.")
    verdict: CriticVerdict
    net_ideality_score: ScoreRating = Field(
        ...,
        description="Post-audit Ideality score (1-10) after penalizing hidden costs and failure modes.",
    )
    second_order_failures: list[FailureModeRisk] = Field(
        ..., min_length=1, max_length=3
    )
    cost_creep: CostCreepAudit
    implementation_risks: list[str] = Field(
        ...,
        min_length=1,
        max_length=3,
        description="Integration bottlenecks, safety/compliance hurdles, or adoption friction.",
    )
    revision_directive: str = Field(
        ...,
        description="Exact modification required before pilot (or 'Ready for pilot as specified' if APPROVED).",
    )


class TRIZCriticReport(BaseModel):
    """Stage-2 Critic audit covering all generated TRIZ concepts for an opportunity."""

    opportunity_id: str
    concept_audits: list[ConceptAudit] = Field(..., min_length=2, max_length=3)
    recommended_concept_name: str = Field(
        ...,
        description="The single highest-ideality surviving concept to advance to prototyping.",
    )
    selection_rationale: str = Field(
        ...,
        description="Why this concept beats alternatives on net ideality, risk, and zero cost creep.",
    )


# ==============================================================================
# 4. LLM BACKEND ADAPTERS (LOCAL OLLAMA & GOOGLE GEMINI)
# ==============================================================================


TRIZ_SYSTEM_PROMPT = """You are a Principal Systems Architect and TRIZ Master.
Analyze the provided engineering/operational opportunity using the TRIZ Ideal Final Result (IFR) methodology:
1. Strip away existing mechanisms to isolate the Target Object and Primary Useful Function (PUF).
2. Formulate all four radical IFR archetypes (Self-Service, Environmental Leverage, Harm Transformation, Disappearance) where the system disappears yet its function is performed.
3. Audit free, latent resources already inside the Operational Zone (OZ) and Operational Time (OT).
4. Identify the core Physical Contradiction and resolve it via one of the four TRIZ Separation Principles.
5. Synthesize 2 to 3 concrete, non-compromising engineering or software concepts with an immediate validation step.
Return strictly valid JSON matching the requested schema."""


CRITIC_SYSTEM_PROMPT = """You are a skeptical Principal Systems Safety & FMEA Auditor reviewing proposed TRIZ Ideal Final Result (IFR) concepts.
Your job is to catch blind spots BEFORE engineering resources are spent:
1. Second-Order Failure Modes: What new failure modes, cascading faults, or edge-case regressions does each concept introduce? Rate severity (1-10) and likelihood (1-10) realistically.
2. Cost & Complexity Creep: Does the concept truly rely on free Operational Zone/Time (OZ/OT) resources, or does it secretly introduce extra sensors, calibration loops, compute overhead, or maintenance debt?
3. Implementation Risks: Identify integration friction, regulatory/safety hurdles, or operational bottlenecks.
4. Assign a strict verdict (APPROVE, REVISE, or REJECT) and a net_ideality_score (1-10) for each concept, then select the single strongest surviving concept to advance.
Return strictly valid JSON matching the requested schema."""


def build_triz_user_prompt(opp: ScoredOpportunity) -> str:
    return (
        f"Opportunity ID: {opp.id}\n"
        f"Title: {opp.title}\n"
        f"Category: {opp.category.value}\n"
        f"Target System / Group: {opp.target_system}\n"
        f"Decision Quadrant: {opp.quadrant.value} (Adjusted Score: {opp.adjusted_score}/10.0)\n"
        f"Scores -> Severity: {opp.severity}/10, Reach: {opp.reach}/10, Benefit: {opp.benefit}/10, "
        f"Practicality: {opp.practicality}/10, Feasibility: {opp.feasibility}/10, Low Risk: {opp.low_risk}/10\n"
        f"Existing Action Note: {opp.routing_guidance}\n\n"
        "Generate a rigorous, domain-specific TRIZ Ideal Final Result (IFR) analysis for this opportunity."
    )


def build_critic_user_prompt(
    opp: ScoredOpportunity, triz: TRIZIdealFinalResultAnalysis
) -> str:
    return (
        f"Original Opportunity ({opp.id}): {opp.title} [{opp.category.value} | {opp.target_system}]\n"
        f"Baseline Risk Score: {opp.low_risk}/10 | Feasibility Score: {opp.feasibility}/10\n\n"
        "Generated TRIZ IFR Analysis & Candidate Concepts (JSON):\n"
        f"{triz.model_dump_json(indent=2)}\n\n"
        "Audit every candidate concept in `concrete_concepts` for second-order failure modes, "
        "hidden cost/complexity creep, and implementation risks. Be candid and rigorous."
    )


def _invoke_ollama_structured(
    system_prompt: str,
    user_prompt: str,
    schema_cls: type[BaseModel],
    model_name: str,
    host: str,
) -> str:
    """Shared Ollama structured JSON invocation with HTTP fallback."""
    schema = schema_cls.model_json_schema()
    try:
        import ollama  # type: ignore

        client = ollama.Client(host=host)
        response = client.chat(
            model=model_name,
            messages=[
                {"role": "system", "content": system_prompt},
                {"role": "user", "content": user_prompt},
            ],
            format=schema,
            options={"temperature": 0.2},
        )
        return response["message"]["content"]
    except ImportError:
        payload = json.dumps(
            {
                "model": model_name,
                "messages": [
                    {"role": "system", "content": system_prompt},
                    {"role": "user", "content": user_prompt},
                ],
                "format": schema,
                "stream": False,
                "options": {"temperature": 0.2},
            }
        ).encode("utf-8")
        req = urllib.request.Request(
            f"{host.rstrip('/')}/api/chat",
            data=payload,
            headers={"Content-Type": "application/json"},
        )
        with urllib.request.urlopen(req, timeout=180) as resp:
            body = json.loads(resp.read().decode("utf-8"))
            return body["message"]["content"]


def _invoke_gemini_structured(
    system_prompt: str,
    user_prompt: str,
    schema_cls: type[BaseModel],
    model_name: str,
) -> BaseModel:
    """Shared Google GenAI SDK native Pydantic structured output invocation."""
    from google import genai
    from google.genai import types

    api_key = os.environ.get("GEMINI_API_KEY")
    if not api_key:
        raise EnvironmentError(
            "GEMINI_API_KEY environment variable is required for --provider gemini."
        )

    client = genai.Client(api_key=api_key)
    response = client.models.generate_content(
        model=model_name,
        contents=user_prompt,
        config=types.GenerateContentConfig(
            system_instruction=system_prompt,
            response_mime_type="application/json",
            response_schema=schema_cls,
            temperature=0.2,
        ),
    )
    if response.parsed is not None:
        return response.parsed
    return schema_cls.model_validate_json(response.text)


def run_triz_generation(
    opp: ScoredOpportunity,
    provider: Literal["ollama", "gemini"],
    model_name: str,
    ollama_host: str,
) -> TRIZIdealFinalResultAnalysis:
    user_prompt = build_triz_user_prompt(opp)
    if provider == "ollama":
        raw = _invoke_ollama_structured(
            TRIZ_SYSTEM_PROMPT,
            user_prompt,
            TRIZIdealFinalResultAnalysis,
            model_name,
            ollama_host,
        )
        analysis = TRIZIdealFinalResultAnalysis.model_validate_json(raw)
    else:
        analysis = _invoke_gemini_structured(
            TRIZ_SYSTEM_PROMPT,
            user_prompt,
            TRIZIdealFinalResultAnalysis,
            model_name,
        )  # type: ignore[assignment]
    analysis.opportunity_id = opp.id
    return analysis


def run_critic_audit(
    opp: ScoredOpportunity,
    triz: TRIZIdealFinalResultAnalysis,
    provider: Literal["ollama", "gemini"],
    model_name: str,
    ollama_host: str,
) -> TRIZCriticReport:
    user_prompt = build_critic_user_prompt(opp, triz)
    if provider == "ollama":
        raw = _invoke_ollama_structured(
            CRITIC_SYSTEM_PROMPT,
            user_prompt,
            TRIZCriticReport,
            model_name,
            ollama_host,
        )
        report = TRIZCriticReport.model_validate_json(raw)
    else:
        report = _invoke_gemini_structured(
            CRITIC_SYSTEM_PROMPT,
            user_prompt,
            TRIZCriticReport,
            model_name,
        )  # type: ignore[assignment]
    report.opportunity_id = opp.id
    return report


# ==============================================================================
# 5. CSV INGESTION, PIPELINE ORCHESTRATION & REPORT GENERATION
# ==============================================================================


def ingest_and_score_csv(
    csv_path: Path, weights: ScoringWeights | None = None
) -> tuple[list[ScoredOpportunity], list[str]]:
    """Read CSV rows, validate with Pydantic v2, and return sorted results + row errors."""
    active_weights = weights or ScoringWeights()
    active_weights.validate_sum()

    scored: list[ScoredOpportunity] = []
    errors: list[str] = []

    with csv_path.open(mode="r", encoding="utf-8-sig", newline="") as f:
        reader = csv.DictReader(f)
        if not reader.fieldnames:
            raise ValueError(f"CSV file '{csv_path}' is empty or missing headers.")

        for line_num, raw_row in enumerate(reader, start=2):
            cleaned_row = {
                k.strip().lower().replace(" ", "_"): (v.strip() if v is not None else "")
                for k, v in raw_row.items()
                if k is not None
            }
            if not any(cleaned_row.values()):
                continue

            try:
                validated = OpportunityInput.model_validate(cleaned_row)
                enriched = ScoredOpportunity(
                    **validated.model_dump(),
                    weights=active_weights,
                )
                scored.append(enriched)
            except ValidationError as exc:
                err_msgs = "; ".join(
                    f"{'.'.join(str(loc) for loc in e['loc'])}: {e['msg']}"
                    for e in exc.errors()
                )
                errors.append(f"Row {line_num} ({cleaned_row.get('id', 'UNKNOWN')}): {err_msgs}")

    scored.sort(key=lambda x: (x.adjusted_score, x.base_score, x.severity), reverse=True)
    return scored, errors


def run_triz_and_critic_stages(
    opportunities: list[ScoredOpportunity],
    provider: Literal["ollama", "gemini", "none"],
    model_name: str,
    critic_model_name: str,
    ollama_host: str,
    top_k: int,
    skip_critic: bool = False,
) -> tuple[dict[str, TRIZIdealFinalResultAnalysis], dict[str, TRIZCriticReport]]:
    """Run Stage-1 TRIZ IFR generation and Stage-2 Critic audit on top-K candidates."""
    if provider == "none" or top_k <= 0:
        return {}, {}

    eligible = [
        opp
        for opp in opportunities
        if opp.quadrant in (DecisionQuadrant.STRATEGIC_BET, DecisionQuadrant.QUICK_WIN)
    ][:top_k]

    triz_analyses: dict[str, TRIZIdealFinalResultAnalysis] = {}
    critic_reports: dict[str, TRIZCriticReport] = {}

    for idx, opp in enumerate(eligible, start=1):
        print(
            f"[{idx}/{len(eligible)}] Stage 1 — TRIZ IFR ({provider}:{model_name}) "
            f"for {opp.id} ({opp.quadrant.value})..."
        )
        try:
            triz_result = run_triz_generation(
                opp=opp,
                provider=provider,
                model_name=model_name,
                ollama_host=ollama_host,
            )
            triz_analyses[opp.id] = triz_result
        except Exception as exc:
            print(f"  [!] Stage 1 TRIZ analysis failed for {opp.id}: {exc}")
            continue

        if not skip_critic:
            print(
                f"[{idx}/{len(eligible)}] Stage 2 — FMEA Critic ({provider}:{critic_model_name}) "
                f"auditing {len(triz_result.concrete_concepts)} concepts for {opp.id}..."
            )
            try:
                critic_result = run_critic_audit(
                    opp=opp,
                    triz=triz_result,
                    provider=provider,
                    model_name=critic_model_name,
                    ollama_host=ollama_host,
                )
                critic_reports[opp.id] = critic_result
            except Exception as exc:
                print(f"  [!] Stage 2 Critic audit failed for {opp.id}: {exc}")

    return triz_analyses, critic_reports


def write_ranked_csv(
    opportunities: list[ScoredOpportunity],
    critic_reports: dict[str, TRIZCriticReport],
    output_csv: Path,
) -> None:
    """Export ranked opportunities with computed fields and Critic winner to CSV."""
    output_csv.parent.mkdir(parents=True, exist_ok=True)
    fieldnames = [
        "rank",
        "id",
        "title",
        "category",
        "target_system",
        "severity",
        "reach",
        "benefit",
        "practicality",
        "feasibility",
        "low_risk",
        "base_score",
        "category_multiplier",
        "adjusted_score",
        "value_axis",
        "execution_axis",
        "quadrant",
        "routing_guidance",
        "critic_selected_concept",
    ]
    with output_csv.open(mode="w", encoding="utf-8", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=fieldnames)
        writer.writeheader()
        for rank, opp in enumerate(opportunities, start=1):
            row = opp.model_dump()
            row["rank"] = rank
            row["category"] = opp.category.value
            row["quadrant"] = opp.quadrant.value
            critic = critic_reports.get(opp.id)
            row["critic_selected_concept"] = (
                critic.recommended_concept_name if critic else ""
            )
            writer.writerow({k: row[k] for k in fieldnames})


def write_json_artifact(items: list[BaseModel], output_json: Path) -> None:
    """Export a list of Pydantic models to formatted JSON."""
    output_json.parent.mkdir(parents=True, exist_ok=True)
    serialized = [item.model_dump() for item in items]
    output_json.write_text(json.dumps(serialized, indent=2), encoding="utf-8")


def write_markdown_report(
    opportunities: list[ScoredOpportunity],
    triz_analyses: dict[str, TRIZIdealFinalResultAnalysis],
    critic_reports: dict[str, TRIZCriticReport],
    errors: list[str],
    output_md: Path,
) -> None:
    """Generate a ranked executive Markdown report with embedded TRIZ IFR & Critic audits."""
    output_md.parent.mkdir(parents=True, exist_ok=True)
    lines: list[str] = [
        "# Ranked Opportunity Evaluation, TRIZ IFR & FMEA Critic Report",
        "",
        f"- **Total Validated Opportunities:** {len(opportunities)}",
        f"- **Stage 1 TRIZ IFR Analyses:** {len(triz_analyses)}",
        f"- **Stage 2 FMEA Critic Audits:** {len(critic_reports)}",
        f"- **Validation Errors Skipped:** {len(errors)}",
        "",
        "## Master Ranked Leaderboard",
        "",
        "| Rank | ID | Category | Opportunity | Target System | Base | Adj. Score | Value / Exec | Quadrant | Top Critic-Vetted Concept |",
        "| :---: | :--- | :--- | :--- | :--- | :---: | :---: | :---: | :--- | :--- |",
    ]

    for rank, opp in enumerate(opportunities, start=1):
        critic = critic_reports.get(opp.id)
        vetted = (
            f"**{critic.recommended_concept_name}**"
            if critic
            else opp.routing_guidance
        )
        lines.append(
            f"| **{rank}** | `{opp.id}` | {opp.category.value} | {opp.title} | "
            f"{opp.target_system} | {opp.base_score:.2f} | **{opp.adjusted_score:.2f}** | "
            f"{opp.value_axis:.1f} / {opp.execution_axis:.1f} | **{opp.quadrant.value}** | "
            f"{vetted} |"
        )

    lines.extend(["", "## Quadrant Breakdown", ""])
    for quad in DecisionQuadrant:
        items = [o for o in opportunities if o.quadrant == quad]
        lines.append(f"### {quad.value} ({len(items)})")
        if not items:
            lines.append("- *No opportunities in this quadrant.*")
        else:
            for item in items:
                lines.append(
                    f"- **`{item.id}` ({item.adjusted_score:.2f}) — {item.title}** "
                    f"[{item.category.value} | {item.target_system}]: {item.routing_guidance}"
                )
        lines.append("")

    if triz_analyses:
        lines.extend(
            ["---", "", "## TRIZ Ideal Final Result (IFR) & Critic Audit Dossiers", ""]
        )
        opp_lookup = {o.id: o for o in opportunities}
        for opp_id, triz in triz_analyses.items():
            opp = opp_lookup[opp_id]
            critic = critic_reports.get(opp_id)
            lines.extend(
                [
                    f"### `{opp.id}` — {opp.title} ({opp.quadrant.value} | Score: {opp.adjusted_score:.2f})",
                    "",
                    f"- **Target Object:** {triz.target_object}",
                    f"- **Primary Useful Function (PUF):** {triz.primary_useful_function}",
                    "",
                    "#### 1. Four IFR Archetypes",
                    f"- **Self-Service:** {triz.ifr_archetypes.self_service}",
                    f"- **Environmental Leverage:** {triz.ifr_archetypes.environmental_leverage}",
                    f"- **Harm Transformation:** {triz.ifr_archetypes.harm_transformation}",
                    f"- **Disappearance:** {triz.ifr_archetypes.disappearance}",
                    "",
                    "#### 2. Operational Zone & Time (OZ/OT) Resource Audit",
                    f"- **Substances:** {', '.join(triz.resource_audit.substances)}",
                    f"- **Fields:** {', '.join(triz.resource_audit.fields)}",
                    f"- **Space & Time:** {', '.join(triz.resource_audit.space_and_time)}",
                    "",
                    "#### 3. Contradiction & Separation Resolution",
                    f"- **Physical Contradiction:** {triz.contradiction.physical_contradiction}",
                    f"- **Separation Principle:** `{triz.contradiction.separation_principle}`",
                    f"- **Resolution Mechanism:** {triz.contradiction.resolution_mechanism}",
                    "",
                    "#### 4. Candidate Concepts & Stage-2 FMEA Critic Audit",
                ]
            )

            audit_lookup = (
                {a.concept_name.lower(): a for a in critic.concept_audits}
                if critic
                else {}
            )

            for c_idx, concept in enumerate(triz.concrete_concepts, start=1):
                audit = audit_lookup.get(concept.concept_name.lower())
                verdict_badge = (
                    f" — **[{audit.verdict.value} | Net Ideality: {audit.net_ideality_score}/10]**"
                    if audit
                    else ""
                )
                lines.extend(
                    [
                        f"{c_idx}. **{concept.concept_name}**{verdict_badge}",
                        f"   - *Mechanism:* {concept.mechanism_summary}",
                        f"   - *Ideality Gain:* {concept.ideality_gain}",
                        f"   - *First Validation Step:* {concept.first_validation_step}",
                    ]
                )
                if audit:
                    lines.append(
                        f"   - *Cost Creep (`{audit.cost_creep.creep_level}`):* "
                        f"{audit.cost_creep.true_resource_dependency} "
                        f"(Drivers: {', '.join(audit.cost_creep.hidden_cost_drivers) or 'None'})"
                    )
                    for fm in audit.second_order_failures:
                        lines.append(
                            f"   - *2nd-Order Failure (Criticality {fm.criticality_index}/100 — "
                            f"S:{fm.severity} x L:{fm.likelihood}):* **{fm.failure_mode}** "
                            f"when *{fm.trigger_condition}* → **Mitigation:** {fm.mitigation}"
                        )
                    lines.append(
                        f"   - *Implementation Risks:* {'; '.join(audit.implementation_risks)}"
                    )
                    lines.append(
                        f"   - *Critic Directive:* {audit.revision_directive}"
                    )

            if critic:
                lines.extend(
                    [
                        "",
                        f"> **Critic Final Selection:** `{critic.recommended_concept_name}` — "
                        f"{critic.selection_rationale}",
                    ]
                )
            lines.append("")

    if errors:
        lines.extend(["## Validation Warnings / Rejected Rows", ""])
        for err in errors:
            lines.append(f"- `{err}`")
        lines.append("")

    output_md.write_text("\n".join(lines), encoding="utf-8")


def create_sample_csv(sample_path: Path) -> None:
    """Write a starter CSV template."""
    sample_path.parent.mkdir(parents=True, exist_ok=True)
    rows = [
        {
            "id": "OPP-01",
            "title": "Sensor calibration drift under thermal load",
            "category": "Broke",
            "target_system": "Field Hardware",
            "severity": 9,
            "reach": 7,
            "benefit": 8,
            "practicality": 8,
            "feasibility": 6,
            "low_risk": 7,
            "recommended_action": "Apply TRIZ thermal compensation; pilot fix",
        },
        {
            "id": "OPP-02",
            "title": "Manual CSV reformatting between tools",
            "category": "Pain",
            "target_system": "Data Pipeline",
            "severity": 6,
            "reach": 9,
            "benefit": 7,
            "practicality": 9,
            "feasibility": 9,
            "low_risk": 9,
            "recommended_action": "Automate schema bridge script",
        },
        {
            "id": "OPP-03",
            "title": "Predictive anomaly pre-alert dashboard",
            "category": "Would Be Nice",
            "target_system": "Ops Telemetry",
            "severity": 3,
            "reach": 6,
            "benefit": 9,
            "practicality": 7,
            "feasibility": 5,
            "low_risk": 6,
            "recommended_action": "",
        },
    ]
    with sample_path.open("w", encoding="utf-8", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=list(rows[0].keys()))
        writer.writeheader()
        writer.writerows(rows)


def main() -> None:
    parser = argparse.ArgumentParser(
        description="Score opportunities, generate TRIZ IFR concepts, and run FMEA Critic audits."
    )
    parser.add_argument(
        "input_csv",
        type=Path,
        nargs="?",
        default=Path("opportunities.csv"),
        help="Path to input CSV file (default: opportunities.csv)",
    )
    parser.add_argument(
        "--provider",
        choices=["ollama", "gemini", "none"],
        default="ollama",
        help="LLM backend for TRIZ IFR & Critic stages (default: ollama)",
    )
    parser.add_argument(
        "--model",
        type=str,
        default=None,
        help="Primary TRIZ model (defaults to 'gpt-oss:20b' for ollama, 'gemini-2.5-flash' for gemini)",
    )
    parser.add_argument(
        "--critic-model",
        type=str,
        default=None,
        help="Optional separate model for Stage-2 Critic pass (defaults to --model)",
    )
    parser.add_argument(
        "--ollama-host",
        type=str,
        default="http://localhost:11434",
        help="Local Ollama endpoint URL (default: http://localhost:11434)",
    )
    parser.add_argument(
        "--top-k",
        type=int,
        default=5,
        help="Max number of top Strategic Bet / Quick Win items to analyze (default: 5)",
    )
    parser.add_argument(
        "--skip-critic",
        action="store_true",
        help="Run Stage-1 TRIZ IFR generation only without Stage-2 FMEA Critic pass",
    )
    parser.add_argument(
        "--out-csv",
        type=Path,
        default=Path("output/ranked_opportunities.csv"),
        help="Path for ranked output CSV",
    )
    parser.add_argument(
        "--out-triz-json",
        type=Path,
        default=Path("output/triz_ifr_analyses.json"),
        help="Path for Stage-1 TRIZ IFR JSON output",
    )
    parser.add_argument(
        "--out-critic-json",
        type=Path,
        default=Path("output/triz_critic_audits.json"),
        help="Path for Stage-2 Critic FMEA JSON output",
    )
    parser.add_argument(
        "--out-md",
        type=Path,
        default=Path("output/ranked_report.md"),
        help="Path for Markdown report",
    )
    parser.add_argument(
        "--init-sample",
        action="store_true",
        help="Generate a sample input CSV at input_csv and exit",
    )
    args = parser.parse_args()

    if args.init_sample:
        create_sample_csv(args.input_csv)
        print(f"Sample CSV created at: {args.input_csv}")
        return

    if not args.input_csv.exists():
        raise FileNotFoundError(
            f"Input CSV '{args.input_csv}' not found. Run with --init-sample to create one."
        )

    default_model = "gpt-oss:20b" if args.provider == "ollama" else "gemini-2.5-flash"
    active_model = args.model or default_model
    active_critic_model = args.critic_model or active_model

    opportunities, errors = ingest_and_score_csv(args.input_csv)
    triz_analyses, critic_reports = run_triz_and_critic_stages(
        opportunities=opportunities,
        provider=args.provider,
        model_name=active_model,
        critic_model_name=active_critic_model,
        ollama_host=args.ollama_host,
        top_k=args.top_k,
        skip_critic=args.skip_critic,
    )

    write_ranked_csv(opportunities, critic_reports, args.out_csv)
    write_json_artifact(list(triz_analyses.values()), args.out_triz_json)
    write_json_artifact(list(critic_reports.values()), args.out_critic_json)
    write_markdown_report(
        opportunities, triz_analyses, critic_reports, errors, args.out_md
    )

    print(f"\nProcessed {len(opportunities)} valid opportunities ({len(errors)} validation errors).")
    print(f"Stage 1 TRIZ IFR  : {len(triz_analyses)} generated -> {args.out_triz_json}")
    print(f"Stage 2 Critic    : {len(critic_reports)} audited   -> {args.out_critic_json}")
    print(f"Ranked CSV        : {args.out_csv}")
    print(f"MD Report         : {args.out_md}")


if __name__ == "__main__":
    main()

Example Commands (Including Split Generator / Critic Models)

  • Local Ollama with separate Generator & Critic models (e.g., generating with gpt-oss:20b and auditing with gemma4:31b):

    python score_opportunities.py opportunities.csv \
      --provider ollama \
      --model gpt-oss:20b \
      --critic-model gemma4:31b \
      --top-k 5
    
  • Google GenAI SDK (google-genai):

    python score_opportunities.py opportunities.csv \
      --provider gemini \
      --model gemini-2.5-flash \
      --critic-model gemini-2.5-pro \
      --top-k 5


Persist scored opportunities, TRIZ IFRs, and FMEA audits to SQLite

Below is the updated score_opportunities.py script with normalized SQLite3 persistence (--db-path, defaulting to output/opportunities_pipeline.db).

What Was Added for SQLite Persistence

  1. Normalized Relational Schema (init_sqlite_db):

    • pipeline_runs — Tracks every execution timestamp, source CSV path, provider, models used, and row counts.

    • opportunities — Stores validated opportunities, scores, multipliers, axes, and quadrant assignments (UPSERT on id so re-runs update cleanly without duplicating rows).

    • triz_analyses & triz_concepts — Persists the Stage-1 TRIZ IFR target object, PUF, all four IFR archetypes, OZ/OT resource audit, contradiction/separation resolution, and each generated candidate concept.

    • critic_audits, concept_audits, & second_order_failures — Persists Stage-2 FMEA Critic verdicts (APPROVE / REVISE / REJECT), net ideality scores, cost-creep levels, implementation risks, and individual second-order failure modes with their severity * likelihood criticality index.

  2. Idempotent Re-Runs (--skip-existing): Optionally skips LLM Stage-1/Stage-2 calls for opportunities that already have a TRIZ IFR + Critic audit stored in SQLite—preventing redundant local inference or API calls when adding new rows to your CSV.

  3. Summary Query Helper (--query-summary): Prints a quick SQLite portfolio summary of stored opportunities, quadrant distribution, and approved concepts without running a full pipeline pass.

score_opportunities.py

#!/usr/bin/env python3
"""
Opportunity Scoring, Automated TRIZ IFR Generation, FMEA Critic & SQLite Persistence Pipeline
1. Ingests a CSV of Pain / Broke / Would-Be-Nice opportunities.
2. Validates 1-10 ratings via Pydantic v2, computes weighted scores and quadrants.
3. Passes top-ranked Strategic Bet and Quick Win opportunities through a structured
   TRIZ Ideal Final Result (IFR) generator (Stage 1).
4. Audits every generated TRIZ concept through a second-stage FMEA & Ideality Critic
   pass (Stage 2) for second-order failure modes, cost creep, and implementation risks.
5. Persists all runs, opportunities, TRIZ IFRs, concepts, and FMEA audits to SQLite3,
   and exports ranked CSV, JSON artifacts, and a Markdown executive report.
"""

from __future__ import annotations

import argparse
import csv
import json
import os
import sqlite3
import urllib.request
from datetime import datetime, timezone
from enum import Enum
from pathlib import Path
from typing import Annotated, Literal

from pydantic import BaseModel, Field, ValidationError, computed_field, field_validator


# ==============================================================================
# 1. SCORING & QUADRANT SCHEMAS
# ==============================================================================


class OpportunityCategory(str, Enum):
    BROKE = "Broke"
    PAIN = "Pain"
    WOULD_BE_NICE = "Would Be Nice"


class DecisionQuadrant(str, Enum):
    QUICK_WIN = "Quick Win"
    STRATEGIC_BET = "Strategic Bet"
    FILLER = "Filler"
    DISTRACTION = "Distraction"


class ScoringWeights(BaseModel):
    """Configurable rubric weights (must sum to 1.0)."""

    severity: float = Field(default=0.25, gt=0, lt=1)
    reach: float = Field(default=0.15, gt=0, lt=1)
    benefit: float = Field(default=0.20, gt=0, lt=1)
    practicality: float = Field(default=0.15, gt=0, lt=1)
    feasibility: float = Field(default=0.15, gt=0, lt=1)
    low_risk: float = Field(default=0.10, gt=0, lt=1)

    def validate_sum(self) -> None:
        total = (
            self.severity
            + self.reach
            + self.benefit
            + self.practicality
            + self.feasibility
            + self.low_risk
        )
        if not (0.999 <= total <= 1.001):
            raise ValueError(f"Scoring weights must sum to 1.0 (got {total:.4f})")


ScoreRating = Annotated[int, Field(ge=1, le=10)]


class OpportunityInput(BaseModel):
    """Schema validating each CSV row."""

    id: str = Field(..., min_length=1, description="Unique identifier (e.g., OPP-01)")
    title: str = Field(..., min_length=3, description="Concise opportunity summary")
    category: OpportunityCategory
    target_system: str = Field(..., min_length=1, description="Affected system or user group")
    severity: ScoreRating
    reach: ScoreRating
    benefit: ScoreRating
    practicality: ScoreRating
    feasibility: ScoreRating
    low_risk: ScoreRating
    recommended_action: str = Field(default="", description="Optional next-step note")

    @field_validator("category", mode="before")
    @classmethod
    def normalize_category(cls, value: str) -> str:
        if not isinstance(value, str):
            return value
        cleaned = value.strip().lower().replace("-", " ").replace("_", " ")
        mapping = {
            "broke": OpportunityCategory.BROKE.value,
            "broken": OpportunityCategory.BROKE.value,
            "pain": OpportunityCategory.PAIN.value,
            "pain point": OpportunityCategory.PAIN.value,
            "would be nice": OpportunityCategory.WOULD_BE_NICE.value,
            "nice to have": OpportunityCategory.WOULD_BE_NICE.value,
            "wbn": OpportunityCategory.WOULD_BE_NICE.value,
        }
        return mapping.get(cleaned, value.strip())


class ScoredOpportunity(OpportunityInput):
    """Enriched opportunity model with computed scores, multipliers, and quadrant routing."""

    weights: ScoringWeights = Field(default_factory=ScoringWeights, exclude=True)

    @computed_field
    @property
    def base_score(self) -> float:
        raw = (
            self.weights.severity * self.severity
            + self.weights.reach * self.reach
            + self.weights.benefit * self.benefit
            + self.weights.practicality * self.practicality
            + self.weights.feasibility * self.feasibility
            + self.weights.low_risk * self.low_risk
        )
        return round(raw, 2)

    @computed_field
    @property
    def category_multiplier(self) -> float:
        if self.category == OpportunityCategory.BROKE and self.severity >= 8:
            return 1.10
        if self.category == OpportunityCategory.WOULD_BE_NICE:
            if self.benefit >= 9 and self.feasibility >= 7:
                return 1.00
            return 0.95
        return 1.00

    @computed_field
    @property
    def adjusted_score(self) -> float:
        return round(min(10.0, self.base_score * self.category_multiplier), 2)

    @computed_field
    @property
    def value_axis(self) -> float:
        val_weight = self.weights.severity + self.weights.reach + self.weights.benefit
        score = (
            self.weights.severity * self.severity
            + self.weights.reach * self.reach
            + self.weights.benefit * self.benefit
        ) / val_weight
        return round(score, 2)

    @computed_field
    @property
    def execution_axis(self) -> float:
        exec_weight = self.weights.practicality + self.weights.feasibility + self.weights.low_risk
        score = (
            self.weights.practicality * self.practicality
            + self.weights.feasibility * self.feasibility
            + self.weights.low_risk * self.low_risk
        ) / exec_weight
        return round(score, 2)

    @computed_field
    @property
    def quadrant(self) -> DecisionQuadrant:
        high_val = self.value_axis >= 7.0
        high_exec = self.execution_axis >= 7.0
        if high_val and high_exec:
            return DecisionQuadrant.QUICK_WIN
        if high_val and not high_exec:
            return DecisionQuadrant.STRATEGIC_BET
        if not high_val and high_exec:
            return DecisionQuadrant.FILLER
        return DecisionQuadrant.DISTRACTION

    @computed_field
    @property
    def routing_guidance(self) -> str:
        if self.recommended_action.strip():
            return self.recommended_action.strip()
        guidance = {
            DecisionQuadrant.QUICK_WIN: "Execute immediately in next sprint / rapid prototype",
            DecisionQuadrant.STRATEGIC_BET: "Apply TRIZ IFR to reduce complexity before full build",
            DecisionQuadrant.FILLER: "Batch with adjacent maintenance or automate opportunistically",
            DecisionQuadrant.DISTRACTION: "Archive or reframe; do not allocate active capacity",
        }
        return guidance[self.quadrant]


# ==============================================================================
# 2. STAGE 1: TRIZ IDEAL FINAL RESULT (IFR) PYDANTIC V2 SCHEMAS
# ==============================================================================


class IFRArchetypes(BaseModel):
    """The four radical TRIZ Ideal Final Result formulations."""

    self_service: str = Field(
        ...,
        description="How the target object or substance performs the useful function by itself.",
    )
    environmental_leverage: str = Field(
        ...,
        description="How the external environment or ambient medium delivers the useful function.",
    )
    harm_transformation: str = Field(
        ...,
        description="How the harmful factor, defect, or waste is converted to perform the function.",
    )
    disappearance: str = Field(
        ...,
        description="How the mechanism vanishes or requires zero footprint while the function remains.",
    )


class ResourceAudit(BaseModel):
    """Latent, free, or underutilized resources in the Operational Zone (OZ) & Time (OT)."""

    substances: list[str] = Field(
        ..., description="Existing feedstocks, debris, air, coatings, data exhaust, or materials."
    )
    fields: list[str] = Field(
        ..., description="Gravity, thermal gradients, pressure, friction, EM fields, or kinetic energy."
    )
    space_and_time: list[str] = Field(
        ..., description="Gaps, micro-voids, idle cycle windows, or transition phases."
    )


class ContradictionResolution(BaseModel):
    """Physical contradiction and TRIZ separation principle resolution."""

    physical_contradiction: str = Field(
        ...,
        description="Parameter X must be high to achieve benefit A, yet low to avoid harm B.",
    )
    separation_principle: Literal[
        "Separation in Time",
        "Separation in Space",
        "Separation between System and Parts (Scale)",
        "Separation upon Condition",
    ]
    resolution_mechanism: str = Field(
        ..., description="Exact mechanism applying the separation principle without compromise."
    )


class ConcreteConcept(BaseModel):
    """Actionable, non-compromising engineering or workflow concept inspired by IFR."""

    concept_name: str = Field(..., description="Short technical title for the concept.")
    mechanism_summary: str = Field(
        ..., description="How it achieves the PUF using free OZ/OT resources without added complexity."
    )
    ideality_gain: str = Field(
        ..., description="Why this increases Useful Functions while cutting Cost or Harm."
    )
    first_validation_step: str = Field(
        ..., description="Concrete 72-hour test, simulation, or prototype to verify feasibility."
    )


class TRIZIdealFinalResultAnalysis(BaseModel):
    """Complete structured TRIZ IFR analysis for a high-priority opportunity."""

    opportunity_id: str
    target_object: str = Field(
        ..., description="Specific physical or logical object receiving the primary action."
    )
    primary_useful_function: str = Field(
        ..., description="Core action stripped of legacy mechanisms (what must happen, not how)."
    )
    ifr_archetypes: IFRArchetypes
    resource_audit: ResourceAudit
    contradiction: ContradictionResolution
    concrete_concepts: list[ConcreteConcept] = Field(..., min_length=2, max_length=3)


# ==============================================================================
# 3. STAGE 2: FMEA & IDEALITY CRITIC PYDANTIC V2 SCHEMAS
# ==============================================================================


class CriticVerdict(str, Enum):
    APPROVE = "APPROVE"
    REVISE = "REVISE"
    REJECT = "REJECT"


class FailureModeRisk(BaseModel):
    """Second-order failure mode introduced by implementing the proposed concept."""

    failure_mode: str = Field(
        ..., description="Specific new fault, side effect, or edge-case regression introduced."
    )
    trigger_condition: str = Field(
        ..., description="Operating state or boundary condition that triggers this failure."
    )
    severity: ScoreRating = Field(..., description="Impact if failure occurs (1-10).")
    likelihood: ScoreRating = Field(..., description="Probability of occurrence (1-10).")
    mitigation: str = Field(
        ..., description="Low-cost fail-safe or design guard that neutralizes the failure mode."
    )

    @computed_field
    @property
    def criticality_index(self) -> int:
        """Severity x Likelihood risk score (1-100)."""
        return self.severity * self.likelihood


class CostCreepAudit(BaseModel):
    """Audit of hidden hardware, maintenance, compute, or operational overhead."""

    creep_level: Literal["none", "low", "moderate", "severe"]
    hidden_cost_drivers: list[str] = Field(
        ...,
        description="Unstated CapEx, OpEx, calibration, licensing, or maintenance burdens.",
    )
    true_resource_dependency: str = Field(
        ...,
        description="Whether it genuinely uses free OZ/OT resources or smuggles in new components.",
    )


class ConceptAudit(BaseModel):
    """Rigorous engineering & FMEA critique of a single TRIZ concept."""

    concept_name: str = Field(..., description="Must match the audited ConcreteConcept name.")
    verdict: CriticVerdict
    net_ideality_score: ScoreRating = Field(
        ...,
        description="Post-audit Ideality score (1-10) after penalizing hidden costs and failure modes.",
    )
    second_order_failures: list[FailureModeRisk] = Field(..., min_length=1, max_length=3)
    cost_creep: CostCreepAudit
    implementation_risks: list[str] = Field(
        ...,
        min_length=1,
        max_length=3,
        description="Integration bottlenecks, safety/compliance hurdles, or adoption friction.",
    )
    revision_directive: str = Field(
        ...,
        description="Exact modification required before pilot (or 'Ready for pilot as specified' if APPROVED).",
    )


class TRIZCriticReport(BaseModel):
    """Stage-2 Critic audit covering all generated TRIZ concepts for an opportunity."""

    opportunity_id: str
    concept_audits: list[ConceptAudit] = Field(..., min_length=2, max_length=3)
    recommended_concept_name: str = Field(
        ...,
        description="The single highest-ideality surviving concept to advance to prototyping.",
    )
    selection_rationale: str = Field(
        ...,
        description="Why this concept beats alternatives on net ideality, risk, and zero cost creep.",
    )


# ==============================================================================
# 4. SQLITE3 PERSISTENCE LAYER
# ==============================================================================


SCHEMA_SQL = """
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS pipeline_runs (
    run_id INTEGER PRIMARY KEY AUTOINCREMENT,
    executed_at TEXT NOT NULL,
    source_csv TEXT NOT NULL,
    provider TEXT NOT NULL,
    triz_model TEXT NOT NULL,
    critic_model TEXT NOT NULL,
    valid_count INTEGER NOT NULL,
    error_count INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS opportunities (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    category TEXT NOT NULL,
    target_system TEXT NOT NULL,
    severity INTEGER NOT NULL,
    reach INTEGER NOT NULL,
    benefit INTEGER NOT NULL,
    practicality INTEGER NOT NULL,
    feasibility INTEGER NOT NULL,
    low_risk INTEGER NOT NULL,
    base_score REAL NOT NULL,
    category_multiplier REAL NOT NULL,
    adjusted_score REAL NOT NULL,
    value_axis REAL NOT NULL,
    execution_axis REAL NOT NULL,
    quadrant TEXT NOT NULL,
    routing_guidance TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS triz_analyses (
    opportunity_id TEXT PRIMARY KEY,
    target_object TEXT NOT NULL,
    primary_useful_function TEXT NOT NULL,
    ifr_self_service TEXT NOT NULL,
    ifr_environmental_leverage TEXT NOT NULL,
    ifr_harm_transformation TEXT NOT NULL,
    ifr_disappearance TEXT NOT NULL,
    resources_substances_json TEXT NOT NULL,
    resources_fields_json TEXT NOT NULL,
    resources_space_time_json TEXT NOT NULL,
    physical_contradiction TEXT NOT NULL,
    separation_principle TEXT NOT NULL,
    resolution_mechanism TEXT NOT NULL,
    model_used TEXT NOT NULL,
    created_at TEXT NOT NULL,
    FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS triz_concepts (
    concept_id INTEGER PRIMARY KEY AUTOINCREMENT,
    opportunity_id TEXT NOT NULL,
    concept_order INTEGER NOT NULL,
    concept_name TEXT NOT NULL,
    mechanism_summary TEXT NOT NULL,
    ideality_gain TEXT NOT NULL,
    first_validation_step TEXT NOT NULL,
    UNIQUE(opportunity_id, concept_name),
    FOREIGN KEY (opportunity_id) REFERENCES triz_analyses(opportunity_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS critic_audits (
    opportunity_id TEXT PRIMARY KEY,
    recommended_concept_name TEXT NOT NULL,
    selection_rationale TEXT NOT NULL,
    model_used TEXT NOT NULL,
    created_at TEXT NOT NULL,
    FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS concept_audits (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    opportunity_id TEXT NOT NULL,
    concept_name TEXT NOT NULL,
    verdict TEXT NOT NULL,
    net_ideality_score INTEGER NOT NULL,
    cost_creep_level TEXT NOT NULL,
    hidden_cost_drivers_json TEXT NOT NULL,
    true_resource_dependency TEXT NOT NULL,
    implementation_risks_json TEXT NOT NULL,
    revision_directive TEXT NOT NULL,
    UNIQUE(opportunity_id, concept_name),
    FOREIGN KEY (opportunity_id) REFERENCES critic_audits(opportunity_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS second_order_failures (
    failure_id INTEGER PRIMARY KEY AUTOINCREMENT,
    audit_id INTEGER NOT NULL,
    opportunity_id TEXT NOT NULL,
    concept_name TEXT NOT NULL,
    failure_mode TEXT NOT NULL,
    trigger_condition TEXT NOT NULL,
    severity INTEGER NOT NULL,
    likelihood INTEGER NOT NULL,
    criticality_index INTEGER NOT NULL,
    mitigation TEXT NOT NULL,
    FOREIGN KEY (audit_id) REFERENCES concept_audits(audit_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_opp_quadrant ON opportunities(quadrant);
CREATE INDEX IF NOT EXISTS idx_opp_score ON opportunities(adjusted_score DESC);
CREATE INDEX IF NOT EXISTS idx_concept_verdict ON concept_audits(verdict);
CREATE INDEX IF NOT EXISTS idx_failure_crit ON second_order_failures(criticality_index DESC);
"""


def init_sqlite_db(db_path: Path) -> sqlite3.Connection:
    """Initialize SQLite database with foreign keys, WAL mode, and relational schema."""
    db_path.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(str(db_path))
    conn.execute("PRAGMA journal_mode = WAL;")
    conn.execute("PRAGMA foreign_keys = ON;")
    conn.executescript(SCHEMA_SQL)
    conn.commit()
    return conn


def get_existing_audited_ids(conn: sqlite3.Connection) -> set[str]:
    """Return opportunity IDs that already have both TRIZ IFR and Critic records stored."""
    rows = conn.execute(
        """
        SELECT t.opportunity_id
        FROM triz_analyses t
        INNER JOIN critic_audits c ON t.opportunity_id = c.opportunity_id
        """
    ).fetchall()
    return {r[0] for r in rows}


def persist_pipeline_results(
    conn: sqlite3.Connection,
    source_csv: Path,
    provider: str,
    triz_model: str,
    critic_model: str,
    opportunities: list[ScoredOpportunity],
    triz_analyses: dict[str, TRIZIdealFinalResultAnalysis],
    critic_reports: dict[str, TRIZCriticReport],
    error_count: int,
) -> int:
    """Atomically persist pipeline run, opportunities, TRIZ IFRs, and FMEA Critic audits."""
    now_iso = datetime.now(timezone.utc).isoformat()
    with conn:
        cur = conn.execute(
            """
            INSERT INTO pipeline_runs (
                executed_at, source_csv, provider, triz_model, critic_model, valid_count, error_count
            ) VALUES (?, ?, ?, ?, ?, ?, ?)
            """,
            (
                now_iso,
                str(source_csv),
                provider,
                triz_model,
                critic_model,
                len(opportunities),
                error_count,
            ),
        )
        run_id = int(cur.lastrowid or 0)

        for opp in opportunities:
            conn.execute(
                """
                INSERT INTO opportunities (
                    id, title, category, target_system,
                    severity, reach, benefit, practicality, feasibility, low_risk,
                    base_score, category_multiplier, adjusted_score,
                    value_axis, execution_axis, quadrant, routing_guidance, updated_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(id) DO UPDATE SET
                    title = excluded.title,
                    category = excluded.category,
                    target_system = excluded.target_system,
                    severity = excluded.severity,
                    reach = excluded.reach,
                    benefit = excluded.benefit,
                    practicality = excluded.practicality,
                    feasibility = excluded.feasibility,
                    low_risk = excluded.low_risk,
                    base_score = excluded.base_score,
                    category_multiplier = excluded.category_multiplier,
                    adjusted_score = excluded.adjusted_score,
                    value_axis = excluded.value_axis,
                    execution_axis = excluded.execution_axis,
                    quadrant = excluded.quadrant,
                    routing_guidance = excluded.routing_guidance,
                    updated_at = excluded.updated_at
                """,
                (
                    opp.id,
                    opp.title,
                    opp.category.value,
                    opp.target_system,
                    opp.severity,
                    opp.reach,
                    opp.benefit,
                    opp.practicality,
                    opp.feasibility,
                    opp.low_risk,
                    opp.base_score,
                    opp.category_multiplier,
                    opp.adjusted_score,
                    opp.value_axis,
                    opp.execution_axis,
                    opp.quadrant.value,
                    opp.routing_guidance,
                    now_iso,
                ),
            )

        for opp_id, triz in triz_analyses.items():
            # Replace child concepts cleanly on re-analysis
            conn.execute("DELETE FROM triz_concepts WHERE opportunity_id = ?", (opp_id,))
            conn.execute(
                """
                INSERT INTO triz_analyses (
                    opportunity_id, target_object, primary_useful_function,
                    ifr_self_service, ifr_environmental_leverage,
                    ifr_harm_transformation, ifr_disappearance,
                    resources_substances_json, resources_fields_json, resources_space_time_json,
                    physical_contradiction, separation_principle, resolution_mechanism,
                    model_used, created_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(opportunity_id) DO UPDATE SET
                    target_object = excluded.target_object,
                    primary_useful_function = excluded.primary_useful_function,
                    ifr_self_service = excluded.ifr_self_service,
                    ifr_environmental_leverage = excluded.ifr_environmental_leverage,
                    ifr_harm_transformation = excluded.ifr_harm_transformation,
                    ifr_disappearance = excluded.ifr_disappearance,
                    resources_substances_json = excluded.resources_substances_json,
                    resources_fields_json = excluded.resources_fields_json,
                    resources_space_time_json = excluded.resources_space_time_json,
                    physical_contradiction = excluded.physical_contradiction,
                    separation_principle = excluded.separation_principle,
                    resolution_mechanism = excluded.resolution_mechanism,
                    model_used = excluded.model_used,
                    created_at = excluded.created_at
                """,
                (
                    opp_id,
                    triz.target_object,
                    triz.primary_useful_function,
                    triz.ifr_archetypes.self_service,
                    triz.ifr_archetypes.environmental_leverage,
                    triz.ifr_archetypes.harm_transformation,
                    triz.ifr_archetypes.disappearance,
                    json.dumps(triz.resource_audit.substances),
                    json.dumps(triz.resource_audit.fields),
                    json.dumps(triz.resource_audit.space_and_time),
                    triz.contradiction.physical_contradiction,
                    triz.contradiction.separation_principle,
                    triz.contradiction.resolution_mechanism,
                    triz_model,
                    now_iso,
                ),
            )
            for idx, concept in enumerate(triz.concrete_concepts, start=1):
                conn.execute(
                    """
                    INSERT INTO triz_concepts (
                        opportunity_id, concept_order, concept_name,
                        mechanism_summary, ideality_gain, first_validation_step
                    ) VALUES (?, ?, ?, ?, ?, ?)
                    """,
                    (
                        opp_id,
                        idx,
                        concept.concept_name,
                        concept.mechanism_summary,
                        concept.ideality_gain,
                        concept.first_validation_step,
                    ),
                )

        for opp_id, critic in critic_reports.items():
            # Replace child audits & failure modes cleanly on re-audit
            conn.execute("DELETE FROM second_order_failures WHERE opportunity_id = ?", (opp_id,))
            conn.execute("DELETE FROM concept_audits WHERE opportunity_id = ?", (opp_id,))
            conn.execute(
                """
                INSERT INTO critic_audits (
                    opportunity_id, recommended_concept_name, selection_rationale,
                    model_used, created_at
                ) VALUES (?, ?, ?, ?, ?)
                ON CONFLICT(opportunity_id) DO UPDATE SET
                    recommended_concept_name = excluded.recommended_concept_name,
                    selection_rationale = excluded.selection_rationale,
                    model_used = excluded.model_used,
                    created_at = excluded.created_at
                """,
                (
                    opp_id,
                    critic.recommended_concept_name,
                    critic.selection_rationale,
                    critic_model,
                    now_iso,
                ),
            )
            for audit in critic.concept_audits:
                audit_cur = conn.execute(
                    """
                    INSERT INTO concept_audits (
                        opportunity_id, concept_name, verdict, net_ideality_score,
                        cost_creep_level, hidden_cost_drivers_json,
                        true_resource_dependency, implementation_risks_json, revision_directive
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """,
                    (
                        opp_id,
                        audit.concept_name,
                        audit.verdict.value,
                        audit.net_ideality_score,
                        audit.cost_creep.creep_level,
                        json.dumps(audit.cost_creep.hidden_cost_drivers),
                        audit.cost_creep.true_resource_dependency,
                        json.dumps(audit.implementation_risks),
                        audit.revision_directive,
                    ),
                )
                audit_id = int(audit_cur.lastrowid or 0)
                for fm in audit.second_order_failures:
                    conn.execute(
                        """
                        INSERT INTO second_order_failures (
                            audit_id, opportunity_id, concept_name, failure_mode,
                            trigger_condition, severity, likelihood, criticality_index, mitigation
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                        """,
                        (
                            audit_id,
                            opp_id,
                            audit.concept_name,
                            fm.failure_mode,
                            fm.trigger_condition,
                            fm.severity,
                            fm.likelihood,
                            fm.criticality_index,
                            fm.mitigation,
                        ),
                    )
    return run_id


def print_sqlite_summary(conn: sqlite3.Connection) -> None:
    """Print summary statistics and top Critic-vetted concepts from SQLite."""
    total_opps = conn.execute("SELECT COUNT(*) FROM opportunities").fetchone()[0]
    total_triz = conn.execute("SELECT COUNT(*) FROM triz_analyses").fetchone()[0]
    total_audits = conn.execute("SELECT COUNT(*) FROM critic_audits").fetchone()[0]
    print(f"SQLite Store Summary -> Opportunities: {total_opps} | TRIZ IFRs: {total_triz} | FMEA Audits: {total_audits}")

    quad_rows = conn.execute(
        "SELECT quadrant, COUNT(*), ROUND(AVG(adjusted_score), 2) FROM opportunities GROUP BY quadrant ORDER BY 2 DESC"
    ).fetchall()
    for quad, cnt, avg_s in quad_rows:
        print(f"  - {quad}: {cnt} items (avg adjusted score: {avg_s})")

    top_rows = conn.execute(
        """
        SELECT o.id, o.title, o.adjusted_score, c.recommended_concept_name, ca.verdict, ca.net_ideality_score
        FROM opportunities o
        JOIN critic_audits c ON o.id = c.opportunity_id
        LEFT JOIN concept_audits ca
          ON ca.opportunity_id = c.opportunity_id
         AND LOWER(ca.concept_name) = LOWER(c.recommended_concept_name)
        ORDER BY o.adjusted_score DESC
        LIMIT 10
        """
    ).fetchall()
    if top_rows:
        print("\nTop Critic-Selected Concepts in SQLite:")
        for oid, title, score, cname, verdict, net_id in top_rows:
            print(f"  - [{oid}] ({score:.2f}) {title} -> '{cname}' [{verdict or 'N/A'} | Net Ideality: {net_id or 'N/A'}/10]")


# ==============================================================================
# 5. LLM BACKEND ADAPTERS (LOCAL OLLAMA & GOOGLE GEMINI)
# ==============================================================================


TRIZ_SYSTEM_PROMPT = """You are a Principal Systems Architect and TRIZ Master.
Analyze the provided engineering/operational opportunity using the TRIZ Ideal Final Result (IFR) methodology:
1. Strip away existing mechanisms to isolate the Target Object and Primary Useful Function (PUF).
2. Formulate all four radical IFR archetypes (Self-Service, Environmental Leverage, Harm Transformation, Disappearance) where the system disappears yet its function is performed.
3. Audit free, latent resources already inside the Operational Zone (OZ) and Operational Time (OT).
4. Identify the core Physical Contradiction and resolve it via one of the four TRIZ Separation Principles.
5. Synthesize 2 to 3 concrete, non-compromising engineering or software concepts with an immediate validation step.
Return strictly valid JSON matching the requested schema."""


CRITIC_SYSTEM_PROMPT = """You are a skeptical Principal Systems Safety & FMEA Auditor reviewing proposed TRIZ Ideal Final Result (IFR) concepts.
Your job is to catch blind spots BEFORE engineering resources are spent:
1. Second-Order Failure Modes: What new failure modes, cascading faults, or edge-case regressions does each concept introduce? Rate severity (1-10) and likelihood (1-10) realistically.
2. Cost & Complexity Creep: Does the concept truly rely on free Operational Zone/Time (OZ/OT) resources, or does it secretly introduce extra sensors, calibration loops, compute overhead, or maintenance debt?
3. Implementation Risks: Identify integration friction, regulatory/safety hurdles, or operational bottlenecks.
4. Assign a strict verdict (APPROVE, REVISE, or REJECT) and a net_ideality_score (1-10) for each concept, then select the single strongest surviving concept to advance.
Return strictly valid JSON matching the requested schema."""


def build_triz_user_prompt(opp: ScoredOpportunity) -> str:
    return (
        f"Opportunity ID: {opp.id}\n"
        f"Title: {opp.title}\n"
        f"Category: {opp.category.value}\n"
        f"Target System / Group: {opp.target_system}\n"
        f"Decision Quadrant: {opp.quadrant.value} (Adjusted Score: {opp.adjusted_score}/10.0)\n"
        f"Scores -> Severity: {opp.severity}/10, Reach: {opp.reach}/10, Benefit: {opp.benefit}/10, "
        f"Practicality: {opp.practicality}/10, Feasibility: {opp.feasibility}/10, Low Risk: {opp.low_risk}/10\n"
        f"Existing Action Note: {opp.routing_guidance}\n\n"
        "Generate a rigorous, domain-specific TRIZ Ideal Final Result (IFR) analysis for this opportunity."
    )


def build_critic_user_prompt(
    opp: ScoredOpportunity, triz: TRIZIdealFinalResultAnalysis
) -> str:
    return (
        f"Original Opportunity ({opp.id}): {opp.title} [{opp.category.value} | {opp.target_system}]\n"
        f"Baseline Risk Score: {opp.low_risk}/10 | Feasibility Score: {opp.feasibility}/10\n\n"
        "Generated TRIZ IFR Analysis & Candidate Concepts (JSON):\n"
        f"{triz.model_dump_json(indent=2)}\n\n"
        "Audit every candidate concept in `concrete_concepts` for second-order failure modes, "
        "hidden cost/complexity creep, and implementation risks. Be candid and rigorous."
    )


def _invoke_ollama_structured(
    system_prompt: str,
    user_prompt: str,
    schema_cls: type[BaseModel],
    model_name: str,
    host: str,
) -> str:
    """Shared Ollama structured JSON invocation with HTTP fallback."""
    schema = schema_cls.model_json_schema()
    try:
        import ollama  # type: ignore

        client = ollama.Client(host=host)
        response = client.chat(
            model=model_name,
            messages=[
                {"role": "system", "content": system_prompt},
                {"role": "user", "content": user_prompt},
            ],
            format=schema,
            options={"temperature": 0.2},
        )
        return response["message"]["content"]
    except ImportError:
        payload = json.dumps(
            {
                "model": model_name,
                "messages": [
                    {"role": "system", "content": system_prompt},
                    {"role": "user", "content": user_prompt},
                ],
                "format": schema,
                "stream": False,
                "options": {"temperature": 0.2},
            }
        ).encode("utf-8")
        req = urllib.request.Request(
            f"{host.rstrip('/')}/api/chat",
            data=payload,
            headers={"Content-Type": "application/json"},
        )
        with urllib.request.urlopen(req, timeout=180) as resp:
            body = json.loads(resp.read().decode("utf-8"))
            return body["message"]["content"]


def _invoke_gemini_structured(
    system_prompt: str,
    user_prompt: str,
    schema_cls: type[BaseModel],
    model_name: str,
) -> BaseModel:
    """Shared Google GenAI SDK native Pydantic structured output invocation."""
    from google import genai
    from google.genai import types

    api_key = os.environ.get("GEMINI_API_KEY")
    if not api_key:
        raise EnvironmentError(
            "GEMINI_API_KEY environment variable is required for --provider gemini."
        )

    client = genai.Client(api_key=api_key)
    response = client.models.generate_content(
        model=model_name,
        contents=user_prompt,
        config=types.GenerateContentConfig(
            system_instruction=system_prompt,
            response_mime_type="application/json",
            response_schema=schema_cls,
            temperature=0.2,
        ),
    )
    if response.parsed is not None:
        return response.parsed
    return schema_cls.model_validate_json(response.text)


def run_triz_generation(
    opp: ScoredOpportunity,
    provider: Literal["ollama", "gemini"],
    model_name: str,
    ollama_host: str,
) -> TRIZIdealFinalResultAnalysis:
    user_prompt = build_triz_user_prompt(opp)
    if provider == "ollama":
        raw = _invoke_ollama_structured(
            TRIZ_SYSTEM_PROMPT,
            user_prompt,
            TRIZIdealFinalResultAnalysis,
            model_name,
            ollama_host,
        )
        analysis = TRIZIdealFinalResultAnalysis.model_validate_json(raw)
    else:
        analysis = _invoke_gemini_structured(
            TRIZ_SYSTEM_PROMPT,
            user_prompt,
            TRIZIdealFinalResultAnalysis,
            model_name,
        )  # type: ignore[assignment]
    analysis.opportunity_id = opp.id
    return analysis


def run_critic_audit(
    opp: ScoredOpportunity,
    triz: TRIZIdealFinalResultAnalysis,
    provider: Literal["ollama", "gemini"],
    model_name: str,
    ollama_host: str,
) -> TRIZCriticReport:
    user_prompt = build_critic_user_prompt(opp, triz)
    if provider == "ollama":
        raw = _invoke_ollama_structured(
            CRITIC_SYSTEM_PROMPT,
            user_prompt,
            TRIZCriticReport,
            model_name,
            ollama_host,
        )
        report = TRIZCriticReport.model_validate_json(raw)
    else:
        report = _invoke_gemini_structured(
            CRITIC_SYSTEM_PROMPT,
            user_prompt,
            TRIZCriticReport,
            model_name,
        )  # type: ignore[assignment]
    report.opportunity_id = opp.id
    return report


# ==============================================================================
# 6. CSV INGESTION, PIPELINE ORCHESTRATION & REPORT GENERATION
# ==============================================================================


def ingest_and_score_csv(
    csv_path: Path, weights: ScoringWeights | None = None
) -> tuple[list[ScoredOpportunity], list[str]]:
    """Read CSV rows, validate with Pydantic v2, and return sorted results + row errors."""
    active_weights = weights or ScoringWeights()
    active_weights.validate_sum()

    scored: list[ScoredOpportunity] = []
    errors: list[str] = []

    with csv_path.open(mode="r", encoding="utf-8-sig", newline="") as f:
        reader = csv.DictReader(f)
        if not reader.fieldnames:
            raise ValueError(f"CSV file '{csv_path}' is empty or missing headers.")

        for line_num, raw_row in enumerate(reader, start=2):
            cleaned_row = {
                k.strip().lower().replace(" ", "_"): (v.strip() if v is not None else "")
                for k, v in raw_row.items()
                if k is not None
            }
            if not any(cleaned_row.values()):
                continue

            try:
                validated = OpportunityInput.model_validate(cleaned_row)
                enriched = ScoredOpportunity(
                    **validated.model_dump(),
                    weights=active_weights,
                )
                scored.append(enriched)
            except ValidationError as exc:
                err_msgs = "; ".join(
                    f"{'.'.join(str(loc) for loc in e['loc'])}: {e['msg']}"
                    for e in exc.errors()
                )
                errors.append(f"Row {line_num} ({cleaned_row.get('id', 'UNKNOWN')}): {err_msgs}")

    scored.sort(key=lambda x: (x.adjusted_score, x.base_score, x.severity), reverse=True)
    return scored, errors


def run_triz_and_critic_stages(
    opportunities: list[ScoredOpportunity],
    provider: Literal["ollama", "gemini", "none"],
    model_name: str,
    critic_model_name: str,
    ollama_host: str,
    top_k: int,
    skip_critic: bool = False,
    already_audited_ids: set[str] | None = None,
) -> tuple[dict[str, TRIZIdealFinalResultAnalysis], dict[str, TRIZCriticReport]]:
    """Run Stage-1 TRIZ IFR generation and Stage-2 Critic audit on top-K candidates."""
    if provider == "none" or top_k <= 0:
        return {}, {}

    skip_ids = already_audited_ids or set()
    eligible = [
        opp
        for opp in opportunities
        if opp.quadrant in (DecisionQuadrant.STRATEGIC_BET, DecisionQuadrant.QUICK_WIN)
        and opp.id not in skip_ids
    ][:top_k]

    triz_analyses: dict[str, TRIZIdealFinalResultAnalysis] = {}
    critic_reports: dict[str, TRIZCriticReport] = {}

    for idx, opp in enumerate(eligible, start=1):
        print(
            f"[{idx}/{len(eligible)}] Stage 1 — TRIZ IFR ({provider}:{model_name}) "
            f"for {opp.id} ({opp.quadrant.value})..."
        )
        try:
            triz_result = run_triz_generation(
                opp=opp,
                provider=provider,
                model_name=model_name,
                ollama_host=ollama_host,
            )
            triz_analyses[opp.id] = triz_result
        except Exception as exc:
            print(f"  [!] Stage 1 TRIZ analysis failed for {opp.id}: {exc}")
            continue

        if not skip_critic:
            print(
                f"[{idx}/{len(eligible)}] Stage 2 — FMEA Critic ({provider}:{critic_model_name}) "
                f"auditing {len(triz_result.concrete_concepts)} concepts for {opp.id}..."
            )
            try:
                critic_result = run_critic_audit(
                    opp=opp,
                    triz=triz_result,
                    provider=provider,
                    model_name=critic_model_name,
                    ollama_host=ollama_host,
                )
                critic_reports[opp.id] = critic_result
            except Exception as exc:
                print(f"  [!] Stage 2 Critic audit failed for {opp.id}: {exc}")

    return triz_analyses, critic_reports


def write_ranked_csv(
    opportunities: list[ScoredOpportunity],
    critic_reports: dict[str, TRIZCriticReport],
    output_csv: Path,
) -> None:
    """Export ranked opportunities with computed fields and Critic winner to CSV."""
    output_csv.parent.mkdir(parents=True, exist_ok=True)
    fieldnames = [
        "rank",
        "id",
        "title",
        "category",
        "target_system",
        "severity",
        "reach",
        "benefit",
        "practicality",
        "feasibility",
        "low_risk",
        "base_score",
        "category_multiplier",
        "adjusted_score",
        "value_axis",
        "execution_axis",
        "quadrant",
        "routing_guidance",
        "critic_selected_concept",
    ]
    with output_csv.open(mode="w", encoding="utf-8", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=fieldnames)
        writer.writeheader()
        for rank, opp in enumerate(opportunities, start=1):
            row = opp.model_dump()
            row["rank"] = rank
            row["category"] = opp.category.value
            row["quadrant"] = opp.quadrant.value
            critic = critic_reports.get(opp.id)
            row["critic_selected_concept"] = (
                critic.recommended_concept_name if critic else ""
            )
            writer.writerow({k: row[k] for k in fieldnames})


def write_json_artifact(items: list[BaseModel], output_json: Path) -> None:
    """Export a list of Pydantic models to formatted JSON."""
    output_json.parent.mkdir(parents=True, exist_ok=True)
    serialized = [item.model_dump() for item in items]
    output_json.write_text(json.dumps(serialized, indent=2), encoding="utf-8")


def write_markdown_report(
    opportunities: list[ScoredOpportunity],
    triz_analyses: dict[str, TRIZIdealFinalResultAnalysis],
    critic_reports: dict[str, TRIZCriticReport],
    errors: list[str],
    output_md: Path,
    db_path: Path,
    run_id: int,
) -> None:
    """Generate a ranked executive Markdown report with embedded TRIZ IFR & Critic audits."""
    output_md.parent.mkdir(parents=True, exist_ok=True)
    lines: list[str] = [
        "# Ranked Opportunity Evaluation, TRIZ IFR & FMEA Critic Report",
        "",
        f"- **SQLite Persistence:** `{db_path}` (Run ID: `#{run_id}`)",
        f"- **Total Validated Opportunities:** {len(opportunities)}",
        f"- **Stage 1 TRIZ IFR Analyses:** {len(triz_analyses)}",
        f"- **Stage 2 FMEA Critic Audits:** {len(critic_reports)}",
        f"- **Validation Errors Skipped:** {len(errors)}",
        "",
        "## Master Ranked Leaderboard",
        "",
        "| Rank | ID | Category | Opportunity | Target System | Base | Adj. Score | Value / Exec | Quadrant | Top Critic-Vetted Concept |",
        "| :---: | :--- | :--- | :--- | :--- | :---: | :---: | :---: | :--- | :--- |",
    ]

    for rank, opp in enumerate(opportunities, start=1):
        critic = critic_reports.get(opp.id)
        vetted = (
            f"**{critic.recommended_concept_name}**"
            if critic
            else opp.routing_guidance
        )
        lines.append(
            f"| **{rank}** | `{opp.id}` | {opp.category.value} | {opp.title} | "
            f"{opp.target_system} | {opp.base_score:.2f} | **{opp.adjusted_score:.2f}** | "
            f"{opp.value_axis:.1f} / {opp.execution_axis:.1f} | **{opp.quadrant.value}** | "
            f"{vetted} |"
        )

    lines.extend(["", "## Quadrant Breakdown", ""])
    for quad in DecisionQuadrant:
        items = [o for o in opportunities if o.quadrant == quad]
        lines.append(f"### {quad.value} ({len(items)})")
        if not items:
            lines.append("- *No opportunities in this quadrant.*")
        else:
            for item in items:
                lines.append(
                    f"- **`{item.id}` ({item.adjusted_score:.2f}) — {item.title}** "
                    f"[{item.category.value} | {item.target_system}]: {item.routing_guidance}"
                )
        lines.append("")

    if triz_analyses:
        lines.extend(
            ["---", "", "## TRIZ Ideal Final Result (IFR) & Critic Audit Dossiers", ""]
        )
        opp_lookup = {o.id: o for o in opportunities}
        for opp_id, triz in triz_analyses.items():
            opp = opp_lookup[opp_id]
            critic = critic_reports.get(opp_id)
            lines.extend(
                [
                    f"### `{opp.id}` — {opp.title} ({opp.quadrant.value} | Score: {opp.adjusted_score:.2f})",
                    "",
                    f"- **Target Object:** {triz.target_object}",
                    f"- **Primary Useful Function (PUF):** {triz.primary_useful_function}",
                    "",
                    "#### 1. Four IFR Archetypes",
                    f"- **Self-Service:** {triz.ifr_archetypes.self_service}",
                    f"- **Environmental Leverage:** {triz.ifr_archetypes.environmental_leverage}",
                    f"- **Harm Transformation:** {triz.ifr_archetypes.harm_transformation}",
                    f"- **Disappearance:** {triz.ifr_archetypes.disappearance}",
                    "",
                    "#### 2. Operational Zone & Time (OZ/OT) Resource Audit",
                    f"- **Substances:** {', '.join(triz.resource_audit.substances)}",
                    f"- **Fields:** {', '.join(triz.resource_audit.fields)}",
                    f"- **Space & Time:** {', '.join(triz.resource_audit.space_and_time)}",
                    "",
                    "#### 3. Contradiction & Separation Resolution",
                    f"- **Physical Contradiction:** {triz.contradiction.physical_contradiction}",
                    f"- **Separation Principle:** `{triz.contradiction.separation_principle}`",
                    f"- **Resolution Mechanism:** {triz.contradiction.resolution_mechanism}",
                    "",
                    "#### 4. Candidate Concepts & Stage-2 FMEA Critic Audit",
                ]
            )

            audit_lookup = (
                {a.concept_name.lower(): a for a in critic.concept_audits}
                if critic
                else {}
            )

            for c_idx, concept in enumerate(triz.concrete_concepts, start=1):
                audit = audit_lookup.get(concept.concept_name.lower())
                verdict_badge = (
                    f" — **[{audit.verdict.value} | Net Ideality: {audit.net_ideality_score}/10]**"
                    if audit
                    else ""
                )
                lines.extend(
                    [
                        f"{c_idx}. **{concept.concept_name}**{verdict_badge}",
                        f"   - *Mechanism:* {concept.mechanism_summary}",
                        f"   - *Ideality Gain:* {concept.ideality_gain}",
                        f"   - *First Validation Step:* {concept.first_validation_step}",
                    ]
                )
                if audit:
                    lines.append(
                        f"   - *Cost Creep (`{audit.cost_creep.creep_level}`):* "
                        f"{audit.cost_creep.true_resource_dependency} "
                        f"(Drivers: {', '.join(audit.cost_creep.hidden_cost_drivers) or 'None'})"
                    )
                    for fm in audit.second_order_failures:
                        lines.append(
                            f"   - *2nd-Order Failure (Criticality {fm.criticality_index}/100 — "
                            f"S:{fm.severity} x L:{fm.likelihood}):* **{fm.failure_mode}** "
                            f"when *{fm.trigger_condition}* → **Mitigation:** {fm.mitigation}"
                        )
                    lines.append(
                        f"   - *Implementation Risks:* {'; '.join(audit.implementation_risks)}"
                    )
                    lines.append(
                        f"   - *Critic Directive:* {audit.revision_directive}"
                    )

            if critic:
                lines.extend(
                    [
                        "",
                        f"> **Critic Final Selection:** `{critic.recommended_concept_name}` — "
                        f"{critic.selection_rationale}",
                    ]
                )
            lines.append("")

    if errors:
        lines.extend(["## Validation Warnings / Rejected Rows", ""])
        for err in errors:
            lines.append(f"- `{err}`")
        lines.append("")

    output_md.write_text("\n".join(lines), encoding="utf-8")


def create_sample_csv(sample_path: Path) -> None:
    """Write a starter CSV template."""
    sample_path.parent.mkdir(parents=True, exist_ok=True)
    rows = [
        {
            "id": "OPP-01",
            "title": "Sensor calibration drift under thermal load",
            "category": "Broke",
            "target_system": "Field Hardware",
            "severity": 9,
            "reach": 7,
            "benefit": 8,
            "practicality": 8,
            "feasibility": 6,
            "low_risk": 7,
            "recommended_action": "Apply TRIZ thermal compensation; pilot fix",
        },
        {
            "id": "OPP-02",
            "title": "Manual CSV reformatting between tools",
            "category": "Pain",
            "target_system": "Data Pipeline",
            "severity": 6,
            "reach": 9,
            "benefit": 7,
            "practicality": 9,
            "feasibility": 9,
            "low_risk": 9,
            "recommended_action": "Automate schema bridge script",
        },
        {
            "id": "OPP-03",
            "title": "Predictive anomaly pre-alert dashboard",
            "category": "Would Be Nice",
            "target_system": "Ops Telemetry",
            "severity": 3,
            "reach": 6,
            "benefit": 9,
            "practicality": 7,
            "feasibility": 5,
            "low_risk": 6,
            "recommended_action": "",
        },
    ]
    with sample_path.open("w", encoding="utf-8", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=list(rows[0].keys()))
        writer.writeheader()
        writer.writerows(rows)


def main() -> None:
    parser = argparse.ArgumentParser(
        description="Score opportunities, generate TRIZ IFR concepts, run FMEA Critic audits, and persist to SQLite."
    )
    parser.add_argument(
        "input_csv",
        type=Path,
        nargs="?",
        default=Path("opportunities.csv"),
        help="Path to input CSV file (default: opportunities.csv)",
    )
    parser.add_argument(
        "--db-path",
        type=Path,
        default=Path("output/opportunities_pipeline.db"),
        help="Path to SQLite3 database file (default: output/opportunities_pipeline.db)",
    )
    parser.add_argument(
        "--skip-existing",
        action="store_true",
        help="Skip TRIZ/Critic LLM calls for opportunity IDs already audited in SQLite",
    )
    parser.add_argument(
        "--query-summary",
        action="store_true",
        help="Print a summary of records currently stored in SQLite and exit",
    )
    parser.add_argument(
        "--provider",
        choices=["ollama", "gemini", "none"],
        default="ollama",
        help="LLM backend for TRIZ IFR & Critic stages (default: ollama)",
    )
    parser.add_argument(
        "--model",
        type=str,
        default=None,
        help="Primary TRIZ model (defaults to 'gpt-oss:20b' for ollama, 'gemini-2.5-flash' for gemini)",
    )
    parser.add_argument(
        "--critic-model",
        type=str,
        default=None,
        help="Optional separate model for Stage-2 Critic pass (defaults to --model)",
    )
    parser.add_argument(
        "--ollama-host",
        type=str,
        default="http://localhost:11434",
        help="Local Ollama endpoint URL (default: http://localhost:11434)",
    )
    parser.add_argument(
        "--top-k",
        type=int,
        default=5,
        help="Max number of top Strategic Bet / Quick Win items to analyze (default: 5)",
    )
    parser.add_argument(
        "--skip-critic",
        action="store_true",
        help="Run Stage-1 TRIZ IFR generation only without Stage-2 FMEA Critic pass",
    )
    parser.add_argument(
        "--out-csv",
        type=Path,
        default=Path("output/ranked_opportunities.csv"),
        help="Path for ranked output CSV",
    )
    parser.add_argument(
        "--out-triz-json",
        type=Path,
        default=Path("output/triz_ifr_analyses.json"),
        help="Path for Stage-1 TRIZ IFR JSON output",
    )
    parser.add_argument(
        "--out-critic-json",
        type=Path,
        default=Path("output/triz_critic_audits.json"),
        help="Path for Stage-2 Critic FMEA JSON output",
    )
    parser.add_argument(
        "--out-md",
        type=Path,
        default=Path("output/ranked_report.md"),
        help="Path for Markdown report",
    )
    parser.add_argument(
        "--init-sample",
        action="store_true",
        help="Generate a sample input CSV at input_csv and exit",
    )
    args = parser.parse_args()

    if args.init_sample:
        create_sample_csv(args.input_csv)
        print(f"Sample CSV created at: {args.input_csv}")
        return

    conn = init_sqlite_db(args.db_path)

    if args.query_summary:
        print_sqlite_summary(conn)
        conn.close()
        return

    if not args.input_csv.exists():
        conn.close()
        raise FileNotFoundError(
            f"Input CSV '{args.input_csv}' not found. Run with --init-sample to create one."
        )

    default_model = "gpt-oss:20b" if args.provider == "ollama" else "gemini-2.5-flash"
    active_model = args.model or default_model
    active_critic_model = args.critic_model or active_model

    opportunities, errors = ingest_and_score_csv(args.input_csv)
    already_audited = get_existing_audited_ids(conn) if args.skip_existing else set()

    triz_analyses, critic_reports = run_triz_and_critic_stages(
        opportunities=opportunities,
        provider=args.provider,
        model_name=active_model,
        critic_model_name=active_critic_model,
        ollama_host=args.ollama_host,
        top_k=args.top_k,
        skip_critic=args.skip_critic,
        already_audited_ids=already_audited,
    )

    run_id = persist_pipeline_results(
        conn=conn,
        source_csv=args.input_csv,
        provider=args.provider,
        triz_model=active_model if args.provider != "none" else "none",
        critic_model=(
            active_critic_model
            if (args.provider != "none" and not args.skip_critic)
            else "none"
        ),
        opportunities=opportunities,
        triz_analyses=triz_analyses,
        critic_reports=critic_reports,
        error_count=len(errors),
    )

    write_ranked_csv(opportunities, critic_reports, args.out_csv)
    write_json_artifact(list(triz_analyses.values()), args.out_triz_json)
    write_json_artifact(list(critic_reports.values()), args.out_critic_json)
    write_markdown_report(
        opportunities,
        triz_analyses,
        critic_reports,
        errors,
        args.out_md,
        db_path=args.db_path,
        run_id=run_id,
    )

    print(f"\nProcessed {len(opportunities)} valid opportunities ({len(errors)} validation errors).")
    print(f"SQLite Database   : {args.db_path} (Run #{run_id})")
    print(f"Stage 1 TRIZ IFR  : {len(triz_analyses)} generated -> {args.out_triz_json}")
    print(f"Stage 2 Critic    : {len(critic_reports)} audited   -> {args.out_critic_json}")
    print(f"Ranked CSV        : {args.out_csv}")
    print(f"MD Report         : {args.out_md}\n")
    print_sqlite_summary(conn)
    conn.close()


if __name__ == "__main__":
    main()

Example SQLite Workflows

  • Run the full pipeline and persist to SQLite:

    python score_opportunities.py opportunities.csv --db-path output/opportunities_pipeline.db --provider ollama --model gpt-oss:20b --critic-model gemma4:31b
    
  • Append new rows to opportunities.csv and only analyze newly added IDs:

    python score_opportunities.py opportunities.csv --db-path output/opportunities_pipeline.db --skip-existing
    
  • Inspect the stored SQLite database summary anytime:

    python score_opportunities.py --db-path output/opportunities_pipeline.db --query-summary
    
  • Query high-criticality second-order failure modes directly via sqlite3:

    SELECT o.id, o.title, f.concept_name, f.failure_mode, f.criticality_index, f.mitigation
    FROM second_order_failures f
    JOIN opportunities o ON f.opportunity_id = o.id
    WHERE f.criticality_index >= 35
    ORDER BY f.criticality_index DESC;


From <https://gemini.google.com/app/2c46a40d8e7a0810>  Google Gemini (4 Hydrogen)

Comments

Popular posts from this blog

#4297 ISS Astronaut Unable to Speak

#3356 PHP and JavaScript to Retrieve and Set an API_Key

#4188 Hybrid/GraphRAG