IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'sp_GenerateCouponsNumber') AND type in (N'P', N'PC'))
	DROP PROCEDURE sp_GenerateCouponsNumber
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_GenerateCouponsNumber
	@id_direction uniqueidentifier
AS
BEGIN

	declare @max int, @gen_count int 
    set @gen_count = (select Count(*) from AKUZ.T_DIRECTION_TO_MILK_RECIPE_COUPON co where co.DirectionToMilkRecipe = @id_direction)
	set @max = isnull((select max(MilkCouponsIdID) from AKUZ.T_MILK_COUPONS_ID ), 0);

     select 
         t.x, 
         row_number() over(order by t.x) as id
     into #Table
     from
     (
     	select top(@gen_count) right('000000000000' + cast(cast(rand(cast(newid() as varbinary))*1000000000000 as bigint) as varchar(12)), 12) as x
     	from sys.objects
     ) t
     where not exists(select * from AKUZ.T_MILK_COUPONS_ID tt where tt.Id = t.x);
     
     insert into AKUZ.T_MILK_COUPONS_ID(MilkCouponsIdID, Id, CreateDate)
     select t.id + @max, t.x, getdate() 
     from #Table t;
     
    with cte(DirectionToMilkRecipeCouponID, ordNum)
    as (
        select co.DirectionToMilkRecipeCouponID, 
                ROW_NUMBER() over (order by co.CouponId)
        from AKUZ.T_DIRECTION_TO_MILK_RECIPE_COUPON co
        where co.DirectionToMilkRecipe = @id_direction)
    update co
    set co.CouponId = t.x
    from AKUZ.T_DIRECTION_TO_MILK_RECIPE_COUPON co
    join cte cte on cte.DirectionToMilkRecipeCouponID = co.DirectionToMilkRecipeCouponID
    join #Table t on t.id = cte.ordNum;
END