DROP PROCEDURE IF EXISTS sp_GenerateCouponsNumber;

CREATE PROCEDURE sp_GenerateCouponsNumber
(
    id_direction uuid
)
    language plpgsql
as
$$
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 AKUZ.T_DIRECTION_TO_MILK_RECIPE_COUPON coupon
    set CouponId = t.CouponId
	from cte
    join tempTable t on t.Id = cte.ordNum
    where coupon.DirectionToMilkRecipeCouponID = cte.DirectionToMilkRecipeCouponID;
End;
$$;