Заполни поставки так, чтобы запрос работал: Определить первых 3 поставщиков, осуществивших поставки на
наибольшую сумму. (не менее 12 поставок, в каждой поставке -
накладная, где не менее 5 продуктов (в базе))
DROP DATABASE restaurant;
CREATE DATABASE restaurant;
USE restaurant;

--  Таблица отдыхающих -- 

CREATE TABLE vacationers
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Persons_name VARCHAR(20) NOT NULL,
    Persons_surname VARCHAR(20) NOT NULL,
    Age TINYINT UNSIGNED NOT NULL,
    Phone VARCHAR(11) UNIQUE NOT NULL,
    Email VARCHAR(30) UNIQUE,
    Passport VARCHAR(10) NOT NULL UNIQUE,
    Chek_in_date DATETIME NOT NULL,
    Time_of_remain TINYINT UNSIGNED NOT NULL
);

--  Таблица видов блюд -- 

CREATE TABLE dish_type
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Dish_name VARCHAR(30) NOT NULL
);

--  Таблица рецептов -- 

CREATE TABLE recipe
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Action_list TEXT NOT NULL,
    Cooking_time TIME NOT NULL
);

-- Таблица продукта -- 

CREATE TABLE foodstuff
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Food_name VARCHAR(20) NOT NULL,
	Calorific DECIMAL(6,2) NOT NULL,
    Average_price DECIMAL(7,2) NOT NULL
);

-- Таблица склада -- 

CREATE TABLE food_storage
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Foodstuff_id INT UNSIGNED NOT NULL,
    Amount DECIMAL(5, 2) NOT NULL,
    FOREIGN KEY (Foodstuff_id) REFERENCES foodstuff(Id)
);

-- Таблица блюда -- 

CREATE TABLE dish
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Recipe_id INT UNSIGNED NOT NULL,
    Dish_name VARCHAR(20) NOT NULL,
    Calorific DECIMAL(6,2) NOT NULL,
    Weight DECIMAL(6,2) NOT NULL,
    Cost_price DECIMAL(6, 2) NOT NULL,
    FOREIGN KEY (Recipe_id) REFERENCES recipe(Id)
);

-- Таблица блюда -- 

CREATE TABLE composition
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Dish_id INT UNSIGNED NOT NULL,
    Food_id INT UNSIGNED NOT NULL,
    Amount DECIMAL(5, 2) NOT NULL,
    FOREIGN KEY (Dish_id) REFERENCES dish(Id),
    FOREIGN KEY (Food_id) REFERENCES foodstuff(Id)
);

-- Таблица меню -- 

CREATE TABLE menu
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Dish_id INT UNSIGNED NOT NULL,
    Type_id INT UNSIGNED NOT NULL,
    Dish_description TINYTEXT NOT NULL,
    Price DECIMAL(6, 2) NOT NULL,
    FOREIGN KEY (Dish_id) REFERENCES dish(Id),
    FOREIGN KEY (Type_id) REFERENCES dish_type(Id)
);

-- Таблица трапеза -- 

CREATE TABLE repast
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Vacationer_id INT UNSIGNED NOT NULL,
    Menu_id INT UNSIGNED NOT NULL,
    Repast_date DATETIME NOT NULL,
    Cost DECIMAL(7, 2) NOT NULL,
    FOREIGN KEY (Vacationer_id) REFERENCES vacationers(Id),
    FOREIGN KEY (Menu_id) REFERENCES menu(Id)
);

-- Таблица касса -- 

CREATE TABLE till
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Profit DECIMAL(10, 2) NOT NULL,
    Decline DECIMAL(10, 2) NOT NULL,
    Total DECIMAL(10, 2) NOT NULL 
);

--  Таблица поставщиков -- 

CREATE TABLE provider
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Provider_name VARCHAR(20) NOT NULL,
    Provider_type VARCHAR(20) NOT NULL,
    Phone VARCHAR(11) UNIQUE NOT NULL,
    TIN VARCHAR(12) UNIQUE NOT NULL,
    Email VARCHAR(30) UNIQUE,
    Adress TINYTEXT
);

-- Таблица поставок -- 

CREATE TABLE delivery
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Foodstuff_id INT UNSIGNED NOT NULL,
    Amount DECIMAL(5, 2) NOT NULL, 
    Price DECIMAL(7, 2) NOT NULL,
    FOREIGN KEY (Foodstuff_id) REFERENCES foodstuff(Id)
);

-- Таблица квитанция -- 

CREATE TABLE estimates
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Provider_id INT UNSIGNED NOT NULL,
    Delivery_id INT UNSIGNED NOT NULL,
    Cost DECIMAL(8, 2) NOT NULL,
    Receipt_date DATETIME NOT NULL,
    FOREIGN KEY (Provider_id) REFERENCES provider(Id),
    FOREIGN KEY (Delivery_id) REFERENCES delivery(Id)
);

-- Таблица столов -- 

CREATE TABLE booking
(
	Id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Vacationers_id INT UNSIGNED NOT NULL,
    Visibility TINYINT UNSIGNED NOT NULL,
    FOREIGN KEY (Vacationers_id) REFERENCES vacationers(Id)
);

-- Заполнение таблицы отдыхающих

INSERT INTO vacationers (Persons_name, Persons_surname, Age, Phone, Email, Passport, Chek_in_date, Time_of_remain) VALUES
('Иван', 'Иванов', 34, '89101112233', 'ivanov@example.com', '1234567890', '2024-05-01 14:00:00', 10),
('Кирилл', 'Коротыш', 20, '89231820255', 'korotysh@gmail.com', '5017456312', '2024-05-17 10:00:00', 180),
('Матвей', 'Полухин', 20, '89137721052', 'matvey@gmail.com', '5017456493', '2024-02-01 12:30:12', 1),
('Александр', 'Башкиров', 19, '89137396510', 'sanya@gmail.com', '1488228228', '2023-01-01 00:00:00', 23),
('Мария', 'Петрова', 28, '89102223344', 'petrova@example.com', '0987654321', '2024-05-02 11:48:00', 7);

-- Заполнение таблицы видов блюдa

INSERT INTO dish_type (Dish_name) VALUES
('Суп'),
('Салат'),
('Горячее'),
('Десерт');

-- Заполнение таблицы рецептов 

INSERT INTO recipe (Action_list, Cooking_time) VALUES
('Очистить картофель, нарезать кубиками, варить 20 минут', '00:30:00'),
('Нарезать овощи, заправить маслом, перемешать', '00:15:00');

-- Заполнение таблицы продуктов

INSERT INTO foodstuff (Food_name, Calorific, Average_price) VALUES
('Картофель', 77.00, 30.00),
('Морковь', 41.00, 25.00),
('Красный перец', 20.00, 50.00),
('Огурец', 10.30, 15.00),
('Говядина', 250.00, 400.00),
('Молоко', 42.00, 60.00);

-- Заполнение таблицы склада

INSERT INTO food_storage (Foodstuff_id, Amount) VALUES
(1, 100.00),
(2, 50.00),
(3, 200.00),
(4, 30.00);

-- Заполнение таблицы блюда

INSERT INTO dish (Recipe_id, Dish_name, Calorific, Weight, Cost_price) VALUES
(1, 'Картофельное пюре', 150.00, 200.00, 50.00),
(2, 'Овощной салат', 80.00, 150.00, 30.00);

-- Заполнение таблицы состав блюда

INSERT INTO composition (Dish_id, Food_id, Amount) VALUES
(1, 1, 200.00),
(2, 2, 40.00),
(2, 3, 50.00),
(2, 4, 60.00);

-- Заполнение таблицы меню

INSERT INTO menu (Dish_id, Type_id, Dish_description, Price) VALUES
(1, 3, 'Вкусное картофельное пюре', 100.00),
(2, 2, 'Свежий овощной салат', 60.00);

-- Заполнение таблицы трапеза

INSERT INTO repast (Vacationer_id, Menu_id, Repast_date, Cost) VALUES
(1, 1, '2023-05-01 19:00:00', 100.00),
(2, 2, '2023-05-03 14:00:00', 60.00);

-- Заполнение таблицы касса

INSERT INTO till (Profit, Decline, Total) VALUES
(1600.00, 600.00, 1000.00);

-- Заполнение таблицы поставщиков

INSERT INTO provider (Provider_name, Provider_type, Phone, TIN, Email, Adress) VALUES
('ООО Продукты', 'Продукты питания', '89011234567', '123456789012', 'products@mail.ru', 'ул. Примерная, д. 1'),
('ООО Фастфуд', 'Продукты быстрого приготовления', '89011234566', '098765432107', 'nyamnyam@mail.ru', 'ул. Примерная, д. 3'),
('ООО Весёлый молочник', 'Молокочная продукция', '89012234566', '198765432107', 'milk@mail.ru', 'ул. Молочная, д. 4'),
('ООО Мясо', 'Мясная продукция', '89019876543', '098765432109', 'meat@mail.ru', 'ул. Примерная, д. 2');

-- Заполнение таблицы поставок

INSERT INTO delivery (Foodstuff_id, Amount, Price) VALUES
(1, 50.00, 1500.00),
(3, 100.00, 40000.00);

-- Заполнение таблицы квитанций

INSERT INTO estimates (Provider_id, Delivery_id, Cost, Receipt_date) VALUES
(1, 1, 1500.00, '2023-05-01 10:00:00'),
(2, 2, 40000.00, '2023-05-02 11:00:00');

-- Заполнение таблицы столов

INSERT INTO booking (Vacationers_id, Visibility) VALUES
(1, 3),
(2, 4);

-- Запрос. Определить долю веса продукта в общем весе блюда в процентах
 
SELECT 
    d.Dish_name AS BLUDO,
    f.Food_name AS NAME_PROD,
    d.Weight AS VEC,
    (c.Amount / d.Weight * 100) AS PROC
FROM 
    composition c
JOIN 
    dish d ON c.Dish_id = d.Id
JOIN 
    foodstuff f ON c.Food_id = f.Id;

--  Запрос. Определить первых 3 поставщиков, осуществивших поставки на наибольшую сумму

SELECT 
    p.Provider_name,
    SUM(e.Cost) AS Total_Sum,
    COUNT(d.Id) AS Total_Deliveries
FROM 
    provider p
JOIN 
    estimates e ON p.Id = e.Provider_id
JOIN 
    delivery d ON e.Delivery_id = d.Id
GROUP BY 
    p.Provider_name
HAVING 
    COUNT(d.Id) >= 12 AND SUM(d.Amount) >= 5
ORDER BY 
    Total_Sum DESC
LIMIT 3;

-- Запрос. Показать относительный ранг веса продукта среди других продуктов этого же блюда

SELECT 
    c.Dish_id,
    f.Food_name,
    c.Amount AS Food_Weight,
    d.Dish_name,
    RANK() OVER (PARTITION BY c.Dish_id ORDER BY c.Amount DESC) AS Weight_Rank
FROM 
    composition c
JOIN 
    foodstuff f ON c.Food_id = f.Id
JOIN 
    dish d ON c.Dish_id = d.Id;

-- Запрос. Подсчет скользящего значения. Получить среднее значение цены продукта в ходе поступления продуктов

SELECT 
    f.Food_name,
    d.Price,
    AVG(d.Price) OVER (PARTITION BY f.Food_name ORDER BY d.Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Moving_Avg_Price
FROM 
    delivery d
JOIN 
    foodstuff f ON d.Foodstuff_id = f.Id;