# Complete Optimization Problem and Solution: real_estate_properties

## 1. Problem Context and Goals

### Context  
A real estate agency is focused on maximizing its total revenue from selling properties by determining the optimal selling price for each property. The agency must consider three key factors: the vendor's requested price, the buyer's offered price, and the property's features. The pricing model is designed to adjust the selling price linearly based on the property's features, ensuring that the final price reflects the property's value while remaining within the acceptable range for both the vendor and the buyer.

The agency uses a weighting factor to incorporate the influence of property features into the pricing model. This weighting factor, set at 0.3, ensures that features such as a pool, garage, or garden moderately influence the final selling price without overwhelming the base price. The agreed selling price for each property is calculated by adjusting the vendor's requested price based on the combined scores of the property's features, multiplied by the weighting factor.

The decision variable in this optimization problem is the agreed selling price for each property, which must be determined within the bounds set by the vendor's requested price and the buyer's offered price. The goal is to maximize the total revenue from all properties sold, ensuring that the pricing strategy is both fair and profitable.

### Goals  
The primary goal of this optimization problem is to maximize the total revenue generated from selling properties. This is achieved by determining the optimal agreed selling price for each property, ensuring that it falls within the acceptable range for both the vendor and the buyer. The success of this optimization is measured by the total sum of the agreed selling prices across all properties, which directly reflects the agency's revenue.

## 2. Constraints    

The optimization problem is subject to the following constraints:

1. **Vendor's Minimum Price Constraint**: The agreed selling price for each property must be at least as high as the vendor's requested price. This ensures that the vendor's minimum acceptable price is respected in the final agreement.

2. **Buyer's Maximum Price Constraint**: The agreed selling price for each property must not exceed the buyer's offered price. This ensures that the buyer's maximum willingness to pay is not surpassed.

3. **Feature-Adjusted Price Constraint**: The agreed selling price for each property is calculated by adjusting the vendor's requested price based on the property's features. This adjustment is made by multiplying the vendor's requested price by a factor that includes the weighting factor and the sum of the feature scores for the property. This ensures that the final price reflects the value added by the property's features.

These constraints ensure that the agreed selling price is both fair and realistic, balancing the interests of the vendor, the buyer, and the agency.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include adding a PropertyFeatures table to capture feature influence on pricing, and updating business_configuration_logic.json to include weighting factors for property features. The schema now supports the optimization model by incorporating property features into the pricing strategy.

CREATE TABLE Properties (
  property_id INTEGER,
  agreed_selling_price FLOAT,
  vendor_requested_price FLOAT,
  buyer_offered_price FLOAT
);

CREATE TABLE PropertyFeatures (
  property_id INTEGER,
  feature_name STRING,
  feature_score FLOAT
);
```

### Data Dictionary  
- **Properties Table**:  
  - **property_id**: A unique identifier for each property, used to link properties to their features and pricing details.  
  - **agreed_selling_price**: The final selling price agreed upon by the vendor and the buyer, which is the decision variable in the optimization model.  
  - **vendor_requested_price**: The minimum price the vendor is willing to accept, serving as the lower bound constraint in the optimization model.  
  - **buyer_offered_price**: The maximum price the buyer is willing to pay, serving as the upper bound constraint in the optimization model.  

- **PropertyFeatures Table**:  
  - **property_id**: A foreign key linking each feature to the corresponding property in the Properties table.  
  - **feature_name**: The name of the property feature, such as a pool, garage, or garden, which influences the selling price.  
  - **feature_score**: A score representing the influence of the feature on the selling price, used in the feature-adjusted price calculation.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic real estate market data, ensuring that vendor requested prices are lower than buyer offered prices, and feature scores reflect typical property feature influences.

-- Realistic data for Properties
INSERT INTO Properties (property_id, agreed_selling_price, vendor_requested_price, buyer_offered_price) VALUES (1, 320000.0, 300000.0, 340000.0);
INSERT INTO Properties (property_id, agreed_selling_price, vendor_requested_price, buyer_offered_price) VALUES (2, 480000.0, 450000.0, 500000.0);
INSERT INTO Properties (property_id, agreed_selling_price, vendor_requested_price, buyer_offered_price) VALUES (3, 260000.0, 240000.0, 280000.0);

-- Realistic data for PropertyFeatures
INSERT INTO PropertyFeatures (property_id, feature_name, feature_score) VALUES (1, 'Pool', 0.15);
INSERT INTO PropertyFeatures (property_id, feature_name, feature_score) VALUES (1, 'Garage', 0.1);
INSERT INTO PropertyFeatures (property_id, feature_name, feature_score) VALUES (2, 'Garden', 0.2);
INSERT INTO PropertyFeatures (property_id, feature_name, feature_score) VALUES (3, 'Garage', 0.1);
```

## 4. Mathematical Optimization Formulation

#### Decision Variables
- Let \( x_i \) represent the **agreed_selling_price** for property \( i \), where \( i \in \{1, 2, 3\} \).  
  - \( x_i \) is a continuous variable.

#### Objective Function
Maximize the total revenue from selling all properties:  
\[
\text{Maximize } \sum_{i=1}^{3} x_i
\]

#### Constraints
1. **Vendor's Minimum Price Constraint**:  
   The agreed selling price must be at least as high as the vendor's requested price:  
   \[
   x_i \geq \text{vendor_requested_price}_i \quad \forall i \in \{1, 2, 3\}
   \]

2. **Buyer's Maximum Price Constraint**:  
   The agreed selling price must not exceed the buyer's offered price:  
   \[
   x_i \leq \text{buyer_offered_price}_i \quad \forall i \in \{1, 2, 3\}
   \]

3. **Feature-Adjusted Price Constraint**:  
   The agreed selling price is adjusted based on the property's features and the weighting factor (0.3):  
   \[
   x_i \geq \text{vendor_requested_price}_i \times \left(1 + 0.3 \times \sum_{j} \text{feature_score}_{ij}\right) \quad \forall i \in \{1, 2, 3\}
   \]
   where \( \text{feature_score}_{ij} \) is the score of feature \( j \) for property \( i \).

#### Data Source Verification
- **Decision Variables**:  
  \( x_i \) maps to **Properties.agreed_selling_price**.

- **Objective Function Coefficients**:  
  The coefficient for \( x_i \) is 1, derived from the goal of maximizing total revenue.

- **Vendor's Minimum Price Constraint**:  
  \( \text{vendor_requested_price}_i \) maps to **Properties.vendor_requested_price**.

- **Buyer's Maximum Price Constraint**:  
  \( \text{buyer_offered_price}_i \) maps to **Properties.buyer_offered_price**.

- **Feature-Adjusted Price Constraint**:  
  - \( \text{vendor_requested_price}_i \) maps to **Properties.vendor_requested_price**.  
  - \( \text{feature_score}_{ij} \) maps to **PropertyFeatures.feature_score**.  
  - The weighting factor 0.3 is from the business configuration parameter **feature_weighting_factor**.

#### Numerical Example
Using the provided data:

1. **Vendor's Minimum Price Constraint**:  
   \[
   x_1 \geq 300000, \quad x_2 \geq 450000, \quad x_3 \geq 240000
   \]

2. **Buyer's Maximum Price Constraint**:  
   \[
   x_1 \leq 340000, \quad x_2 \leq 500000, \quad x_3 \leq 280000
   \]

3. **Feature-Adjusted Price Constraint**:  
   - For property 1:  
     \[
     x_1 \geq 300000 \times \left(1 + 0.3 \times (0.15 + 0.1)\right) = 300000 \times 1.075 = 322500
     \]
   - For property 2:  
     \[
     x_2 \geq 450000 \times \left(1 + 0.3 \times 0.2\right) = 450000 \times 1.06 = 477000
     \]
   - For property 3:  
     \[
     x_3 \geq 240000 \times \left(1 + 0.3 \times 0.1\right) = 240000 \times 1.03 = 247200
     \]

#### Final Linear Programming Formulation
\[
\text{Maximize } x_1 + x_2 + x_3
\]
Subject to:
\[
x_1 \geq 322500, \quad x_1 \leq 340000
\]
\[
x_2 \geq 477000, \quad x_2 \leq 500000
\]
\[
x_3 \geq 247200, \quad x_3 \leq 280000
\]

This is a complete, immediately solvable linear programming model.

## 5. Gurobipy Implementation

```python
#!/usr/bin/env python3
"""
Gurobipy 12.0.2 Implementation for Real Estate Properties Optimization
"""

import gurobipy as gp
from gurobipy import GRB

def optimize_real_estate_properties():
    """Optimize the agreed selling price for real estate properties to maximize total revenue."""
    
    # 1. MODEL & DATA SETUP
    model = gp.Model("real_estate_properties")
    
    # Data from the problem
    properties = {
        1: {"vendor_requested_price": 300000.0, "buyer_offered_price": 340000.0},
        2: {"vendor_requested_price": 450000.0, "buyer_offered_price": 500000.0},
        3: {"vendor_requested_price": 240000.0, "buyer_offered_price": 280000.0}
    }
    
    property_features = {
        1: [("Pool", 0.15), ("Garage", 0.1)],
        2: [("Garden", 0.2)],
        3: [("Garage", 0.1)]
    }
    
    weighting_factor = 0.3
    
    # CRITICAL: Validate array lengths before loops
    assert len(properties) == len(property_features), "Properties and features length mismatch"
    
    # 2. VARIABLES
    x = {i: model.addVar(vtype=GRB.CONTINUOUS, name=f"x_{i}", lb=0) for i in properties}
    
    # 3. OBJECTIVE FUNCTION
    model.setObjective(gp.quicksum(x[i] for i in properties), GRB.MAXIMIZE)
    
    # 4. CONSTRAINTS
    for i in properties:
        vendor_price = properties[i]["vendor_requested_price"]
        buyer_price = properties[i]["buyer_offered_price"]
        
        # Vendor's Minimum Price Constraint
        model.addConstr(x[i] >= vendor_price, name=f"vendor_min_{i}")
        
        # Buyer's Maximum Price Constraint
        model.addConstr(x[i] <= buyer_price, name=f"buyer_max_{i}")
        
        # Feature-Adjusted Price Constraint
        feature_sum = sum(feature[1] for feature in property_features[i])
        adjusted_price = vendor_price * (1 + weighting_factor * feature_sum)
        model.addConstr(x[i] >= adjusted_price, name=f"feature_adjusted_{i}")
    
    # 5. SOLVING & RESULTS
    model.optimize()
    
    if model.status == GRB.OPTIMAL:
        print(f"Optimal value: {model.objVal}")
        for i in properties:
            print(f"x[{i}] = {x[i].x:.3f}")
    elif model.status == GRB.INFEASIBLE:
        print("Problem is infeasible")
    elif model.status == GRB.UNBOUNDED:
        print("Problem is unbounded")
    
    return model

# Run the optimization
optimize_real_estate_properties()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 1120000.0
**Execution Time**: 0.17 seconds
**Reliability**: high
**Analysis**: Gurobipy found the optimal solution quickly with the lowest execution time.

## 6. DOCplex Implementation

```python
#!/usr/bin/env python3
"""
DOCplex implementation for Real Estate Properties Optimization Problem
"""

from docplex.mp.model import Model

def real_estate_optimization():
    """Optimize the agreed selling price for real estate properties to maximize total revenue."""
    
    # 1. MODEL & DATA SETUP
    mdl = Model(name="real_estate_properties")
    
    # Data from the problem
    property_ids = [1, 2, 3]
    vendor_prices = {1: 300000.0, 2: 450000.0, 3: 240000.0}
    buyer_prices = {1: 340000.0, 2: 500000.0, 3: 280000.0}
    feature_scores = {
        1: [0.15, 0.1],  # Pool and Garage for property 1
        2: [0.2],        # Garden for property 2
        3: [0.1]         # Garage for property 3
    }
    weighting_factor = 0.3
    
    # CRITICAL: Validate array lengths to prevent IndexError
    assert len(property_ids) == len(vendor_prices) == len(buyer_prices) == len(feature_scores), "Array length mismatch"
    
    # 2. VARIABLES
    # Decision variable: agreed_selling_price for each property
    x = {i: mdl.continuous_var(name=f"x_{i}", lb=0) for i in property_ids}
    
    # 3. OBJECTIVE FUNCTION
    # Maximize total revenue
    objective = mdl.sum(x[i] for i in property_ids)
    mdl.maximize(objective)
    
    # 4. CONSTRAINTS
    # Vendor's Minimum Price Constraint
    for i in property_ids:
        mdl.add_constraint(x[i] >= vendor_prices[i], ctname=f"vendor_min_{i}")
    
    # Buyer's Maximum Price Constraint
    for i in property_ids:
        mdl.add_constraint(x[i] <= buyer_prices[i], ctname=f"buyer_max_{i}")
    
    # Feature-Adjusted Price Constraint
    for i in property_ids:
        sum_feature_scores = sum(feature_scores[i])
        adjusted_price = vendor_prices[i] * (1 + weighting_factor * sum_feature_scores)
        mdl.add_constraint(x[i] >= adjusted_price, ctname=f"feature_adjusted_{i}")
    
    # 5. SOLVING & RESULTS
    solution = mdl.solve()
    
    if solution:
        print(f"Optimal value: {solution.objective_value}")
        for i in property_ids:
            value = solution.get_value(x[i])
            print(f"Agreed selling price for property {i}: {value:.2f}")
    else:
        print("No solution found")
        print(f"Status: {mdl.solve_details.status}")
    
    return mdl

# Execute the optimization
if __name__ == "__main__":
    real_estate_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 1120000.0
**Execution Time**: 1.08 seconds
**Reliability**: high
**Analysis**: DOCplex found the optimal solution but took longer compared to Gurobipy.

## 7. Pyomo Implementation

```python
# Complete PYOMO implementation

import pyomo.environ as pyo
from pyomo.opt import SolverFactory

def real_estate_optimization():
    """Optimization model for maximizing real estate revenue."""
    
    # 1. MODEL CREATION
    model = pyo.ConcreteModel()
    
    # 2. DATA SETUP
    properties = {
        1: {'vendor_price': 300000, 'buyer_price': 340000, 'features': [0.15, 0.1]},
        2: {'vendor_price': 450000, 'buyer_price': 500000, 'features': [0.2]},
        3: {'vendor_price': 240000, 'buyer_price': 280000, 'features': [0.1]}
    }
    
    # CRITICAL: Validate data consistency
    for prop_id, data in properties.items():
        assert data['vendor_price'] <= data['buyer_price'], f"Property {prop_id} has invalid price range"
        assert all(0 <= score <= 1 for score in data['features']), f"Property {prop_id} has invalid feature scores"
    
    # 3. SETS
    model.I = pyo.Set(initialize=properties.keys())  # Property IDs
    
    # 4. PARAMETERS
    model.vendor_price = pyo.Param(model.I, initialize={i: properties[i]['vendor_price'] for i in model.I})
    model.buyer_price = pyo.Param(model.I, initialize={i: properties[i]['buyer_price'] for i in model.I})
    model.feature_scores = pyo.Param(model.I, initialize={i: sum(properties[i]['features']) for i in model.I})
    
    # Weighting factor
    feature_weighting_factor = 0.3
    
    # 5. VARIABLES
    model.x = pyo.Var(model.I, within=pyo.NonNegativeReals)  # Agreed selling price
    
    # 6. OBJECTIVE FUNCTION
    def obj_rule(model):
        return sum(model.x[i] for i in model.I)
    model.objective = pyo.Objective(rule=obj_rule, sense=pyo.maximize)
    
    # 7. CONSTRAINTS
    
    # Vendor's Minimum Price Constraint
    def vendor_min_rule(model, i):
        return model.x[i] >= model.vendor_price[i]
    model.vendor_min_constraint = pyo.Constraint(model.I, rule=vendor_min_rule)
    
    # Buyer's Maximum Price Constraint
    def buyer_max_rule(model, i):
        return model.x[i] <= model.buyer_price[i]
    model.buyer_max_constraint = pyo.Constraint(model.I, rule=buyer_max_rule)
    
    # Feature-Adjusted Price Constraint
    def feature_adjusted_rule(model, i):
        return model.x[i] >= model.vendor_price[i] * (1 + feature_weighting_factor * model.feature_scores[i])
    model.feature_adjusted_constraint = pyo.Constraint(model.I, rule=feature_adjusted_rule)
    
    # 8. SOLVING WITH GUROBI
    solver = SolverFactory('gurobi')
    
    # Optional: Set solver options
    solver.options['TimeLimit'] = 300  # 5 minutes
    solver.options['MIPGap'] = 0.01    # 1% gap
    
    # Solve the model
    results = solver.solve(model, tee=True)  # tee=True shows solver output
    
    # 9. RESULT PROCESSING
    if results.solver.termination_condition == pyo.TerminationCondition.optimal:
        print(f"Optimal value: {pyo.value(model.objective)}")
        
        # Extract variable values
        print("\nAgreed selling prices:")
        for i in model.I:
            x_val = pyo.value(model.x[i])
            print(f"Property {i}: {x_val:.2f}")
        
    elif results.solver.termination_condition == pyo.TerminationCondition.infeasible:
        print("Problem is infeasible")
    elif results.solver.termination_condition == pyo.TerminationCondition.unbounded:
        print("Problem is unbounded")
    else:
        print(f"Solver terminated with condition: {results.solver.termination_condition}")
    
    return model

# Run the optimization
if __name__ == "__main__":
    real_estate_optimization()
```

### Execution Results
**Status**: OPTIMAL
**Optimal Value**: 1120000.0
**Execution Time**: 0.99 seconds
**Reliability**: high
**Analysis**: Pyomo found the optimal solution but had the longest execution time.

## 8. Cross-Solver Analysis and Final Recommendation

### Solver Results Comparison

| Solver | Status | Optimal Value | Execution Time | Decision Variables | Retry Attempt |
|--------|--------|---------------|----------------|-------------------|---------------|
| Gurobipy | OPTIMAL | 1120000.00 | 0.17s | N/A | N/A |
| Docplex | OPTIMAL | 1120000.00 | 1.08s | N/A | N/A |
| Pyomo | OPTIMAL | 1120000.00 | 0.99s | N/A | N/A |

### Solver Consistency Analysis
**Result**: All solvers produced consistent results ✓
**Consistent Solvers**: gurobipy, docplex, pyomo
**Majority Vote Optimal Value**: 1120000.0

### Final Recommendation
**Recommended Optimal Value**: 1120000.0
**Confidence Level**: HIGH
**Preferred Solver(s)**: gurobipy
**Reasoning**: Gurobipy is recommended due to its faster execution time while maintaining high reliability and consistency with other solvers.

### Optimal Decision Variables
- **x_1** = 340000.000
  - *Business Meaning*: The agreed selling price for property 1 is $340,000, which is the maximum the buyer is willing to pay.
- **x_2** = 500000.000
  - *Business Meaning*: The agreed selling price for property 2 is $500,000, which is the maximum the buyer is willing to pay.
- **x_3** = 280000.000
  - *Business Meaning*: The agreed selling price for property 3 is $280,000, which is the maximum the buyer is willing to pay.

### Business Interpretation
**Overall Strategy**: The optimal solution maximizes total revenue by setting the agreed selling prices at the upper bounds of the buyer's offered prices, ensuring all constraints are satisfied.
**Objective Value Meaning**: The total revenue from selling all three properties is maximized at $1,120,000.
**Resource Allocation Summary**: All properties should be sold at the maximum prices offered by the buyers, ensuring vendor and feature-adjusted constraints are met.
**Implementation Recommendations**: Proceed with the agreed selling prices of $340,000, $500,000, and $280,000 for properties 1, 2, and 3, respectively, to maximize revenue while satisfying all constraints.