#!/usr/bin/env python3

import json
import os
from pathlib import Path
from config import *

def load_prompt_template(template_name):
    """Load prompt template from file"""
    if not template_name.endswith('.txt'):
        template_name = template_name + '.txt'
    
    template_path = Path(TEMPLATES_DIR) / template_name
    if template_path.exists():
        with open(template_path, 'r', encoding='utf-8') as f:
            return f.read()
    else:
        return ""

def create_initial_or_expert_prompt(db):
    """Create initial OR expert analysis prompt"""
    db_id = db['db_id']
    table_names = db['table_names_original']
    columns = db['column_names_original']
    column_types = db['column_types']

    # Build table information
    table_info = {}
    for table_name in table_names:
        table_columns = []
        for idx, (table_idx, col_name) in enumerate(columns):
            if table_idx >= 0 and table_idx < len(table_names):
                if table_names[table_idx] == table_name:
                    col_type = column_types[idx] if idx < len(column_types) else 'text'
                    table_columns.append((col_name, col_type))
        table_info[table_name] = table_columns

    # Create schema section
    schema_section = f"Database: {db_id}\n\n"
    for table_name, table_columns in table_info.items():
        if not table_columns:
            continue
        ddl_columns = [f"{col_name} {col_type.upper()}" for col_name, col_type in table_columns]
        schema_section += f"```sql\n"
        schema_section += f"CREATE TABLE {table_name} (\n  " + ",\n  ".join(ddl_columns) + "\n);\n"
        schema_section += f"```\n\n"

    # Try to load from template first
    template = load_prompt_template("or_expert_initial")
    if template:
        prompt = template.format(
            MIN_CONSTRAINTS=MIN_CONSTRAINTS,
            MAX_CONSTRAINTS=MAX_CONSTRAINTS,
            MIN_SAMPLE_ROWS=MIN_SAMPLE_ROWS,
            schema_section=schema_section,
            db_id=db_id
        )
    else:
        # Fallback to hardcoded prompt
        prompt = f"""You are an Operations Research (OR) expert focused ONLY on optimization modeling. Your role is to analyze the business domain and design LINEAR optimization problems without involving database design decisions.

CRITICAL MATHEMATICAL CONSTRAINTS FOR LINEAR/MIXED-INTEGER PROGRAMMING:
- The optimization problem MUST be either Linear Programming (LP) or Mixed-Integer Programming (MIP)
- Objective function MUST be linear: minimize/maximize ∑(coefficient × variable)
- All constraints MUST be linear: ∑(coefficient × variable) ≤/≥/= constant
- Decision variables can be continuous (LP) or mixed continuous/integer (MIP)
- NO variable products, divisions, or other nonlinear relationships
- Design business scenarios that naturally lead to linear mathematical formulations
- Generate between {MIN_CONSTRAINTS} and {MAX_CONSTRAINTS} constraints for optimization feasibility

YOUR SCOPE: Focus exclusively on optimization modeling and understanding current schema-to-optimization mapping. Do NOT propose database changes.
ROW COUNT AWARENESS: Be aware that data engineer will apply {MIN_SAMPLE_ROWS}-row minimum rule - tables unable to generate sufficient meaningful rows will be moved to business_configuration_logic.json.

CRITICAL: Respond with ONLY a valid JSON object. No explanations, no markdown, no extra text.

{schema_section}

Current Business Configuration Logic: {{}} (initially empty)

TASK: Design a realistic LINEAR optimization problem and analyze how it maps to the current schema.

JSON STRUCTURE REQUIRED:

{{
  "database_id": "{db_id}",
  "iteration": 0,
  "business_context": "Describe the realistic business scenario that leads to linear optimization (avoid scenarios requiring nonlinear relationships)",
  "optimization_problem_description": "Detailed description of what needs to be optimized using linear objective functions and constraints",
  "optimization_formulation": {{
    "objective": "minimize/maximize specific_linear_metric with clear definition (sum of weighted variables only)",
    "decision_variables": "What linear variables can be controlled/decided (continuous or integer)",
    "constraints": "What linear limitations must be respected (no variable products or divisions) - design {MIN_CONSTRAINTS} to {MAX_CONSTRAINTS} constraints"
  }},
  
  "current_optimization_to_schema_mapping": {{
    "objective_coefficients": {{
      "coefficient_name[indices]": {{
        "currently_mapped_to": "table.column OR business_configuration_logic.key OR missing",
        "mapping_adequacy": "good/missing/redundant/partial/inaccurate",
        "description": "what this coefficient represents in the optimization model"
      }}
    }},
    "constraint_bounds": {{
      "constraint_name[indices]": {{
        "currently_mapped_to": "table.column OR business_configuration_logic.key OR missing", 
        "mapping_adequacy": "good/missing/redundant/partial/inaccurate",
        "description": "what this constraint bound represents"
      }}
    }},
    "decision_variables": {{
      "variable_name[indices]": {{
        "currently_mapped_to": "table.column OR business_configuration_logic.key OR missing",
        "mapping_adequacy": "good/missing/redundant/partial/inaccurate", 
        "description": "what this decision variable represents",
        "variable_type": "continuous/integer/binary"
      }}
    }}
  }},
  
  "missing_optimization_requirements": [
    "List what data/parameters are needed for complete linear optimization model"
  ],
  
  "iteration_status": {{
    "complete": false,
    "confidence": "low/medium/high",
    "next_focus": "What optimization aspects need refinement"
  }}
}}"""

    return prompt, table_info

def create_iterative_or_expert_prompt(db_id, state_info, schema_sql, data_dictionary, business_configuration_logic, iteration_num):
    """Create iterative OR expert prompt with enhanced state checking"""
    
    convergence_pressure = ""
    if iteration_num >= 3:
        convergence_pressure = f"""
CONVERGENCE PRESSURE (Iteration {iteration_num}/{MAX_ALTERNATING_ITERATIONS}):
- Focus on finalizing the linear optimization model
- Set complete=true if formulation is mathematically sound and linear
- Minimize further schema change requests
"""

    data_dict_section = ""
    if data_dictionary:
        data_dict_section = f"""
CURRENT DATA DICTIONARY:
{json.dumps(data_dictionary, indent=2)}
"""

    business_config_section = ""
    if business_configuration_logic:
        business_config_section = f"""
CURRENT BUSINESS CONFIGURATION LOGIC:
{json.dumps(business_configuration_logic, indent=2)}
"""

    state_checking_instructions = """
DATA AVAILABILITY CHECK: 
Before listing missing requirements, verify:
- Check current schema for required data columns
- Check business configuration logic for required parameters  
- Only list as "missing" if data is truly unavailable
- If all mappings are "good", missing_requirements should be []

CONSISTENCY RULES:
- IF all mapping_adequacy == "good" THEN missing_optimization_requirements = []
- IF missing_optimization_requirements = [] THEN complete CAN be true
- IF complete == true THEN confidence should be "high"

SELF-CHECK: Before responding, verify:
1. Does current schema contain the data I claim is missing?
2. Are my mapping assessments consistent with missing requirements?
3. Is my complete status consistent with missing requirements?

MAPPING COMPLETENESS CHECK: Ensure logical consistency between:
- All objective coefficients mapped with adequacy evaluation
- All constraint bounds mapped with adequacy evaluation  
- All decision variables mapped with adequacy evaluation
- Missing requirements list matches inadequate mappings only
"""

    # Try to load from template first
    template = load_prompt_template("or_expert_iterative")
    if template:
        prompt = template.format(
            iteration_num=iteration_num,
            MAX_ALTERNATING_ITERATIONS=MAX_ALTERNATING_ITERATIONS,
            MIN_CONSTRAINTS=MIN_CONSTRAINTS,
            MAX_CONSTRAINTS=MAX_CONSTRAINTS,
            MIN_SAMPLE_ROWS=MIN_SAMPLE_ROWS,
            state_checking_instructions=state_checking_instructions,
            convergence_pressure=convergence_pressure,
            db_id=db_id,
            iteration_num_minus_1=iteration_num-1,
            state_info=json.dumps(state_info, indent=2) if state_info else "{}",
            schema_sql=schema_sql,
            data_dict_section=data_dict_section,
            business_config_section=business_config_section,
            complete_status="true" if iteration_num >= 4 else "true/false based on linear formulation completeness"
        )
    else:
        # Fallback to hardcoded prompt
        prompt = f"""You are an Operations Research (OR) expert in iteration {iteration_num} of an alternating optimization process. The algorithm alternates between OR expert analysis and data engineering implementation until convergence.

CRITICAL MATHEMATICAL CONSTRAINTS FOR LINEAR/MIXED-INTEGER PROGRAMMING:
- The optimization problem MUST remain Linear Programming (LP) or Mixed-Integer Programming (MIP)
- Objective function MUST be linear: minimize/maximize ∑(coefficient × variable)
- All constraints MUST be linear: ∑(coefficient × variable) ≤/≥/= constant
- Decision variables can be continuous (LP) or mixed continuous/integer (MIP)
- NO variable products, divisions, or other nonlinear relationships
- If previous iteration introduced nonlinear elements, redesign as linear formulation
- Maintain between {MIN_CONSTRAINTS} and {MAX_CONSTRAINTS} constraints for optimization feasibility

YOUR SCOPE: Focus exclusively on optimization modeling and mapping analysis. Do NOT propose database changes.
ROW COUNT AWARENESS: Understand that data engineer applies {MIN_SAMPLE_ROWS}-row minimum rule - insufficient table data gets moved to business_configuration_logic.json.

{state_checking_instructions}

CRITICAL: Respond with ONLY a valid JSON object. No explanations, no markdown, no extra text.

{convergence_pressure}

CURRENT STATE (iteration {iteration_num-1}):
{json.dumps(state_info, indent=2) if state_info else "{}"}

CURRENT SCHEMA:
```sql
{schema_sql}
```
{data_dict_section}
{business_config_section}

TASK: Refine the optimization problem formulation by analyzing current data schema mapping and identifying requirements while maintaining LINEAR structure.

JSON STRUCTURE REQUIRED:

{{
  "database_id": "{db_id}",
  "iteration": {iteration_num},
  "business_context": "Updated realistic business scenario description that supports linear optimization",
  "optimization_problem_description": "Refined description of LINEAR optimization problem", 
  "optimization_formulation": {{
    "objective": "refined linear minimize/maximize with mathematical precision (sum of weighted variables only)",
    "decision_variables": "clearly defined controllable linear variables (continuous or integer)",
    "constraints": "mathematically precise LINEAR constraint definitions (no variable products or divisions) - maintain {MIN_CONSTRAINTS} to {MAX_CONSTRAINTS} constraints"
  }},
  
  "current_optimization_to_schema_mapping": {{
    "objective_coefficients": {{
      "coefficient_name[indices]": {{
        "currently_mapped_to": "table.column OR business_configuration_logic.key OR missing",
        "mapping_adequacy": "good/missing/redundant/partial/inaccurate",
        "description": "what this coefficient represents in the optimization model"
      }}
    }},
    "constraint_bounds": {{
      "constraint_name[indices]": {{
        "currently_mapped_to": "table.column OR business_configuration_logic.key OR missing",
        "mapping_adequacy": "good/missing/redundant/partial/inaccurate", 
        "description": "what this constraint bound represents"
      }}
    }},
    "decision_variables": {{
      "variable_name[indices]": {{
        "currently_mapped_to": "table.column OR business_configuration_logic.key OR missing",
        "mapping_adequacy": "good/missing/redundant/partial/inaccurate",
        "description": "what this decision variable represents",
        "variable_type": "continuous/integer/binary"
      }}
    }}
  }},
  
  "missing_optimization_requirements": [
    "List what data/parameters are still needed for linear formulation (should be [] if all mappings are good)"
  ],
  
  "iteration_status": {{
    "complete": {"true" if iteration_num >= 4 else "true/false based on linear formulation completeness"},
    "confidence": "updated confidence level",
    "next_focus": "What needs refinement for linear optimization OR 'Ready for convergence'"
  }}
}}"""

    return prompt

def create_data_engineer_prompt(or_analysis_json, db_id, iteration_num, current_schema_sql="", current_data_dictionary=None, current_business_configuration_logic=None):
    """Create data engineer prompt with business configuration logic design"""
    
    schema_context = ""
    if current_schema_sql:
        schema_context = f"""
CURRENT DATABASE SCHEMA:
```sql
{current_schema_sql}
```
"""
    
    data_dict_context = ""
    if current_data_dictionary:
        data_dict_context = f"""
CURRENT DATA DICTIONARY:
{json.dumps(current_data_dictionary, indent=2)}
"""
    
    business_config_context = ""
    if current_business_configuration_logic:
        business_config_context = f"""
CURRENT BUSINESS CONFIGURATION LOGIC:
{json.dumps(current_business_configuration_logic, indent=2)}
"""

    business_config_instructions = """
BUSINESS CONFIGURATION LOGIC DESIGN:
- Create business_configuration_logic.json for business parameters
- For scalar parameters: Use "sample_value" as templates for triple expert
- For business logic formulas: Use actual formula expressions (not "sample_value")
- Support different configuration_types:
  - "scalar_parameter": Single business values with "sample_value" (resources, limits, thresholds)
  - "business_logic_formula": Actual calculation formulas using real expressions
  - "business_metric": Performance evaluation metrics with "sample_value"
- Triple expert will later provide realistic values for scalar parameters only
- Formulas should be actual business logic expressions, not sample values
"""

    # Try to load from template first
    template = load_prompt_template("data_engineer")
    if template:
        prompt = template.format(
            iteration_num=iteration_num,
            MIN_SAMPLE_ROWS=MIN_SAMPLE_ROWS,
            MAX_SAMPLE_ROWS=MAX_SAMPLE_ROWS,
            business_config_instructions=business_config_instructions,
            or_analysis_json=or_analysis_json,
            schema_context=schema_context,
            data_dict_context=data_dict_context,
            business_config_context=business_config_context,
            db_id=db_id
        )
    else:
        # Fallback to hardcoded prompt
        prompt = f"""You are a senior database architect implementing schema modifications for iteration {iteration_num}. Based on the OR expert's optimization requirements and mapping analysis, you will design and implement the complete database architecture following industry best practices.

YOUR RESPONSIBILITIES:
- Analyze OR expert's mapping evaluations and missing requirements
- Design schema adjustments following database normalization principles
- Implement complete data dictionary with business-oriented descriptions
- Manage business configuration logic parameters (scalar values and formulas not suitable for tables)
- Maintain business realism by preserving relevant non-optimization tables
- Follow industry database design standards and naming conventions
- Ensure each table will store between {MIN_SAMPLE_ROWS} and {MAX_SAMPLE_ROWS} data rows for realistic optimization scenarios
- Apply the {MIN_SAMPLE_ROWS}-row minimum rule - if optimization information is insufficient to generate at least {MIN_SAMPLE_ROWS} meaningful rows for a table, move that information to business_configuration_logic.json instead.

{business_config_instructions}

CRITICAL: Respond with ONLY a valid JSON object. No explanations, no markdown, no extra text.

OR EXPERT ANALYSIS (iteration {iteration_num}):
{or_analysis_json}

{schema_context}
{data_dict_context}
{business_config_context}

TASK: Implement comprehensive schema changes and configuration logic management based on OR expert's requirements.

JSON STRUCTURE REQUIRED:

{{
  "database_id": "{db_id}",
  "iteration": {iteration_num},
  "implementation_summary": "Summary of schema changes and configuration logic updates based on OR expert mapping analysis",
  
  "or_requirements_analysis": {{
    "mapping_gaps_identified": [
      "List specific gaps identified from OR expert's mapping_adequacy assessments"
    ],
    "missing_data_requirements": [
      "List missing optimization data requirements from OR expert"
    ],
    "business_configuration_logic_needs": [
      "Scalar parameters and formulas better suited for configuration than tables"
    ]
  }},
  
  "schema_adjustment_decisions": {{
    "tables_to_delete": [
      {{
        "table_name": "table_name",
        "reason": "business justification for removal (optimization irrelevant vs business irrelevant)"
      }}
    ],
    "tables_to_create": [
      {{
        "table_name": "table_name", 
        "purpose": "optimization role (decision_variables/objective_coefficients/constraint_bounds/business_data)",
        "business_meaning": "what this table represents in business context"
      }}
    ],
    "tables_to_modify": [
      {{
        "table_name": "existing_table",
        "changes": "specific modifications needed",
        "reason": "why these changes address OR expert's mapping gaps"
      }}
    ]
  }},
  
  "business_configuration_logic_updates": {{
    "configuration_parameters": {{
      "parameter_name": {{
        "sample_value": "sample_parameter_value",
        "data_type": "INTEGER/FLOAT/STRING/BOOLEAN",
        "business_meaning": "what this parameter represents in business context",
        "optimization_role": "how this parameter is used in optimization model",
        "configuration_type": "scalar_parameter"
      }},
      "formula_name": {{
        "formula_expression": "actual_business_formula_expression",
        "data_type": "STRING",
        "business_meaning": "what this formula represents in business context",
        "optimization_role": "how this formula is used in optimization model",
        "configuration_type": "business_logic_formula"
      }}
    }},
    "updates_rationale": "why these parameters are better in configuration logic than tables"
  }},
  
  "implemented_optimization_mapping": {{
    "objective_coefficients_mapping": {{
      "coefficient_name[indices]": "table.column OR business_configuration_logic.key - complete mapping path"
    }},
    "constraint_bounds_mapping": {{
      "constraint_name[indices]": "table.column OR business_configuration_logic.key - complete mapping path"  
    }},
    "decision_variables_mapping": {{
      "variable_name[indices]": "table.column OR business_configuration_logic.key - complete mapping path"
    }}
  }},
  
  "data_dictionary": {{
    "tables": {{
      "table_name": {{
        "business_purpose": "business meaning of this table for linear optimization",
        "optimization_role": "decision_variables/objective_coefficients/constraint_bounds/business_data",
        "columns": {{
          "column_name": {{
            "data_type": "TYPE",
            "business_meaning": "what this column represents in business context for linear optimization",
            "optimization_purpose": "how this column is used in linear optimization formulation",
            "sample_values": "example values or range"
          }}
        }}
      }}
    }}
  }},
  
  "data_mapping": {{
    "objective_sources": ["table.column pairs for linear objective function"],
    "constraint_sources": ["table.column pairs for linear constraints"],
    "sample_data_rows": {{"table1": 3, "table2": 5}}
  }},
  
  "validation": {{
    "schema_complete": true,
    "data_consistent": true,
    "math_traceable": true,
    "business_aligned": true,
    "follows_db_standards": true
  }}
}}"""
    
    return prompt

def create_triple_expert_prompt(final_or_analysis, final_implementation, final_schema_sql, final_data_dictionary, final_business_configuration_logic, db_id):
    """Create triple expert prompt for realistic data generation with business configuration values"""
    
    business_config_section = ""
    if final_business_configuration_logic:
        business_config_section = f"""
BUSINESS CONFIGURATION LOGIC:
{json.dumps(final_business_configuration_logic, indent=2)}
"""

    business_config_instructions = f"""
BUSINESS CONFIGURATION INSTRUCTIONS:
- business_configuration_logic.json contains templates for scalar parameters with "sample_value"
- This includes parameters that were moved from potential tables due to insufficient row generation capability (minimum {MIN_SAMPLE_ROWS} rows rule)
- Your task: Replace "sample_value" with realistic "value" for scalar_parameter types
- Keep business_logic_formula expressions unchanged - DO NOT modify formulas
- Provide business_justification for each scalar value change
- Do not modify business_logic_formula or business_metric formulas
"""

    # Try to load from template first
    template = load_prompt_template("triple_expert")
    if template:
        prompt = template.format(
            business_config_instructions=business_config_instructions,
            final_or_analysis=json.dumps(final_or_analysis, indent=2),
            final_implementation=json.dumps(final_implementation, indent=2),
            final_schema_sql=final_schema_sql,
            final_data_dictionary=json.dumps(final_data_dictionary, indent=2),
            business_config_section=business_config_section,
            MIN_SAMPLE_ROWS=MIN_SAMPLE_ROWS,
            MAX_SAMPLE_ROWS=MAX_SAMPLE_ROWS,
            db_id=db_id
        )
    else:
        # Fallback to hardcoded prompt
        prompt = f"""You are a triple expert with deep knowledge in business operations, data management, and optimization modeling. Your task is to generate realistic, non-trivial, and solvable data values for the optimization problem given the final OR analysis, database schema, and business configuration logic.

{business_config_instructions}

CRITICAL: Respond with ONLY a valid JSON object. No explanations, no markdown, no extra text.

FINAL OR ANALYSIS:
{json.dumps(final_or_analysis, indent=2)}

FINAL IMPLEMENTATION:
{json.dumps(final_implementation, indent=2)}

FINAL SCHEMA:
```sql
{final_schema_sql}
```

DATA DICTIONARY:
{json.dumps(final_data_dictionary, indent=2)}

{business_config_section}

DATA GENERATION CONSTRAINTS:
- Minimum {MIN_SAMPLE_ROWS} rows per table
- Maximum {MAX_SAMPLE_ROWS} rows per table
- Values must be realistic and business-appropriate
- Data must enable a solvable optimization problem
- Consider business relationships between tables
- Respect business configuration logic parameters

TASK: Generate realistic data values that make the optimization problem meaningful and solvable.

JSON STRUCTURE REQUIRED:

{{
  "database_id": "{db_id}",
  "data_generation_approach": "Description of how you determined realistic values",
  "business_rationale": "Why these values make business sense",
  "optimization_solvability": "How these values ensure the problem is solvable",
  
  "generated_data": {{
    "table_name": [
      {{
        "column1": "realistic_value1",
        "column2": "realistic_value2",
        "business_justification": "why these specific values"
      }},
      {{
        "column1": "realistic_value3", 
        "column2": "realistic_value4",
        "business_justification": "why these specific values"
      }}
    ]
  }},
  
  "business_configuration_values": {{
    "parameter_name": {{
      "value": "realistic_parameter_value",
      "business_justification": "why this parameter value is realistic"
    }}
  }},
  
  "data_relationships": {{
    "cross_table_consistency": "How values across tables relate logically",
    "optimization_coefficients": "How values support the objective function",
    "constraint_feasibility": "How values ensure constraints are satisfiable",
    "configuration_integration": "How business configuration parameters integrate with table data"
  }},
  
  "validation": {{
    "business_realistic": true,
    "optimization_solvable": true,
    "data_consistent": true,
    "non_trivial": true
  }}
}}"""
    
    return prompt

def create_problem_description_prompt(final_or_analysis, final_implementation, schema_sql, data_sql, data_dictionary, business_configuration, expected_alternating_formulation, db_id):
    """Create structured markdown documentation for sections 1-3 with enhanced mathematical consistency and auto-extracted business configuration"""
    
    from utils import extract_business_configuration_requirements, format_business_requirements_for_prompt
    
    scalar_parameters, business_formulas = extract_business_configuration_requirements(business_configuration)
    business_requirements = format_business_requirements_for_prompt(scalar_parameters, business_formulas)
    
    data_dict_section = ""
    if data_dictionary:
        data_dict_section = f"""
DATA DICTIONARY:
{json.dumps(data_dictionary, indent=2)}
"""

    business_config_section = ""
    if business_configuration:
        business_config_section = f"""
BUSINESS CONFIGURATION:
{json.dumps(business_configuration, indent=2)}

Business Configuration Design: 
Our system separates business logic design from value determination:
- Configuration Logic (business_configuration_logic.json): Templates designed by data engineers with sample_value for scalars and actual formulas for business logic
- Configuration Values (business_configuration.json): Realistic values determined by domain experts for scalar parameters only
- Design Rationale: Ensures business logic consistency while allowing flexible parameter tuning
"""

    expected_objective = expected_alternating_formulation.get("objective_function", {}).get("description", "")
    expected_decision_vars = expected_alternating_formulation.get("decision_variables", {}).get("definition", "")
    expected_constraints = expected_alternating_formulation.get("constraints", [])
    
    auto_context_requirements = []
    auto_context_requirements.append(f"- Business decisions match expected decision variables: {expected_decision_vars}")
    auto_context_requirements.append(f"- Operational parameters align with expected linear objective: {expected_objective}")
    auto_context_requirements.extend([f"- {req}" for req in business_requirements])
    auto_context_requirements.extend([
        "- Use natural language to precisely describe linear mathematical relationships",
        "- NO mathematical formulas, equations, or symbolic notation",
        "- Present data as current operational information",
        "- Focus on precise operational decision-making that leads to linear formulations",
        "- Resource limitations match expected linear constraints",
        "- Avoid scenarios requiring variable products, divisions, or other nonlinear relationships",
        "- Include specific operational parameters that map to expected coefficient sources",
        "- Reference business configuration parameters where appropriate"
    ])

    # Try to load from template first
    template = load_prompt_template("problem_description")
    if template:
        prompt = template.format(
            final_or_analysis_iteration=final_or_analysis.get('iteration', 'unknown'),
            auto_context_requirements=chr(10).join(auto_context_requirements),
            final_or_analysis=json.dumps(final_or_analysis, indent=2),
            final_schema_sql=schema_sql,
            final_data_sql=data_sql,
            data_dict_section=data_dict_section,
            business_config_section=business_config_section,
            db_id=db_id,
            expected_goal=expected_alternating_formulation.get("objective_function", {}).get("goal", ""),
            expected_objective=expected_objective,
            expected_constraints=chr(10).join([f"- Expected constraint: {c.get('description', '')} (Form: {c.get('mathematical_form', '')})" for c in expected_constraints])
        )
    else:
        # Fallback to hardcoded prompt
        prompt = f"""You are a business analyst creating structured optimization problem documentation.

DATA SOURCES EXPLANATION:
- FINAL OR ANALYSIS: Final converged optimization problem from alternating process (iteration {final_or_analysis.get('iteration', 'unknown')}), contains business context and schema mapping evaluation
- DATABASE SCHEMA: Current database structure after iterative adjustments  
- DATA DICTIONARY: Business meanings and optimization roles of tables and columns
- CURRENT STORED VALUES: Realistic business data generated by triple expert (business + data + optimization)
- BUSINESS CONFIGURATION: Scalar parameters and business logic formulas separated from table data

CRITICAL REQUIREMENTS: 
- Ensure problem description naturally leads to LINEAR or MIXED-INTEGER optimization formulation
- Make business context consistent with the intended decision variables and objectives
- Align constraint descriptions with expected mathematical constraints
- Ensure data descriptions map clearly to expected coefficient sources
- Maintain business authenticity while fixing mathematical consistency issues
- Avoid business scenarios that would naturally require nonlinear relationships (variable products, divisions, etc.)

AUTO-EXTRACTED CONTEXT REQUIREMENTS:
{chr(10).join(auto_context_requirements)}

FINAL OR ANALYSIS:
{json.dumps(final_or_analysis, indent=2)}

FINAL DATABASE SCHEMA:
```sql
{schema_sql}
```

CURRENT STORED VALUES:
```sql
{data_sql}
```
{data_dict_section}

BUSINESS CONFIGURATION:
{business_config_section}

TASK: Create structured markdown documentation for SECTIONS 1-3 ONLY (Problem Description).

EXACT MARKDOWN STRUCTURE TO FOLLOW:

# Complete Optimization Problem and Solution: {db_id}

## 1. Problem Context and Goals

### Context  
[Regenerate business context that naturally aligns with LINEAR optimization formulation. Ensure:]
{chr(10).join(auto_context_requirements)}
- CRITICAL: Include ALL business configuration information (scalar parameters AND business logic formulas) in natural business language

### Goals  
[Regenerate goals that clearly lead to LINEAR mathematical objective:]
- Optimization goal: {expected_alternating_formulation.get("objective_function", {}).get("goal", "")}
- Metric to optimize: {expected_objective}
- Success measurement aligned with expected coefficient sources
- Use natural language to precisely describe linear optimization goal
- NO mathematical formulas, equations, or symbolic notation

## 2. Constraints    

[Regenerate constraints that directly match expected LINEAR mathematical constraints:]
{chr(10).join([f"- Expected constraint: {c.get('description', '')} (Form: {c.get('mathematical_form', '')})" for c in expected_constraints])}

[Each constraint should be described in business terms that naturally lead to LINEAR mathematical forms (no variable products or divisions)]

## 3. Available Data  

### Database Schema  
```sql
{schema_sql}
```

### Data Dictionary  
[Create comprehensive business-oriented data dictionary mapping tables and columns to their business purposes and optimization roles - NOT technical database terms. Base this on the data_dictionary provided but rewrite in business language that clearly connects to the expected linear mathematical formulation]

### Current Stored Values  
```sql
{data_sql}
```

STOP HERE - Do not include section 4 (Mathematical Optimization Formulation).
"""
    
    return prompt

def create_mathematical_solution_prompt(problem_description_text, db_id):
    """Create mathematical solution prompt for section 4"""
    
    # Try to load from template first
    template = load_prompt_template("mathematical_solution")
    if template:
        prompt = template.format(
            lesson_section="",
            problem_description_text=problem_description_text,
            db_id=db_id
        )
    else:
        # Fallback to hardcoded prompt
        prompt = f"""You are an operations research expert creating the mathematical optimization formulation.

CRITICAL MATHEMATICAL CONSTRAINTS FOR LINEAR/MIXED-INTEGER PROGRAMMING:
- Generate ONLY Linear Programming (LP) or Mixed-Integer Programming (MIP) formulations
- Objective function MUST be linear: minimize/maximize ∑(coefficient × variable)
- All constraints MUST be linear: ∑(coefficient × variable) ≤/≥/= constant
- Decision variables can be continuous (LP) or mixed continuous/integer (MIP)
- NO variable products (x[i] × x[j]), divisions (x[i] / x[j]), or other nonlinear terms
- Use linear combinations of variables only

BUSINESS PROBLEM DESCRIPTION (Sections 1-3):
{problem_description_text}

TASK: Create section 4 (Mathematical Optimization Formulation) based on the business problem using LINEAR formulation only.

EXACT FORMAT TO GENERATE:

## 4. Mathematical Optimization Formulation

#### Decision Variables
[Define variables based on controllable decisions]

#### Objective Function
[Create LINEAR objective function using specific coefficients from data: minimize/maximize ∑(coefficient × variable)]

#### Constraints
[Convert business constraints into LINEAR mathematical form: ∑(coefficient × variable) ≤/≥/= constant]

Data Source Verification:
[Specify which table.column or business_configuration.parameter each coefficient comes from]

Generate a complete, immediately solvable LINEAR mathematical model with all numerical coefficients from the provided data.
"""
    
    return prompt

def create_mathematical_solution_prompt_with_lesson(problem_description_text, lesson, db_id):
    """Create mathematical solution prompt with lesson from diagnosis"""
    
    lesson_section = ""
    if lesson:
        lesson_section = f"""
BASED ON VERIFICATION FAILURE DIAGNOSIS:
- Key Insight: {lesson.get('key_insight', '')}
- Specific Guidance: {lesson.get('specific_guidance', '')}
- Focus Areas: {', '.join(lesson.get('focus_areas', []))}

Please pay special attention to the above guidance when generating the mathematical model.
"""

    # Try to load from template first
    template = load_prompt_template("mathematical_solution")
    if template:
        prompt = template.format(
            lesson_section=lesson_section,
            problem_description_text=problem_description_text,
            db_id=db_id
        )
    else:
        # Fallback to hardcoded prompt
        prompt = f"""You are an operations research expert creating the mathematical optimization formulation.

{lesson_section}

CRITICAL MATHEMATICAL CONSTRAINTS FOR LINEAR/MIXED-INTEGER PROGRAMMING:
- Generate ONLY Linear Programming (LP) or Mixed-Integer Programming (MIP) formulations
- Objective function MUST be linear: minimize/maximize ∑(coefficient × variable)
- All constraints MUST be linear: ∑(coefficient × variable) ≤/≥/= constant
- Decision variables can be continuous (LP) or mixed continuous/integer (MIP)
- NO variable products (x[i] × x[j]), divisions (x[i] / x[j]), or other nonlinear terms
- Use linear combinations of variables only

BUSINESS PROBLEM DESCRIPTION (Sections 1-3):
{problem_description_text}

TASK: Create section 4 (Mathematical Optimization Formulation) based on the business problem using LINEAR formulation only, paying special attention to the diagnostic guidance above.

EXACT FORMAT TO GENERATE:

## 4. Mathematical Optimization Formulation

#### Decision Variables
[Define variables based on controllable decisions]

#### Objective Function
[Create LINEAR objective function using specific coefficients from data: minimize/maximize ∑(coefficient × variable)]

#### Constraints
[Convert business constraints into LINEAR mathematical form: ∑(coefficient × variable) ≤/≥/= constant]

Data Source Verification:
[Specify which table.column or business_configuration.parameter each coefficient comes from]

Generate a complete, immediately solvable LINEAR mathematical model with all numerical coefficients from the provided data.
"""
    
    return prompt

def create_mathematical_verification_prompt(problem_description_text, generated_mathematical_solution, expected_alternating_formulation, db_id):
    """Create verification prompt with explicit quantitative scoring criteria"""
    
    prompt = f"""You are a mathematical verification expert comparing optimization formulations with EXPLICIT SCORING CRITERIA.

DATABASE ID: {db_id}

GENERATED MATHEMATICAL SOLUTION:
{generated_mathematical_solution}

EXPECTED FORMULATION:
{json.dumps(expected_alternating_formulation, indent=2)}

QUANTITATIVE SCORING CRITERIA (Total = 1.0):

1. OBJECTIVE FUNCTION MATCHING (0.30 points):
   - Optimization direction (minimize/maximize): 0.10
   - Coefficient variables match expected sources: 0.10  
   - Mathematical expression structure (linear form): 0.10
   
2. DECISION VARIABLES CONSISTENCY (0.25 points):
   - Variable names/indices match expected mapping: 0.10
   - Variable types (continuous/integer/binary) correct: 0.08
   - Variable definitions clearly stated: 0.07

3. CONSTRAINTS COMPLETENESS (0.30 points):
   - All expected constraint types present: 0.15
   - Constraint mathematical forms correct (≤/≥/=): 0.08
   - Right-hand-side values reference correct data sources: 0.07

4. DATA SOURCE VERIFICATION (0.15 points):
   - Coefficient sources correctly mapped to table.column: 0.08
   - Business configuration parameters properly referenced: 0.07

SCORING INSTRUCTIONS:
- Award FULL points if component perfectly matches expected formulation
- Award PARTIAL points (0.3x, 0.5x, 0.7x) for partial matches
- Award ZERO points if component is missing or completely incorrect
- SUM all component scores to get final consistency_score (0.0 to 1.0)

RESPONSE FORMAT (JSON only):

{{
  "database_id": "{db_id}",
  "verification_results": {{
    "detailed_scoring": {{
      "objective_function": {{
        "optimization_direction_score": 0.0,
        "coefficient_mapping_score": 0.0, 
        "expression_structure_score": 0.0,
        "subtotal": 0.0,
        "issues": ["List specific issues found"]
      }},
      "decision_variables": {{
        "variable_mapping_score": 0.0,
        "variable_types_score": 0.0,
        "variable_definitions_score": 0.0,
        "subtotal": 0.0,
        "issues": ["List specific issues found"]
      }},
      "constraints": {{
        "constraint_completeness_score": 0.0,
        "constraint_forms_score": 0.0,
        "rhs_mapping_score": 0.0,
        "subtotal": 0.0,
        "issues": ["List specific issues found"]
      }},
      "data_sources": {{
        "coefficient_sources_score": 0.0,
        "business_config_score": 0.0,
        "subtotal": 0.0,
        "issues": ["List specific issues found"]
      }}
    }},
    "overall_consistency": "high/medium/low",
    "consistency_score": 0.0,
    "passes_verification": false,
    "scoring_rationale": "Detailed explanation of how you calculated the consistency_score",
    "specific_issues": [
      {{
        "issue_type": "objective_mismatch/variable_mismatch/constraint_mismatch/data_source_mismatch",
        "severity": "high/medium/low",
        "description": "Specific description of the issue",
        "score_impact": "How much this issue reduced the score"
      }}
    ]
  }},
  "improvement_recommendations": {{
    "regenerate_problem_description": false,
    "regenerate_mathematical_solution": false,
    "specific_fixes_needed": [
      "Actionable fix 1: Add missing capacity constraints",
      "Actionable fix 2: Correct variable indexing from routes.id",
      "Actionable fix 3: Reference flight_schedule.cost in objective"
    ]
  }}
}}

EXAMPLE SCORING CALCULATION:
If objective direction correct (0.10) + coefficient mapping 70% correct (0.07) + expression structure correct (0.10) + variables 60% correct (0.15) + constraints 80% correct (0.24) + data sources 50% correct (0.075) = Total: 0.705

CRITICAL: Show your detailed scoring calculation in scoring_rationale field."""
    
    return prompt

def create_detailed_verification_diagnosis_prompt(original_seed_problem, problem_description, mathematical_solution, verification_result, expected_formulation, db_id):
    """Create enhanced diagnosis prompt with specific issue identification and incremental modification guidance"""
    
    prompt = f"""You are an optimization problem diagnostic expert. Analyze the verification failure to identify SPECIFIC issues and provide ACTIONABLE fixes with minimal modification guidance.

ORIGINAL SEED PROBLEM:
{json.dumps(original_seed_problem, indent=2)}

GENERATED PROBLEM DESCRIPTION (Sections 1-3):
{problem_description}

GENERATED MATHEMATICAL SOLUTION (Section 4):
{mathematical_solution}

VERIFICATION FAILURE RESULT:
{json.dumps(verification_result, indent=2)}

EXPECTED MATHEMATICAL FORMULATION:
{json.dumps(expected_formulation, indent=2)}

TASK: Provide SPECIFIC diagnosis with ACTIONABLE fixes and INCREMENTAL modification guidance.

JSON RESPONSE FORMAT:

{{
  "database_id": "{db_id}",
  "root_cause_analysis": {{
    "primary_issue_source": "problem_description/mathematical_solution/both",
    "confidence": "high/medium/low",
    "reasoning": "Why this is identified as the primary issue source"
  }},
  "specific_issues": [
    {{
      "location": "Section 1.2/Section 2.3/Section 4 Objective Function/etc.",
      "problem": "Specific issue description",
      "current_text": "Current problematic text/formulation",
      "required_fix": "Specific actionable fix needed",
      "severity": "high/medium/low",
      "impact_on_verification": "How this affects verification score"
    }}
  ],
  "problem_description_issues": [
    {{
      "section": "Section 1/Section 2/Section 3",
      "issue": "Specific problem description issue",
      "severity": "high/medium/low",
      "missing_element": "Missing key information",
      "current_content": "Current text that needs improvement",
      "specific_improvement": "Exact content to add/modify",
      "preserve_content": "Content that should be kept unchanged"
    }}
  ],
  "mathematical_solution_issues": [
    {{
      "component": "Decision Variables/Objective Function/Constraints/Data Sources",
      "issue": "Specific mathematical modeling issue", 
      "severity": "high/medium/low",
      "correction_needed": "Specific correction required",
      "current_formulation": "Current problematic formulation",
      "target_formulation": "Expected correct formulation"
    }}
  ],
  "targeted_lesson": {{
    "key_insight": "Specific actionable insight (1-2 sentences)",
    "specific_guidance": "Detailed actionable guidance",
    "focus_areas": ["Specific areas that need focused attention"],
    "modification_strategy": "incremental/section_specific/complete_rewrite",
    "preserve_elements": ["Specific sections/content to preserve"],
    "specific_changes": ["Specific targeted changes to make"]
  }}
}}"""
    
    return prompt

def create_incremental_problem_description_prompt(current_description, lesson, final_or_analysis, final_implementation, schema_sql, data_sql, data_dictionary, business_configuration, expected_alternating_formulation, db_id):
    """Create incremental problem description modification prompt with specific fixes"""
    
    from utils import extract_business_configuration_requirements, format_business_requirements_for_prompt
    
    scalar_parameters, business_formulas = extract_business_configuration_requirements(business_configuration)
    business_requirements = format_business_requirements_for_prompt(scalar_parameters, business_formulas)
    
    modification_instructions = ""
    if lesson:
        preserve_sections = lesson.get('preserve_elements', [])
        modify_sections = lesson.get('specific_changes', [])
        
        modification_instructions = f"""
INCREMENTAL MODIFICATION INSTRUCTIONS:
- PRESERVE: {', '.join(preserve_sections) if preserve_sections else 'Good existing content'}
- MODIFY ONLY: {', '.join(modify_sections) if modify_sections else 'Areas identified in diagnosis'}
- Key Insight: {lesson.get('key_insight', '')}
- Specific Guidance: {lesson.get('specific_guidance', '')}
- Focus Areas: {', '.join(lesson.get('focus_areas', []))}

MODIFICATION TYPE: Make MINIMAL targeted changes to fix specific issues while preserving good content.
"""

    data_dict_section = ""
    if data_dictionary:
        data_dict_section = f"""
DATA DICTIONARY:
{json.dumps(data_dictionary, indent=2)}
"""

    business_config_section = ""
    if business_configuration:
        business_config_section = f"""
BUSINESS CONFIGURATION:
{json.dumps(business_configuration, indent=2)}
"""

    expected_objective = expected_alternating_formulation.get("objective_function", {}).get("description", "")
    expected_decision_vars = expected_alternating_formulation.get("decision_variables", {}).get("definition", "")

    prompt = f"""You are a business analyst making INCREMENTAL improvements to the problem description based on diagnostic feedback.

{modification_instructions}

CURRENT PROBLEM DESCRIPTION:
{current_description}

CONTEXT FOR IMPROVEMENTS:

FINAL OR ANALYSIS:
{json.dumps(final_or_analysis, indent=2)}

FINAL DATABASE SCHEMA:
```sql
{schema_sql}
```

CURRENT STORED VALUES:
```sql
{data_sql}
```
{data_dict_section}

BUSINESS CONFIGURATION:
{business_config_section}

TASK: Make TARGETED improvements to the existing problem description based on the diagnostic guidance while preserving good content.

IMPROVEMENT REQUIREMENTS:
- Keep the same overall markdown structure
- Preserve sections/content identified as good in the diagnosis
- Make specific improvements only where identified
- Ensure descriptions naturally lead to LINEAR optimization formulation
- Expected objective alignment: {expected_objective}
- Expected decision variables: {expected_decision_vars}
- Include business configuration information in natural language

OUTPUT: Return the improved problem description with minimal targeted changes, preserving the good existing content.
"""
    
    return prompt

def create_solver_code_prompt_with_template(problem_description_text, mathematical_solution, solver_type, db_id, template_code=""):
    """Create prompt for generating solver-specific code with template guidance"""
    
    solver_specific_instructions = {
        "gurobipy": f"""
Gurobipy 12.0.2 API Guidelines:

Key Syntax Rules:
- Constraints: `model.addConstr(expr <= rhs, name="constraint_name")`
- Summations: `gp.quicksum(coef[i] * var[i] for i in range(n))`  
- Array Safety: Always validate lengths before loops
- Variables: `model.addVar(vtype=GRB.CONTINUOUS, name="x", lb=0)`

Common Error Patterns to Avoid:
- `model.addConstr(expr, GRB.LESS_EQUAL, rhs)` (old syntax)
- `sum(...)` (use `gp.quicksum()` instead)  
- Using arrays without length validation

Template Reference (Gurobipy 12.0.2):
```python
{template_code}
```
""",
        "docplex": f"""
DOCplex 2.29.245 API Guidelines:

Key Syntax Rules:
- Constraints: `mdl.add_constraint(expr <= rhs, ctname="constraint_name")`
- Summations: `mdl.sum(coef[i] * var[i] for i in safe_range)`
- Array Safety: `safe_range = range(min(len(arr1), len(arr2)))`
- Variables: `mdl.continuous_var(name="x", lb=0)`

Critical Array Safety (prevents IndexError):
- Always check: `assert len(array1) == len(array2) == expected_len`
- Use safe indexing: `safe_range = range(min(len(array1), len(array2)))`
- Never: `for i in range(len(array1)): expr += array2[i]` if arrays differ in length

Template Reference (DOCplex 2.29.245):
```python
{template_code}
```
""",
        "pyomo": f"""
Pyomo 6.9.2 API Guidelines:

Key Syntax Rules:
- Model: `model = pyo.ConcreteModel()`
- Variables: `model.x = pyo.Var(model.I, within=pyo.NonNegativeReals)`
- Constraints: Use rule functions for indexed constraints
- Objective: `model.obj = pyo.Objective(rule=obj_rule, sense=pyo.minimize)`
- Solving: `solver = SolverFactory('gurobi'); results = solver.solve(model)`

Rule Function Pattern:
```python
def constraint_rule(model, i):
    return model.x[i] <= model.demand[i]
model.constraint = pyo.Constraint(model.I, rule=constraint_rule)
```

Template Reference (Pyomo 6.9.2):
```python
{template_code}
```
"""
    }

    template = load_prompt_template(f"solver_{solver_type}")
    if template:
        prompt = template.format(
            solver_type=solver_type.upper(),
            problem_description_text=problem_description_text,
            mathematical_solution=mathematical_solution,
            solver_instructions=solver_specific_instructions.get(solver_type, ""),
            template_code=template_code
        )
    else:
        prompt = f"""You are an optimization expert creating {solver_type.upper()} code for this mathematical problem.

CRITICAL: Follow the template patterns exactly to avoid syntax errors.

Business Problem:
{problem_description_text}

Mathematical Formulation:
{mathematical_solution}

{solver_specific_instructions.get(solver_type, "")}

Task: Generate complete, executable {solver_type.upper()} code that:
1. Follows the template syntax patterns exactly
2. Includes proper error handling
3. Validates array lengths before using in loops
4. Outputs "Optimal value: {{value}}" format
5. Handles infeasible/unbounded cases

Output Format:
```python
# Complete {solver_type.upper()} implementation
```

Generate working code based on the template patterns above."""
    
    return prompt

def create_solver_code_retry_prompt_with_template(problem_description_text, mathematical_solution, solver_type, db_id, template_code="", latest_round_results=None, retry_attempt=1):
    """Create prompt for retry solver code generation with only latest round context"""
    
    solver_specific_instructions = {
        "gurobipy": f"""
Gurobipy 12.0.2 API Guidelines:

Key Syntax Rules:
- Constraints: `model.addConstr(expr <= rhs, name="constraint_name")`
- Summations: `gp.quicksum(coef[i] * var[i] for i in range(n))`  
- Array Safety: Always validate lengths before loops
- Variables: `model.addVar(vtype=GRB.CONTINUOUS, name="x", lb=0)`

Common Error Patterns to Avoid:
- `model.addConstr(expr, GRB.LESS_EQUAL, rhs)` (old syntax)
- `sum(...)` (use `gp.quicksum()` instead)  
- Using arrays without length validation

Template Reference (Gurobipy 12.0.2):
```python
{template_code}
```
""",
        "docplex": f"""
DOCplex 2.29.245 API Guidelines:

Key Syntax Rules:
- Constraints: `mdl.add_constraint(expr <= rhs, ctname="constraint_name")`
- Summations: `mdl.sum(coef[i] * var[i] for i in safe_range)`
- Array Safety: `safe_range = range(min(len(arr1), len(arr2)))`
- Variables: `mdl.continuous_var(name="x", lb=0)`

Critical Array Safety (prevents IndexError):
- Always check: `assert len(array1) == len(array2) == expected_len`
- Use safe indexing: `safe_range = range(min(len(array1), len(array2)))`
- Never: `for i in range(len(array1)): expr += array2[i]` if arrays differ in length

Template Reference (DOCplex 2.29.245):
```python
{template_code}
```
""",
        "pyomo": f"""
Pyomo 6.9.2 API Guidelines:

Key Syntax Rules:
- Model: `model = pyo.ConcreteModel()`
- Variables: `model.x = pyo.Var(model.I, within=pyo.NonNegativeReals)`
- Constraints: Use rule functions for indexed constraints
- Objective: `model.obj = pyo.Objective(rule=obj_rule, sense=pyo.minimize)`
- Solving: `solver = SolverFactory('gurobi'); results = solver.solve(model)`

Rule Function Pattern:
```python
def constraint_rule(model, i):
    return model.x[i] <= model.demand[i]
model.constraint = pyo.Constraint(model.I, rule=constraint_rule)
```

Template Reference (Pyomo 6.9.2):
```python
{template_code}
```
"""
    }

    latest_context = ""
    if latest_round_results:
        latest_context = f"""
LATEST ROUND RESULTS (Retry Attempt {retry_attempt}):

"""
        
        error_statuses = ["error", "execution_error", "timeout", "code_generation_failed", "generation_error"]
        has_errors = any(latest_round_results.get(solver, {}).get("status", "") in error_statuses
                        for solver in ["gurobipy", "docplex", "pyomo"])
        
        optimal_values = []
        for solver in ["gurobipy", "docplex", "pyomo"]:
            result = latest_round_results.get(solver, {})
            if result.get("status") == "optimal" and result.get("optimal_value") is not None:
                optimal_values.append(result.get("optimal_value"))
        
        has_inconsistency = False
        if len(optimal_values) >= 2:
            base_value = optimal_values[0]
            has_inconsistency = any(abs(value - base_value) > SOLVER_CONSISTENCY_TOLERANCE 
                                  for value in optimal_values[1:])
        
        for solver in ["gurobipy", "docplex", "pyomo"]:
            result = latest_round_results.get(solver, {})
            status = result.get("status", "unknown")
            optimal_value = result.get("optimal_value")
            error_msg = result.get("error_message", "")
            
            latest_context += f"""
{solver.upper()} Latest Result:
- Status: {status}
- Optimal Value: {optimal_value if optimal_value is not None else "N/A"}
- Error: {error_msg if error_msg else "None"}

"""

        if has_errors and has_inconsistency:
            latest_context += f"""
LATEST ROUND ISSUES: Some solvers failed with errors AND the successful ones produced different optimal values.
YOUR TASK: Generate a corrected {solver_type.upper()} implementation that:
1. Fixes any syntax/runtime errors from latest round
2. Produces consistent results with other successful solvers
CRITICAL: Review both the code syntax and mathematical formulation carefully.
"""
        elif has_errors:
            latest_context += f"""
LATEST ROUND ISSUES: One or more solvers failed with execution errors.
YOUR TASK: Generate a corrected {solver_type.upper()} implementation that fixes the errors from latest round.
CRITICAL: Review the error messages and ensure proper syntax, array bounds, and API usage.
"""
        elif has_inconsistency:
            latest_context += f"""
LATEST ROUND ISSUES: The three solvers produced different optimal values. 
YOUR TASK: Generate a corrected {solver_type.upper()} implementation that should produce consistent results with the other solvers (when they're optimal).
CRITICAL: Review the mathematical formulation carefully and ensure your implementation correctly represents all constraints and the objective function.
"""

    template = load_prompt_template(f"solver_{solver_type}_retry")
    if template:
        prompt = template.format(
            solver_type=solver_type.upper(),
            retry_attempt=retry_attempt,
            problem_description_text=problem_description_text,
            mathematical_solution=mathematical_solution,
            latest_context=latest_context,
            solver_instructions=solver_specific_instructions.get(solver_type, ""),
            template_code=template_code
        )
    else:
        prompt = f"""You are an optimization expert creating {solver_type.upper()} code for this mathematical problem.

CRITICAL: This is retry attempt {retry_attempt} due to solver issues. Follow the template patterns exactly and ensure mathematical correctness.

Business Problem:
{problem_description_text}

Mathematical Formulation:
{mathematical_solution}

{latest_context}

{solver_specific_instructions.get(solver_type, "")}

Task: Generate complete, executable {solver_type.upper()} code that:
1. Follows the template syntax patterns exactly
2. Includes proper error handling
3. Validates array lengths before using in loops
4. Outputs "Optimal value: {{value}}" format
5. Handles infeasible/unbounded cases
6. CRITICAL: Correctly implements the mathematical formulation to achieve consistent results

Output Format:
```python
# Complete {solver_type.upper()} implementation - Retry Attempt {retry_attempt}
```

Generate working code based on the template patterns above, addressing any potential issues from latest round."""
    
    return prompt

def create_solver_results_analysis_prompt(original_mathematical_formulation, solver_results, solver_codes, db_id):
    """Create prompt for OR expert to analyze solver results and provide final recommendation"""
    
    essential_solver_results = {}
    for solver_name, result in solver_results.items():
        essential_solver_results[solver_name] = {
            "status": result.get("status", "unknown"),
            "optimal_value": result.get("optimal_value"),
            "execution_time": result.get("execution_time", 0),
            "error_message": result.get("error_message", ""),
            "retry_attempt": result.get("retry_attempt", "N/A")
        }
    
    template = load_prompt_template("solver_analysis")
    if template:
        prompt = template.format(
            original_mathematical_formulation=original_mathematical_formulation,
            essential_solver_results=json.dumps(essential_solver_results, indent=2),
            db_id=db_id
        )
    else:
        prompt = f"""You are an operations research expert analyzing the results from three different optimization solvers. Based on the original mathematical formulation and solver execution results, provide a comprehensive analysis and final recommendation.

ORIGINAL MATHEMATICAL FORMULATION:
{original_mathematical_formulation}

SOLVER EXECUTION RESULTS:
{json.dumps(essential_solver_results, indent=2)}

TASK: Analyze the three solver results and provide structured recommendations.

RESPONSE FORMAT (JSON only):

{{
  "database_id": "{db_id}",
  "solver_analysis": {{
    "gurobipy_analysis": {{
      "status": "optimal/infeasible/error/timeout",
      "optimal_value": null,
      "decision_variables": {{}},
      "reliability": "high/medium/low",
      "notes": "Analysis of Gurobipy results"
    }},
    "docplex_analysis": {{
      "status": "optimal/infeasible/error/timeout", 
      "optimal_value": null,
      "decision_variables": {{}},
      "reliability": "high/medium/low",
      "notes": "Analysis of DOCplex results"
    }},
    "pyomo_analysis": {{
      "status": "optimal/infeasible/error/timeout",
      "optimal_value": null,
      "decision_variables": {{}},
      "reliability": "high/medium/low", 
      "notes": "Analysis of Pyomo results"
    }}
  }},
  "consistency_evaluation": {{
    "values_consistent": false,
    "tolerance": 1e-6,
    "major_vote_optimal_value": null,
    "consistent_solvers": [],
    "inconsistent_solvers": [],
    "potential_issues": ["List potential reasons for inconsistencies"]
  }},
  "final_recommendation": {{
    "recommended_optimal_value": null,
    "recommended_decision_variables": {{}},
    "confidence": "high/medium/low",
    "business_interpretation": "What these results mean in business terms",
    "solver_preference": "gurobipy/docplex/pyomo/multiple",
    "reasoning": "Why this recommendation is made"
  }},
  "business_insights": {{
    "decision_variable_meanings": {{
      "x_0": "Business meaning of x[0] and its optimal value",
      "x_1": "Business meaning of x[1] and its optimal value",
      "x_2": "Business meaning of x[2] and its optimal value"
    }},
    "objective_value_interpretation": "What the optimal objective value means in business context",
    "resource_allocation_summary": "Summary of how resources should be allocated",
    "implementation_recommendations": "Practical steps for implementing this solution"
  }}
}}"""
    
    return prompt