--1. Без параметров
--Меню на текущую дату

DROP PROCEDURE GetMenuToday

CREATE or alter procedure GetMenuToday as
(select name as N'Название', price + extra as N'Цена'
 from Products
 where availability_tomorrow = 1)

    exec GetMenuToday;

    --2. С входными параметрами
--Сформировать заказ для конкретного клиета(параметр): три его наиболее любимых продукта, по 2 порции

    DROP PROCEDURE GetThreeBestProductByClient
    CREATE or alter procedure GetThreeBestProductByClient @ClientName nvarchar(255) as
    (select top 3 p.name, 2 as Portions
     from orders
              join rel_orders_products rop on orders.id = rop.order_id
              join products p on p.id = rop.product_id
     where customer_name = @ClientName
     group by p.name)

        exec GetThreeBestProductByClient N'admin';

        --3 Входные и выходные параметры
--Найти наименее и наиболее популярный продукт
--Взял для входного параметра: цена, выше которой будет проходить выборка

        DROP PROCEDURE Foo
        CREATE or alter procedure Foo @MinimalPrice INT = 30, @Result nvarchar(255) OUTPUT as
        begin
            declare @TheBestProduct nvarchar(255),
                @NotTheBestProduct nvarchar(255);
            declare @SavedProductListWithRank table
                                              (
                                                  [Name] NVARCHAR(255),
                                                  [rnk1] int,
                                                  [rnk2] int
                                              );
            with ProductList as (select p2.name, count(*) as CountProduct
                                 from orders
                                          join rel_orders_products rop2 on orders.id = rop2.order_id
                                          join products p2 on p2.id = rop2.product_id
                                 where p2.price + p2.extra >= @MinimalPrice
                                 group by p2.name),
                 ProductListWithRank as (select name                                           as Name,
                                                dense_rank() over (order by CountProduct)      as rnk1,
                                                dense_rank() over (order by CountProduct desc) as rnk2
                                         from ProductList)
            insert
            into @SavedProductListWithRank
            select *
            from ProductListWithRank;

            select @TheBestProduct = name
            from @SavedProductListWithRank
            where rnk2 = 1;

            select @NotTheBestProduct = name
            from @SavedProductListWithRank
            where rnk1 = 1;

            set @Result = N'Наименее продукт: ' + @TheBestProduct + N', наиболее продукт: ' + @NotTheBestProduct
        end

        declare @FooResult NVARCHAR(255);
            exec Foo default, @FooResult output;
            print @FooResult;


            --4. Вложенные процедуры
--отчет по наименее и наиболее популярным продуктам
--дата, продукт, количество
            DROP PROCEDURE GetProductsByDate
            CREATE or alter procedure GetProductsByDate @Date date, @Result nvarchar(255) OUTPUT as
            begin
                declare @TheBestProduct nvarchar(255),
                    @NotTheBestProduct nvarchar(255);
                declare @SavedProductListWithRank table
                                                  (
                                                      [Name]  NVARCHAR(255),
                                                      [Count] nvarchar(255),
                                                      [rnk1]  int,
                                                      [rnk2]  int
                                                  );
                with ProductList as (select p2.name, count(*) as CountProduct
                                     from orders
                                              join rel_orders_products rop2 on orders.id = rop2.order_id
                                              join products p2 on p2.id = rop2.product_id
                                     where date = @Date
                                     group by p2.name),
                     ProductListWithRank as (select name                                           as Name,
                                                    CountProduct                                   as Count,
                                                    dense_rank() over (order by CountProduct)      as rnk1,
                                                    dense_rank() over (order by CountProduct desc) as rnk2
                                             from ProductList)
                insert
                into @SavedProductListWithRank
                select *
                from ProductListWithRank;

                select @NotTheBestProduct = name + '(' + Count + ')'
                from @SavedProductListWithRank
                where rnk2 = 1;

                select @TheBestProduct = name + '(' + Count + ')'
                from @SavedProductListWithRank
                where rnk1 = 1;

                set @Result = N'Наименее продукт: ' + @TheBestProduct + N', наиболее продукт: ' + @NotTheBestProduct
            end

                --declare @FooResult NVARCHAR(255);
                --exec GetProductsByDate '2022-11-15', @FooResult output;
                --print @FooResult;

                drop proc PrintProductsTopAtAllDates;

                CREATE or alter procedure PrintProductsTopAtAllDates as
                begin
                    DECLARE Dates_Curcor CURSOR SCROLL FOR
                        SELECT distinct o.date
                        from orders as o;
                    declare @Date date;
                    declare @FooResult NVARCHAR(255);

                    open Dates_Curcor
                    FETCH NEXT FROM Dates_Curcor INTO @Date

                    WHILE @@FETCH_STATUS = 0
                        begin

                            exec GetProductsByDate @Date, @FooResult output;
                            print @Date
                            print @FooResult;

                            FETCH NEXT FROM Dates_Curcor INTO @Date
                        end

                    CLOSE Dates_Curcor
                    DEALLOCATE Dates_Curcor
                end

                    exec PrintProductsTopAtAllDates;

                    --Функции
--1. Скалярная
--Подсчитывает выручку на текущую дату

                    create or alter function GetViruchka(@date date)
                        returns int
                    as
                    begin
                        declare @Viruchka int;

                        select @Viruchka = sum(extra)
                        from orders
                                 join rel_orders_products rop3 on orders.id = rop3.order_id
                                 join products p3 on p3.id = rop3.product_id
                        where date = @date
                        if @Viruchka is null
                            SET @Viruchka = 0
                        return @Viruchka
                    end

                declare @date date = '2022-11-15';
                    --declare @date date = GETDATE();
                    print N'Выручка: ' + CAST(SeregaTheDed_SQLLogin_1.GetViruchka(@date) as varchar)
                    --2. Inline
--произошло массовое отравление, предположительно продуктом К
--Выдать список заказов(всю возможную информацию) по этому продукту на конкретную дату

                    create or alter function GetBadOrdersByDate(@date date, @badProductName nvarchar(255))
                        returns table AS
                            return
                                (
                                    select p4.name, rop4.count, customer_name, date
                                    from orders
                                             join rel_orders_products rop4 on orders.id = rop4.order_id
                                             join products p4 on p4.id = rop4.product_id
                                    where date = @date
                                      and p4.name = @badProductName
                                )


                declare @date date = '2022-11-30';
                declare @productName nvarchar(255) = N'мышь жаренная';

                SELECT *
                FROM SeregaTheDed_SQLLogin_1.GetBadOrdersByDate(@date, @productName)
                    --3. Multi-statement
--Университет решил оплатить 10% расходов на питание за прошедший год.
--Вывод
--Заказчик, январь, февраль, ..., декабрь
--в ячейках выплаты


                    create or alter function GetCustomerList()
                        returns table AS
                            return
                                (
                                    select distinct customer_name
                                    from orders
                                )

                    drop function SeregaTheDed_SQLLogin_1.GetRollbackCustomer

                    create or alter function GetRollbackCustomer()
                        returns @RollbackCustomerTable TABLE
                                                       (
                                                           Customer NVARCHAR(255) NOT NULL,
                                                           [01]     float default (0),
                                                           [02]     float default (0),
                                                           [03]     float default (0),
                                                           [04]     float default (0),
                                                           [05]     float default (0),
                                                           [06]     float default (0),
                                                           [07]     float default (0),
                                                           [08]     float default (0),
                                                           [09]     float default (0),
                                                           [10]     float default (0),
                                                           [11]     float default (0),
                                                           [12]     float default (0)
                                                       )
                    AS
                    begin
                        declare @CurrentCustomerName nvarchar(255);
                        declare CustomersCursor cursor scroll for select * from SeregaTheDed_SQLLogin_1.GetCustomerList();

                        open CustomersCursor;


                        FETCH NEXT FROM CustomersCursor INTO @CurrentCustomerName
                        while @@fetch_status = 0
                            begin
                                insert into @RollbackCustomerTable(Customer)
                                values (@CurrentCustomerName);

                                declare @CurrentMonthNumber int = 1;
                                while @CurrentMonthNumber <= 12
                                    begin
                                        declare @TotalMonthAmount float;
                                        select @TotalMonthAmount = sum(p5.price + p5.extra)
                                        from orders o
                                                 join rel_orders_products rop5 on o.id = rop5.order_id
                                                 join products p5 on p5.id = rop5.product_id
                                        where customer_name = @CurrentCustomerName
                                          and month(date) = @CurrentMonthNumber;

                                        declare @KostilQuery nvarchar(max);
                                        set @KostilQuery =
                                        'update @RollbackCustomerTable
                                        set [' + FORMAT(@CurrentMonthNumber, '00') + '] = ' + CAST((@TotalMonthAmount * 0.1) as nvarchar(255)) +
                                        'where Customer = N'''+ @CurrentCustomerName+'''';
                                        --exec(@KostilQuery);
                                        sp_executesql @KostilQuery;
                                        --exec @KostilQuery;
                                        set @CurrentMonthNumber = @CurrentMonthNumber + 1;
                                    end

                                    FETCH NEXT FROM CustomersCursor INTO @CurrentCustomerName
                            end

                        close CustomersCursor;
                        deallocate CustomersCursor;
                        return
                    end


                    select *
                    from SeregaTheDed_SQLLogin_1.GetRollbackCustomer()