CREATE TABLE Customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    address TEXT,
    registration_date DATE
);

CREATE TABLE Movies (
    movie_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    genre VARCHAR(50),
    release_year INT,
    rental_price DECIMAL(5,2),
    stock INT CHECK (stock >=0)
);

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20) UNIQUE NOT NULL,
    position VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL,
    salary DECIMAL(10,2) NOT NULL
);

CREATE TABLE Rentals (
    rental_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    movie_id INT NOT NULL,
    employee_id INT,
    rental_date DATE NOT NULL,
    return_date DATE DEFAULT NULL,
    status ENUM('Rented', 'Returned') NOT NULL,
    CONSTRAINT fk_rentals_customer FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    CONSTRAINT fk_rentals_movie FOREIGN KEY (movie_id) REFERENCES Movies(movie_id),
    CONSTRAINT fk_rentals_employee FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);

CREATE TABLE Payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    rental_id INT NOT NULL,
    customer_id INT NOT NULL,
    employee_id INT,
    amount DECIMAL(10,2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method ENUM('Credit Card', 'Debit Card', 'Cash', 'Online') NOT NULL,
    CONSTRAINT fk_payments_rental FOREIGN KEY (rental_id) REFERENCES Rentals(rental_id),
    CONSTRAINT fk_payments_customer FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    CONSTRAINT fk_payments_employee FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);

INSERT Customers (customer_id,first_name,last_name,email,phone,address,registration_date) VALUES
    (1, 'John', 'Doe', 'john.doe@example.com', '123-456-7890', '123 Main St, NY', '2024-01-15'),
    (2, 'Jane', 'Smith', 'jane.smith@example.com', '987-654-3210', '456 Elm St, CA', '2024-02-10'),
    (3, 'Michael', 'Brown', 'michael.brown@example.com', '555-678-9012', '789 Oak St, TX', '2024-03-05'),
    (4, 'Emily', 'Davis', 'emily.davis@example.com', '222-333-4444', '321 Pine St, FL', '2024-04-12'),
    (5, 'David', 'Wilson', 'david.wilson@example.com', '777-888-9999', '654 Cedar St, WA', '2024-05-20'),
    (6, 'Sarah', 'Miller', 'sarah.miller@example.com', '444-555-6666', '987 Birch St, CO', '2024-06-25'),
    (7, 'Daniel', 'Martinez', 'daniel.martinez@example.com', '666-777-8888', '159 Spruce St, IL', '2024-07-18'),
    (8, 'Sophia', 'Garcia', 'sophia.garcia@example.com', '111-222-3333', '753 Maple St, NV', '2024-08-30'),
    (9, 'James', 'Hernandez', 'james.hernandez@example.com', '888-999-0000', '852 Redwood St, AZ', '2024-09-14'),
    (10, 'Olivia', 'Lopez', 'olivia.lopez@example.com', '999-000-1111', '369 Walnut St, OR', '2024-10-05');

INSERT Movies (movie_id,title, genre,release_year, rental_price,stock) VALUES
    (1,'Inception','Sci-Fi',2010,3.99,5),
    (2,'The Dark Knight','Action',2008,3.99,7),
    (3,'Titanic','Romance',1997,2.99,4),
    (4,'The Matrix','Sci-Fi',1999,3.49,6),
    (5,'Avengers: Endgame','Action',2019,4.99,10),
    (6,'Forrest Gump','Drama',1994,2.99,5),
    (7,'The Godfather','Crime',1972,3.99,3),
    (8,'Pulp Fiction','Crime',1994,3.49,4),
    (9,'The Lion King','Animation',1994,2.49,8),
    (10,'Frozen','Animation',2013,2.99,10),
    (11,'Interstellar','Sci-Fi',2014,4.49,6),
    (12,'Joker','Thriller',2019,3.99,5),
    (13,'Toy Story','Animation',1995,2.99,7),
    (14,'Finding Nemo','Animation',2003,2.99,9),
    (15,'The Avengers','Action',2012,3.99,8),
    (16,'The Shawshank Redemption','Drama',1994,3.49,6),
    (17,'Black Panther','Action',2018,3.99,7),
    (18,'Gladiator','Action',2000,3.49,5),
    (19,'The Pursuit of Happyness','Drama',2006,2.99,4),
    (20,'Casablanca','Romance',1942,2.49,3);

INSERT Employees (employee_id,first_name,last_name,email,phone,position,hire_date,salary) VALUES
    (1,'Alice','Johnson','alice.johnson@example.com','123-456-7890','Manager','2022-01-10',55000),
    (2,'Bob','Smith','bob.smith@example.com','987-654-3210','Cashier','2023-03-15',35000),
    (3,'Charlie','Brown','charlie.brown@example.com','555-678-9012','Inventory Specialist','2021-07-20',40000),
    (4,'Diana','Davis','diana.davis@example.com','222-333-4444','Customer Service','2023-06-10',32000),
    (5,'Ethan','Wilson','ethan.wilson@example.com','777-888-9999','Cashier','2022-09-05',36000),
    (6,'Fiona','Miller','fiona.miller@example.com','444-555-6666','Shift Supervisor','2020-04-22',45000),
    (7,'George','Martinez','george.martinez@example.com','666-777-8888','Technician','2021-11-30',42000),
    (8,'Hannah','Garcia','hannah.garcia@example.com','111-222-3333','Receptionist','2022-08-14',33000),
    (9,'Ian','Hernandez','ian.hernandez@example.com','888-999-0000','Delivery Personnel','2023-01-19',31000),
    (10,'Julia','Lopez','julia.lopez@example.com','999-000-1111','Accountant','2020-12-01',50000);

INSERT Rentals (rental_id,customer_id,movie_id,employee_id,rental_date,return_date,status) VALUES
    (1,1,5,2,'2024-01-10','2024-01-15','Returned'),
    (2,2,10,3,'2024-02-05',NULL,'Rented'),
    (3,3,15,1,'2024-02-20','2024-02-25','Returned'),
    (4,4,7,5,'2024-03-01',NULL,'Rented'),
    (5,5,12,4,'2024-03-10','2024-03-17','Returned'),
    (6,6,18,2,'2024-04-01','2024-04-08','Returned'),
    (7,7,3,6,'2024-04-12',NULL,'Rented'),
    (8,8,9,3,'2024-05-05','2024-05-12','Returned'),
    (9,9,20,7,'2024-06-15','2024-06-20','Returned'),
    (10,10,2,8,'2024-06-25',NULL,'Rented'),
    (11,1,14,9,'2024-07-01','2024-07-07','Returned'),
    (12,2,6,2,'2024-07-08',NULL,'Rented'),
    (13,3,17,1,'2024-08-10','2024-08-15','Returned'),
    (14,4,8,4,'2024-09-05',NULL,'Rented'),
    (15,5,11,5,'2024-09-10','2024-09-15','Returned'),
    (16,6,1,2,'2024-09-20',NULL,'Rented'),
    (17,7,13,6,'2024-10-01','2024-10-06','Returned'),
    (18,8,4,3,'2024-10-12','2024-10-19','Returned'),
    (19,9,16,7,'2024-11-01',NULL,'Rented'),
    (20,10,19,8,'2024-11-10','2024-11-18','Returned'),
    (21,1,7,9,'2024-11-20',NULL,'Rented'),
    (22,2,12,2,'2024-12-01','2024-12-07','Returned'),
    (23,3,5,1,'2024-12-10',NULL,'Rented'),
    (24,4,15,4,'2024-12-20','2024-12-25','Returned'),
    (25,5,10,5,'2024-12-30',NULL,'Rented'),
    (26,6,8,2,'2025-01-05','2025-01-12','Returned'),
    (27,7,14,6,'2025-01-15',NULL,'Rented'),
    (28,8,17,3,'2025-01-25','2025-01-30','Returned'),
    (29,9,2,7,'2025-02-05',NULL,'Rented'),
    (30,10,9,8,'2025-02-10','2025-02-17','Returned'),
    (31,3,5,2,'2025-02-15',NULL,'Rented');

INSERT Payments (payment_id,rental_id,customer_id,employee_id,amount,payment_date,payment_method) VALUES
    (1,1,1,2,5.99,'2024-01-15','Credit Card'),
    (2,3,3,1,4.99,'2024-02-25','Debit Card'),
    (3,5,5,4,6.99,'2024-03-17','Cash'),
    (4,6,6,2,5.5,'2024-04-08','Online'),
    (5,8,8,3,7.25,'2024-05-12','Credit Card'),
    (6,9,9,7,6.5,'2024-06-20','Debit Card'),
    (7,11,1,9,5.99,'2024-07-07','Cash'),
    (8,13,3,1,4.75,'2024-08-15','Online'),
    (9,15,5,5,7,'2024-09-15','Credit Card'),
    (10,17,7,6,5.99,'2024-10-06','Debit Card'),
    (11,18,8,3,6.25,'2024-10-19','Cash'),
    (12,20,10,8,5.5,'2024-11-18','Online'),
    (13,22,2,2,6.75,'2024-12-07','Credit Card'),
    (14,24,4,4,5.99,'2024-12-25','Debit Card'),
    (15,26,6,2,4.99,'2025-01-12','Cash'),
    (16,28,8,3,6.5,'2025-01-30','Online'),
    (17,30,10,8,7.25,'2025-02-17','Credit Card');

-- SELECT Customers.customer_id, Customers.first_name, Customers.last_name
-- FROM Customers
-- JOIN Rentals ON Customers.customer_id=Rentals.customer_id
-- JOIN Movies ON Rentals.movie_id=Movies.movie_id
-- WHERE Movies.title="Avengers: Endgame"
-- GROUP BY Customers.customer_id

-- SELECT movie_id, title, rental_price
-- FROM Movies
-- WHERE rental_price > 4

-- SELECT Rentals.rental_id, Customers.customer_id, Movies.movie_id, Rentals.rental_date
-- FROM Rentals
-- JOIN Customers ON Rentals.customer_id=Customers.customer_id
-- JOIN Movies ON Rentals.movie_id=Movies.movie_id
-- WHERE return_date IS NULL
-- ORDER BY Rentals.rental_id

-- SELECT Movies.movie_id, Movies.title, COUNT(Rentals.movie_id) AS total_rentals
-- FROM Movies
-- JOIN Rentals ON Rentals.movie_id=Movies.movie_id
-- GROUP BY movie_id, title
-- ORDER BY total_rentals DESC LIMIT 5

-- SELECT Customers.customer_id, Customers.first_name, Customers.last_name, COUNT(Rentals.movie_id) AS total_rentals
-- FROM Customers
-- JOIN Rentals ON Rentals.customer_id=Customers.customer_id
-- GROUP BY Customers.customer_id, Customers.first_name, Customers.last_name
-- ORDER BY total_rentals DESC LIMIT 1

-- SELECT DATE_FORMAT(Payments.payment_date,'%Y-%m') AS month, SUM(Payments.amount) AS total_revenue
-- FROM Payments
-- GROUP BY month
-- ORDER BY month

-- SELECT Customers.customer_id, Customers.first_name, Customers.last_name
-- FROM Customers
-- JOIN Rentals ON Customers.customer_id=Rentals.customer_id
-- LEFT JOIN Payments ON Rentals.rental_id=Payments.rental_id
-- WHERE Payments.rental_id IS NULL    
-- GROUP BY Customers.customer_id

-- SELECT Employees.employee_id, Employees.first_name, Employees.last_name, COUNT(Rentals.employee_id) AS total_rentals
-- FROM Employees
-- JOIN Rentals ON Rentals.employee_id=Employees.employee_id
-- GROUP BY Employees.employee_id, Employees.first_name, Employees.last_name
-- ORDER BY total_rentals DESC LIMIT 3

-- SELECT Rentals.rental_id, Customers.first_name, Customers.last_name, Movies.title, Rentals.rental_date
-- FROM Rentals
-- JOIN Customers ON Customers.customer_id=Rentals.customer_id
-- JOIN Movies ON Movies.movie_id=Rentals.movie_id
-- WHERE Rentals.return_date IS NULL

-- SELECT payment_method, COUNT(Payments.payment_method) AS total_payments
-- FROM Payments
-- GROUP BY payment_method
-- ORDER BY total_payments DESC LIMIT 1

-- SELECT Movies.genre, COUNT(Rentals.movie_id) AS total_rented, SUM(Payments.amount) AS total_revenue
-- FROM Movies
-- JOIN Rentals ON Rentals.movie_id=Movies.movie_id
-- JOIN Payments ON Payments.rental_id=Rentals.rental_id
-- GROUP BY Movies.genre
-- ORDER BY total_revenue DESC

-- CREATE VIEW Unreturned AS SELECT Customers.customer_id, COUNT(Rentals.rental_id) AS unreturned_movies
-- FROM Customers
-- LEFT JOIN Rentals ON Customers.customer_id = Rentals.customer_id AND Rentals.return_date IS NULL
-- GROUP BY Customers.customer_id;
-- SELECT Customers.customer_id, Customers.first_name, Customers.last_name, Unreturned.unreturned_movies
-- FROM Customers
-- JOIN Unreturned ON Customers.customer_id=Unreturned.customer_id
-- WHERE Unreturned.unreturned_movies > 0
-- ORDER BY Unreturned.unreturned_movies DESC;

-- SELECT Movies.movie_id, Movies.title, Movies.genre, COUNT(Rentals.movie_id) AS total_rentals
-- FROM Movies
-- JOIN Rentals ON Rentals.movie_id=Movies.movie_id
-- GROUP BY Movies.movie_id, Movies.title, Movies.genre
-- ORDER BY total_rentals ASC LIMIT 10

CREATE VIEW Total_spent AS SELECT Customers.customer_id, Customers.first_name, Customers.last_name, SUM(Payments.amount) AS total_spent
FROM Customers
JOIN Payments ON Payments.customer_id=Customers.customer_id
GROUP BY Customers.customer_id, Customers.first_name, Customers.last_name;

CREATE VIEW Average_spent AS SELECT AVG(Total_spent.total_spent) AS average
FROM Total_spent;

SELECT Total_spent.customer_id, Total_spent.first_name, Total_spent.last_name, Total_spent.total_spent
FROM Total_spent
WHERE Total_spent.total_spent > (SELECT average FROM Average_spent)
ORDER BY Total_spent.total_spent DESC;