=== Problem Context ===
# Complete Optimization Problem and Solution: game_1

## 1. Problem Context and Goals

### Context  
A university is tasked with optimizing the allocation of scholarships to students based on three key factors: academic performance, sports participation, and gaming habits. The goal is to maximize overall student satisfaction and performance while adhering to budget constraints and ensuring fairness in scholarship distribution. The university has a total budget of $150,000 available for scholarships, and a factor of 1000 is applied to ensure that students with higher GPAs receive proportionally more scholarships.  

The decision variables in this problem are the scholarship amounts allocated to each student, which are continuous values. The operational parameters include the weights assigned to academic performance (GPA), sports participation (hours per week), and gaming habits (hours played). These weights are used to calculate a weighted sum that represents the overall satisfaction and performance of students.  

The problem is designed to avoid nonlinear relationships, ensuring that all calculations and constraints are linear. For example, the scholarship amount for each student is directly proportional to their GPA, and the total scholarship allocation must not exceed the available budget. Additionally, students must meet minimum and maximum hour requirements for both sports participation and gaming to be eligible for scholarships.  

### Goals  
The primary goal of this optimization problem is to maximize the total weighted sum of student satisfaction, which is influenced by academic performance, sports participation, and gaming habits. Success is measured by how well the scholarship allocations align with the weighted contributions of these factors.  

The optimization aims to ensure that students with higher GPAs receive more scholarships, while also considering their involvement in sports and gaming. The weights assigned to each factor (GPA, sports participation, and gaming habits) are critical in determining the final allocation. The university seeks to achieve a fair and balanced distribution of scholarships that encourages both academic excellence and extracurricular engagement.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Total Budget Constraint**: The sum of all scholarship amounts allocated to students must not exceed the total budget of $150,000.  
2. **Scholarship Fairness Constraint**: Each student's scholarship amount must be at least proportional to their GPA, using a factor of 1000 to ensure higher GPA students receive more scholarships.  
3. **Sports Participation Constraints**: Each student must meet the minimum and maximum hour requirements for sports participation. These requirements ensure that students are actively engaged in sports without overcommitting.  
4. **Gaming Habits Constraints**: Each student must meet the minimum and maximum hour requirements for gaming. These requirements ensure that students maintain a balanced lifestyle while still enjoying gaming activities.  

These constraints are designed to ensure that the scholarship allocations are fair, feasible, and aligned with the university's priorities.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 3 Database Schema
-- Objective: Schema changes include adding missing optimization requirements to business configuration logic and ensuring all mappings are complete.

CREATE TABLE Academic_Performance (
  Student_ID INTEGER,
  GPA FLOAT
);

CREATE TABLE Objective_Weights (
  w1 FLOAT,
  w2 FLOAT,
  w3 FLOAT
);

CREATE TABLE SportsInfo (
  Student_ID INTEGER,
  HoursPerWeek FLOAT,
  Min_Hours_Sports FLOAT,
  Max_Hours_Sports FLOAT
);

CREATE TABLE Plays_Games (
  Student_ID INTEGER,
  Hours_Played FLOAT,
  Min_Hours_Gaming FLOAT,
  Max_Hours_Gaming FLOAT
);

CREATE TABLE Scholarship_Allocation (
  Student_ID INTEGER,
  Scholarship_Amount FLOAT
);
```

### Data Dictionary  
- **Academic_Performance**:  
  - **Student_ID**: Unique identifier for each student.  
  - **GPA**: Grade Point Average of the student, used to determine academic performance and scholarship allocation.  

- **Objective_Weights**:  
  - **w1**: Weight assigned to academic performance in the objective function.  
  - **w2**: Weight assigned to sports participation in the objective function.  
  - **w3**: Weight assigned to gaming habits in the objective function.  

- **SportsInfo**:  
  - **Student_ID**: Unique identifier for each student.  
  - **HoursPerWeek**: Hours spent on sports per week, used in the objective function and constraints.  
  - **Min_Hours_Sports**: Minimum hours required for sports participation, used as a constraint bound.  
  - **Max_Hours_Sports**: Maximum hours allowed for sports participation, used as a constraint bound.  

- **Plays_Games**:  
  - **Student_ID**: Unique identifier for each student.  
  - **Hours_Played**: Hours spent on gaming per week, used in the objective function and constraints.  
  - **Min_Hours_Gaming**: Minimum hours required for gaming, used as a constraint bound.  
  - **Max_Hours_Gaming**: Maximum hours allowed for gaming, used as a constraint bound.  

- **Scholarship_Allocation**:  
  - **Student_ID**: Unique identifier for each student.  
  - **Scholarship_Amount**: Scholarship amount allocated to the student, which is the decision variable in the optimization model.  


=== Schema ===
-- Iteration 3 Database Schema
-- Objective: Schema changes include adding missing optimization requirements to business configuration logic and ensuring all mappings are complete.

CREATE TABLE Academic_Performance (
  Student_ID INTEGER,
  GPA FLOAT
);

CREATE TABLE Objective_Weights (
  w1 FLOAT,
  w2 FLOAT,
  w3 FLOAT
);

CREATE TABLE SportsInfo (
  Student_ID INTEGER,
  HoursPerWeek FLOAT,
  Min_Hours_Sports FLOAT,
  Max_Hours_Sports FLOAT
);

CREATE TABLE Plays_Games (
  Student_ID INTEGER,
  Hours_Played FLOAT,
  Min_Hours_Gaming FLOAT,
  Max_Hours_Gaming FLOAT
);

CREATE TABLE Scholarship_Allocation (
  Student_ID INTEGER,
  Scholarship_Amount FLOAT
);



=== LLM Response ===
```sql
-- Query Description: Retrieve the weights for the objective function (w1, w2, w3) which are critical for calculating the weighted sum of student satisfaction.
-- These weights are used to balance the importance of academic performance, sports participation, and gaming habits in the optimization model.
SELECT w1, w2, w3
FROM Objective_Weights;

-- Query Description: Retrieve the GPA for each student, which is a key factor in determining scholarship allocation.
-- The GPA is used to ensure that students with higher academic performance receive proportionally more scholarships.
SELECT Student_ID, GPA
FROM Academic_Performance;

-- Query Description: Retrieve sports participation data for each student, including hours per week and the minimum and maximum hour requirements.
-- This data is used to ensure that students meet the sports participation constraints and to calculate the weighted sum in the objective function.
SELECT Student_ID, HoursPerWeek, Min_Hours_Sports, Max_Hours_Sports
FROM SportsInfo;

-- Query Description: Retrieve gaming habits data for each student, including hours played and the minimum and maximum hour requirements.
-- This data is used to ensure that students meet the gaming habits constraints and to calculate the weighted sum in the objective function.
SELECT Student_ID, Hours_Played, Min_Hours_Gaming, Max_Hours_Gaming
FROM Plays_Games;

-- Query Description: Retrieve the current scholarship allocation for each student.
-- This data is used to ensure that the total scholarship allocation does not exceed the budget and to verify fairness in the distribution.
SELECT Student_ID, Scholarship_Amount
FROM Scholarship_Allocation;

-- Query Description: Retrieve a combined view of academic performance, sports participation, and gaming habits for each student.
-- This data is essential for calculating the weighted sum of student satisfaction and ensuring that all constraints are met.
SELECT 
    ap.Student_ID, 
    ap.GPA, 
    si.HoursPerWeek, 
    si.Min_Hours_Sports, 
    si.Max_Hours_Sports, 
    pg.Hours_Played, 
    pg.Min_Hours_Gaming, 
    pg.Max_Hours_Gaming
FROM Academic_Performance ap
JOIN SportsInfo si ON ap.Student_ID = si.Student_ID
JOIN Plays_Games pg ON ap.Student_ID = pg.Student_ID;

-- Query Description: Calculate the total current scholarship allocation across all students.
-- This is used to ensure that the total allocation does not exceed the budget constraint of $150,000.
SELECT SUM(Scholarship_Amount) AS Total_Scholarship_Allocation
FROM Scholarship_Allocation;

-- Query Description: Retrieve students who do not meet the minimum or maximum hour requirements for sports participation.
-- This data is used to identify students who are ineligible for scholarships due to not meeting the sports participation constraints.
SELECT Student_ID
FROM SportsInfo
WHERE HoursPerWeek < Min_Hours_Sports OR HoursPerWeek > Max_Hours_Sports;

-- Query Description: Retrieve students who do not meet the minimum or maximum hour requirements for gaming.
-- This data is used to identify students who are ineligible for scholarships due to not meeting the gaming habits constraints.
SELECT Student_ID
FROM Plays_Games
WHERE Hours_Played < Min_Hours_Gaming OR Hours_Played > Max_Hours_Gaming;

-- Query Description: Retrieve a summary of the average GPA, sports hours, and gaming hours across all students.
-- This summary data can provide insights into the overall student population and help in setting realistic constraints and weights.
SELECT 
    AVG(GPA) AS Avg_GPA, 
    AVG(HoursPerWeek) AS Avg_Sports_Hours, 
    AVG(Hours_Played) AS Avg_Gaming_Hours
FROM Academic_Performance ap
JOIN SportsInfo si ON ap.Student_ID = si.Student_ID
JOIN Plays_Games pg ON ap.Student_ID = pg.Student_ID;
```

These queries are designed to retrieve the most relevant data for solving the optimization problem, including the weights for the objective function, individual student data for academic performance, sports participation, and gaming habits, as well as current scholarship allocations and summary statistics. The combined view and summary queries provide a comprehensive understanding of the student population, which is essential for making informed decisions in the optimization process.
