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

-- CREATE TRIGGER OrderDateUpdate ON Orders
-- AFTER UPDATE
-- AS BEGIN
--     DECLARE @order_id INT, @delivery_id INT, @date DATETIME;
--     SELECT @order_id = inserted.id FROM inserted;
--     SELECT @delivery_id FROM Shipments WHERE order_id=@order_id;
--     UPDATE Deliveries SET date=@date WHERE id=@delivery_id;
-- END

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

CREATE VIEW OrdersView WITH SCHEMABINDING AS
SELECT Orders.id, Orders.customer_id,
       Orders.date order_date,
       D.driver_id, D.vehicle_id, D.route_id,
       D.date delivery_date
FROM dbo.Orders
JOIN dbo.Shipments ON Orders.id = Shipments.order_id
JOIN dbo.Deliveries D on Shipments.delivery_id = D.id
JOIN dbo.Cities ON Orders.city_id = Cities.id
GO

DROP VIEW OrdersView
GO

-- INSTEAD OF INSERT

CREATE TRIGGER InsertOrdersView ON OrdersView
INSTEAD OF INSERT
AS BEGIN
    DECLARE @route_id INT, @city_src INT, @city_dst INT;
    SELECT @route_id=inserted.route_id FROM inserted;
    SELECT @city_src=city1_id, @city_dst=city2_id FROM Routes WHERE id=@route_id;
    IF @city_src IS NULL OR @city_dst IS NULL BEGIN
        RAISERROR (N'Не найден указанный путь', 10, 1)
        ROLLBACK TRAN
    END

    INSERT INTO Orders (customer_id, city_id, date)
    SELECT inserted.customer_id, @city_dst, inserted.order_date
    FROM inserted;
    DECLARE @order_id INT;
    SET @order_id = SCOPE_IDENTITY();

    INSERT INTO Deliveries (driver_id, vehicle_id, route_id, date)
    SELECT inserted.driver_id, inserted.vehicle_id, inserted.route_id, inserted.delivery_date
    FROM inserted;
    DECLARE @delivery_id INT;
    SET @delivery_id = SCOPE_IDENTITY();

    INSERT INTO Shipments (order_id, delivery_id) VALUES (@order_id, @delivery_id);
END
GO

DROP TRIGGER InsertOrdersView
GO

INSERT INTO OrdersView (id, customer_id, order_date, driver_id, vehicle_id, route_id, delivery_date)
VALUES (1, 123, '2021-09-12', 43, 12, 6, '2021-09-24')
GO

-- INSTEAD OF UPDATE

CREATE TRIGGER UpdateOrdersView ON OrdersView
INSTEAD OF UPDATE
AS BEGIN
    DECLARE @route_id INT, @city_src INT, @city_dst INT;
    SELECT @route_id=route_id FROM inserted;
    SELECT @city_src=city1_id, @city_dst=city2_id FROM Routes WHERE id=@route_id;
    IF @city_src IS NULL OR @city_dst IS NULL BEGIN
        RAISERROR (N'Не найден указанный путь', 10, 1)
        ROLLBACK TRAN
    END

    UPDATE Orders
    SET customer_id=inserted.customer_id,
        city_id=@city_dst,
        date=inserted.order_date
    FROM inserted
    WHERE Orders.id=inserted.id;

    DECLARE @order_id INT, @delivery_id INT;
    SELECT @order_id=inserted.id FROM inserted;
    SELECT @delivery_id FROM Shipments WHERE order_id=@order_id;

    UPDATE Deliveries
    SET driver_id=inserted.driver_id,
        vehicle_id=inserted.vehicle_id,
        route_id=inserted.route_id,
        date=inserted.delivery_date
    FROM inserted
    WHERE Deliveries.id = @delivery_id;
END
GO

DROP TRIGGER UpdateOrdersView
GO

-- TODO: Добавить запрос с UPDATE

-- INSTEAD OF DELETE

CREATE TRIGGER DeleteOrdersView ON OrdersView
INSTEAD OF DELETE
AS BEGIN
    DECLARE @order_id INT, @delivery_id INT;
    SELECT @order_id = deleted.id FROM deleted;
    SELECT @delivery_id FROM Shipments WHERE order_id=@order_id;

    DELETE FROM Boxes WHERE order_id=@order_id;
    DELETE FROM Payments WHERE order_id=@order_id;
    DELETE FROM Deliveries WHERE id=@delivery_id;
    DELETE FROM Shipments WHERE order_id=@order_id AND delivery_id=@delivery_id;
    DELETE FROM Orders WHERE id=@order_id;
END
GO

DROP TRIGGER DeleteOrdersView
GO

DELETE FROM OrdersView WHERE delivery_date='2021-09-12'
GO