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

## 1. Problem Context and Goals

### Context  
A high school is focused on enhancing student well-being by strategically pairing students as friends based on mutual likes. The goal is to maximize overall student happiness while ensuring a balanced social environment. Each student must have a minimum number of friends to prevent isolation and a maximum number to avoid overwhelming social commitments. The decision to pair two students as friends is represented as a binary choice, where a value of 1 indicates a friendship and 0 indicates no friendship. The happiness score between two students, derived from mutual likes, serves as the basis for measuring the success of these pairings. The school has established operational parameters to guide this process: each student must have at least 2 friends and no more than 5 friends. These parameters ensure a supportive yet manageable social network for all students.

### Goals  
The primary goal of this optimization effort is to maximize the total happiness score across all student pairings. This is achieved by strategically selecting friendships that contribute the most to overall happiness, as measured by the happiness scores between students. Success is determined by the sum of happiness scores from all paired friendships, ensuring that the chosen pairings align with the school’s objective of fostering a positive and balanced social environment.

## 2. Constraints  

1. **Minimum Friends Constraint**: Every student must have at least 2 friends. This ensures that no student is left isolated and that everyone has a supportive social network.  
2. **Maximum Friends Constraint**: No student can have more than 5 friends. This prevents any student from being overwhelmed by too many social commitments, promoting a balanced social environment.  
3. **Mutual Friendship Constraint**: If a student is paired with a friend, the friend must also be paired with the student. This ensures that all friendships are mutual and reflect genuine social connections.  

These constraints are designed to maintain fairness and balance in the student pairing process while aligning with the school’s operational parameters.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a HappinessScore table to map likes between students, and moving max_friends and min_friends to business_configuration_logic.json. The Friend table is modified to ensure mutual friendships.

CREATE TABLE HappinessScore (
  student_id INTEGER,
  friend_id INTEGER,
  score INTEGER
);

CREATE TABLE Friend (
  student_id INTEGER,
  friend_id INTEGER
);
```

### Data Dictionary  
- **HappinessScore Table**:  
  - **Purpose**: Maps happiness scores between student pairs based on mutual likes.  
  - **Columns**:  
    - `student_id`: Identifies the student in the happiness score pair.  
    - `friend_id`: Identifies the friend in the happiness score pair.  
    - `score`: Represents the happiness score (1 if the student likes the friend, otherwise 0). This score is used as the coefficient in the optimization objective.  

- **Friend Table**:  
  - **Purpose**: Represents mutual friendships between students.  
  - **Columns**:  
    - `student_id`: Identifies the student in the friendship pair.  
    - `friend_id`: Identifies the friend in the friendship pair.  


=== Schema ===
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a HappinessScore table to map likes between students, and moving max_friends and min_friends to business_configuration_logic.json. The Friend table is modified to ensure mutual friendships.

CREATE TABLE HappinessScore (
  student_id INTEGER,
  friend_id INTEGER,
  score INTEGER
);

CREATE TABLE Friend (
  student_id INTEGER,
  friend_id INTEGER
);



=== LLM Response ===
```sql
-- Query Description: Retrieve all happiness scores between student pairs. This is crucial for the objective function, as the optimization aims to maximize the total happiness score.
SELECT student_id, friend_id, score
FROM HappinessScore;

-- Query Description: Retrieve all existing friendships. This data is important for understanding the current state of the network and ensuring mutual friendships are maintained.
SELECT student_id, friend_id
FROM Friend;

-- Query Description: Retrieve the total number of friends each student currently has. This helps in enforcing the minimum and maximum friends constraints.
SELECT student_id, COUNT(friend_id) AS num_friends
FROM Friend
GROUP BY student_id;

-- Query Description: Retrieve the happiness scores for student pairs who are not yet friends. This data is useful for identifying potential new friendships that could increase the total happiness score.
SELECT hs.student_id, hs.friend_id, hs.score
FROM HappinessScore hs
LEFT JOIN Friend f ON hs.student_id = f.student_id AND hs.friend_id = f.friend_id
WHERE f.student_id IS NULL;

-- Query Description: Retrieve the happiness scores for student pairs who are already friends. This helps in evaluating the current happiness contributions of existing friendships.
SELECT hs.student_id, hs.friend_id, hs.score
FROM HappinessScore hs
JOIN Friend f ON hs.student_id = f.student_id AND hs.friend_id = f.friend_id;

-- Query Description: Retrieve the top N happiness scores for student pairs who are not yet friends. This can be used to prioritize the most beneficial potential friendships.
SELECT hs.student_id, hs.friend_id, hs.score
FROM HappinessScore hs
LEFT JOIN Friend f ON hs.student_id = f.student_id AND hs.friend_id = f.friend_id
WHERE f.student_id IS NULL
ORDER BY hs.score DESC
LIMIT N;  -- Replace N with the desired number of top scores

-- Query Description: Retrieve the happiness scores for student pairs where the friendship is mutual. This ensures that all friendships are bidirectional, aligning with the mutual friendship constraint.
SELECT hs.student_id, hs.friend_id, hs.score
FROM HappinessScore hs
JOIN Friend f1 ON hs.student_id = f1.student_id AND hs.friend_id = f1.friend_id
JOIN Friend f2 ON hs.student_id = f2.friend_id AND hs.friend_id = f2.student_id;

-- Query Description: Retrieve the happiness scores for student pairs where the friendship is not mutual. This helps in identifying and correcting any non-mutual friendships.
SELECT hs.student_id, hs.friend_id, hs.score
FROM HappinessScore hs
JOIN Friend f1 ON hs.student_id = f1.student_id AND hs.friend_id = f1.friend_id
LEFT JOIN Friend f2 ON hs.student_id = f2.friend_id AND hs.friend_id = f2.student_id
WHERE f2.student_id IS NULL;

-- Query Description: Retrieve the total happiness score for each student based on their current friendships. This provides a summary of each student's current happiness contribution.
SELECT f.student_id, SUM(hs.score) AS total_happiness
FROM Friend f
JOIN HappinessScore hs ON f.student_id = hs.student_id AND f.friend_id = hs.friend_id
GROUP BY f.student_id;

-- Query Description: Retrieve the total happiness score for the entire network. This is the key metric to be maximized in the optimization problem.
SELECT SUM(hs.score) AS total_network_happiness
FROM Friend f
JOIN HappinessScore hs ON f.student_id = hs.student_id AND f.friend_id = hs.friend_id;
```

These queries are designed to retrieve the necessary data for the optimization problem, including the happiness scores, existing friendships, and constraints related to the number of friends each student can have. The data retrieved will be used to inform the decision variables, objective function, and constraints in the optimization model.
