--a) Триггер любого типа на добавление нового заказа - если заказчик более 2х раз не забирал свой заказ, то больше от него заказы не принимаем.
CREATE OR ALTER TRIGGER Tr_InsertOrder
    ON orders
    instead OF INSERT
    AS
BEGIN
    --select distinct customer_name from orders --seraja
    INSERT INTO orders
    SELECT customer_name, date, user_id, status_id
    FROM inserted
    where customer_name not in (select customer_name
                                from orders
                                         join users u on orders.user_id = u.id
                                where datediff(day, date, getdate()) > 1
                                  and status_id = 1
                                group by customer_name
                                having count(*) > 2)
END
    select count(*)
    from orders
    -----------
    insert into orders
    values (N'admin', getdate(), 5, 1)
    -----------
    select count(*)
    from orders
    -----------
    insert into orders
    values (N'seraja', getdate(), 5, 1)
    -----------
    select count(*)
    from orders


    --b) Последующий триггер на изменение стоимости продукта– менять стоимость продукта можно, если все заказы включающие этот продукт на данный момент оплачены.
-- Изменение цены возможно не более чем на 5%

    CREATE OR ALTER TRIGGER Tr_UpdateProduct
        ON products
        after UPDATE
        AS
    BEGIN
        if not update(price) return;

        declare @goodProduct table
                             (
                                 ProductName nvarchar(max)
                             );
        with cte1_AllProductsCount as (select p.name, count(*) as AllCount
                                       from orders
                                                join rel_orders_products rop on orders.id = rop.order_id
                                                join products p on rop.product_id = p.id
                                       group by p.name),
             cte2_PaidProductsCount as (select p.name, count(*) as PaidCount
                                        from orders
                                                 join rel_orders_products rop on orders.id = rop.order_id
                                                 join products p on rop.product_id = p.id
                                        where status_id = 2
                                           or status_id = 4
                                        group by p.name)
        insert
        into @goodProduct
        select AllP.name
        from cte1_AllProductsCount AllP
                 left join cte2_PaidProductsCount PaidP on AllP.name = PaidP.name
        where PaidP.PaidCount is null
           or PaidP.PaidCount = AllP.AllCount;


        with BadProducts as (Select i.id as Id, d.price as oldValue, i.price as newValue
                             from deleted d
                                      join inserted i on i.id = d.id
                             where i.name not in (select * from @goodProduct)
                                or abs(i.price - d.price) > d.price * 0.05)

        update products
        set products.price = oldValue
        from products p
                 join BadProducts bp
                      on p.id = bp.Id
        --DECLARE ProductCursor CURSOR
        --FOR
        --open ProductCursor

        --declare @ProductId

    END


--неоплаченный продукт
select *
from products
where name = N'мышь жаренная'
---------------
update products
set price = 101
where name = N'мышь жаренная'
--------------
select *
from products
where name = N'Итальяночка'
-------------
update products
set price = 999999
where name = N'Итальяночка'
------------
update products
set price = 3242
where name = N'Итальяночка'




--c) Замещающий триггер на операцию добавления(редактирования) данных в таблицу position – продукты в первой и второй позиции должны быть различны.
CREATE OR ALTER TRIGGER Tr_InsertUpdatePositions
    ON positions
    instead OF INSERT, update
    AS
BEGIN
    DECLARE @cntInsert INT, @cntDelete INT
    SELECT @cntInsert = COUNT(*) FROM inserted
    SELECT @cntDelete = COUNT(*) FROM deleted

    IF @cntDelete > 0 AND @cntInsert > 0
            BEGIN
                DECLARE InsertedCursor CURSOR
                FOR
                (select i.id as Id, i.product_id_first AS leftt, i.product_id_second as rightt
                from inserted i
                where i.product_id_first  != i.product_id_second)
                open InsertedCursor
                declare @Id int, @IdFirst int, @IdSecond int
                fetch next from InsertedCursor into @Id, @IdFirst, @IdSecond
                while @@fetch_status = 0
                begin
                    update positions
                    set product_id_first = @IdFirst
                    where id = @Id

                    update positions
                    set product_id_second = @IdSecond
                    where id = @Id

                    fetch next from InsertedCursor into @Id, @IdFirst, @IdSecond
                end
                close InsertedCursor
                deallocate InsertedCursor
            END
        ELSE IF @cntInsert > 0
            BEGIN
                insert into positions
                select status_id, date, product_id_first, product_id_second, customer_name, user_id, with_sauce, price
                from inserted i
                where i.product_id_first  != i.product_id_second
            END

end





--a) Триггер любого типа на добавление клиента – если клиент с таким
--   паспортом уже есть, то не добавляем его, а выдаем соответствующее сообщение.

create or alter trigger Tr_UniquePassport
    on Client
    instead of insert
    as
begin
    declare clientCursor cursor
    for
    (select *
    from inserted)

    declare @newClientId int,
        @newClientName nvarchar(max),
        @newClientPassport nvarchar(max),
        @newClientDiscount decimal(4,3)

    open clientCursor
    fetch next from clientCursor into @newClientId, @newClientName, @newClientPassport, @newClientDiscount

    while @@FETCH_STATUS = 0
    begin
        declare @flag int

        select @flag = count(*)
        from Client
        where Client.Passport = @newClientPassport

        if @flag > 0
            print 'Bad passport: ' + @newClientPassport
        else
            insert into Client
            Values(@newClientName, @newClientPassport, @newClientDiscount)

        fetch next from clientCursor into @newClientId, @newClientName, @newClientPassport, @newClientDiscount
    end

    close clientCursor
    deallocate clientCursor
end


insert into Client
Values
('Test1', '7777777777', null),
('Test2', '7777732177', null),
('Test3', '7777712377', null),
('Test4', '7777777777', null),
('Test5', '7777732177', null)






--b)  Последующий триггер на изменение стоимости любого типа отеля –
--    для 5-звездочных отелей стоимость может меняться только в большую сторону,
--    для 1,2-звездочных – только в меньшую.
go


create or alter trigger Tr_ChangePriceHotel
    on Hotel
    after update
    as
begin
    --print
    select i.Name as HotelName,
        i.Type as Starts,
        i.Price as NewValue,
        d.Price as OldValue
    from inserted as i

    --alghoritm
    join deleted as d on d.ID=i.ID
    where
    i.Type = 5 and i.Price < d.Price
    or
    i.Type < 3 and i.Price > d.Price

    declare @flag int

    select @flag = count(*)
    from inserted as i
    join deleted as d on d.ID=i.ID
    where
    i.Type = 5 and i.Price < d.Price
    or
    i.Type < 3 and i.Price > d.Price

    if @flag != 0
    begin
        print 'Bad transaction'
        rollback
    end
end

update Hotel
set Price = 0
where Type = 5