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