---------------
-- Задание 1 --
---------------

CREATE VIEW PaymentNotifications AS
SELECT Customers.email, Payments.amount, Orders.date
FROM Payments
INNER JOIN Orders ON Payments.order_id = Orders.id
INNER JOIN Customers ON Orders.customer_id = Customers.id
GO

DROP VIEW PaymentNotifications
GO

-- При получении оплаты уведомлять клиентов, о начале
-- обработки заказа на указанную дату.
SELECT * FROM PaymentNotifications
GO


CREATE VIEW DeliveryExpenses AS
SELECT Deliveries.date, Vehicles.gas_consumption, Routes.distance
FROM Deliveries
INNER JOIN Vehicles ON Deliveries.vehicle_id = Vehicles.id
INNER JOIN Routes ON Deliveries.route_id = Routes.id
WHERE DATEPART(year, date) = DATEPART(year, GETDATE())
GO

DROP VIEW DeliveryExpenses
GO

-- Посчитать затраты на топливо по месяцам в текущем году.
SELECT DATEPART(month, date), SUM(gas_consumption * distance)
FROM DeliveryExpenses
GROUP BY DATEPART(month, date)
GO


CREATE VIEW BoxDates AS
SELECT Orders.id, Boxes.id, Deliveries.date
FROM Shipments
INNER JOIN Orders ON Shipments.order_id = Orders.id
INNER JOIN Boxes ON Orders.id = Boxes.order_id
INNER JOIN Deliveries ON Shipments.delivery_id = Deliveries.id
WHERE Deliveries.date > GETDATE()
GO

DROP VIEW BoxDates
GO

-- Найти даты доставки всех недоставленных коробок.
SELECT * FROM Boxes
GO

---------------
-- Задание 2 --
---------------


---------------
-- Задание 3 --
---------------