/*
Case Scenario: December Holiday Bonuses  

You are managing an employee database for a company that provides bonuses to employees who have worked a certain number of hours during the month of December.

Schema:
| Column Name   | Data Type    | Description                                |  
|---------------|--------------|--------------------------------------------|  
| employee_id   | INT          | Unique ID for each employee                |  
| employee_name | VARCHAR(50)  | Name of the employee                       |  
| base_salary   | DECIMAL(10,2)| Monthly base salary of the employee        |  
| hours_worked  | INT          | Total hours worked in December             |  

The bonus calculation depends on the number of hours worked:  

1. If the employee has worked more than 160 hours, they receive a 20% bonus of their base salary.  
2. If the employee has worked between 120 and 160 hours, they receive a 10% bonus of their base salary.  
3. If the employee has worked less than 120 hours, they receive no bonus.  

Task
Write a query to calculate and display:  
1. `employee_id`: The unique ID of the employee.  
2. `hours_worked`: The total hours worked by the employee in December.  
3. `base_salary`: The base salary of the employee.  
4. `bonus`: The bonus amount calculated using the above rules.  
5. `total_salary`: The sum of the base salary and the bonus.  

Expected Output:  
| employee_id | employee_name  | hours_worked | base_salary | bonus    | total_salary |  
|-------------|----------------|--------------|-------------|----------|--------------|  
| 4           | David Brown    | 180          | 3500.00     | 700.00   | 4200.00      |  
| 1           | Alice Johnson  | 170          | 3000.00     | 600.00   | 3600.00      |  
| 6           | Frank White    | 160          | 3200.00     | 320.00   | 3520.00      |  
| 2           | Bob Smith      | 145          | 2500.00     | 250.00   | 2750.00      |  
| 3           | Carol Lee      | 110          | 4000.00     | 0.00     | 4000.00      |  
| 5           | Emma Davis     | 95           | 2800.00     | 0.00     | 2800.00      |  
*/

CREATE TABLE employees (  
    employee_id INT,  
    employee_name VARCHAR(50),  
    base_salary DECIMAL(10,2),  
    hours_worked INT  
);  

INSERT INTO employees (employee_id, employee_name, base_salary, hours_worked) VALUES  
(1, 'Alice Johnson', 3000.00, 170),  
(2, 'Bob Smith', 2500.00, 145),  
(3, 'Carol Lee', 4000.00, 110),  
(4, 'David Brown', 3500.00, 180),  
(5, 'Emma Davis', 2800.00, 95),  
(6, 'Frank White', 3200.00, 160);  
  
SELECT  
    employee_id,  
    employee_name,  
    hours_worked,  
    base_salary,  
    IF(hours_worked > 160, base_salary * 0.2,   
       IF(hours_worked BETWEEN 120 AND 160, base_salary * 0.1, 0)) AS bonus,  
    base_salary +  
    IF(hours_worked > 160, base_salary * 0.2,   
       IF(hours_worked BETWEEN 120 AND 160, base_salary * 0.1, 0)) AS total_salary  
FROM employees  
ORDER BY total_salary DESC;