DROP PROCEDURE IF EXISTS sp_GenerateCouponsNumber;

CREATE PROCEDURE sp_GenerateCouponsNumber
(
    id_direction uuid
)
    language plpgsql
as
$$
Begin
	declare
		_max integer; 
		_gen_count integer;
	Begin
		_max := coalesce((select max(MilkCouponsIdID) from AKUZ.T_MILK_COUPONS_ID), 0);
		_gen_count := (select Count(*) from AKUZ.T_DIRECTION_TO_MILK_RECIPE_COUPON co where co.DirectionToMilkRecipe = id_direction);

        DROP TABLE IF EXISTS tempTable;
		CREATE TEMP TABLE tempTable(
		    CouponId varchar(100)
		  , Id integer
		);

        insert into tempTable
        select 
         t.x as CouponId, 
         row_number() over(order by t.x) as Id
        from
		(
			select right('000000000000' || ltrim(('x' || translate(gen_random_uuid()::varchar(36), '-', ''))::bit(64)::bigint::varchar(12), '-'), 12) as x
			from pg_class pc
			limit _gen_count
		) 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.CouponId, now()::timestamp 
        from tempTable 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.CouponId
        from AKUZ.T_DIRECTION_TO_MILK_RECIPE_COUPON co
        join cte cte on cte.DirectionToMilkRecipeCouponID = co.DirectionToMilkRecipeCouponID
        join tempTable t on t.Id = cte.ordNum;
	end;
End;
$$;