create procedure [dbo].[MssWebUpdateOrderGuaranteePriceInfo]
    @inOrderId int,
    @inRateUpdates [MssWebGuaranteePriceRateUpdate] readonly,
    @inCharge money = null,
    @inCredit money = null
as
begin
    set nocount on;

    -- Resolve input rate updates to MaterialItemCodeMapID by joining MaterialCPUType and MaterialItemCodeMap
    -- Only valid combinations (where both MaterialCPUType and MaterialItemCodeMap exist) are processed
    ;with RateChanges as
    (
        select
            OrderId = @inOrderId,
            MaterialItemCodeMapID = MaterialItemCodeMap.MaterialItemCodeMapID,
            NewRate = InputRates.Rate
        from @inRateUpdates as InputRates
        inner join MaterialCPUType as MaterialCpuType
            on MaterialCpuType.[Type] = InputRates.RateTypeName
        inner join MaterialItemCodeMap as MaterialItemCodeMap
            on MaterialItemCodeMap.MaterialTypeFID = InputRates.MaterialTypeId
            and MaterialItemCodeMap.MaterialCPUTypeFID = MaterialCpuType.MaterialCPUTypeID
        where exists (select top 1 1 from Orders where PriKey = @inOrderId)
    )

    -- Merge rate updates into GuaranteedPriceCPU table
    -- Insert new rates > 0, update existing rates > 0, delete rates <= 0
    merge GuaranteedPriceCPU with (tablock) as target
    using RateChanges as source
        on target.OrderFID = source.OrderId
        and target.MaterialItemCodeMapFID = source.MaterialItemCodeMapID

    when not matched and source.NewRate > 0 then insert
    (
        OrderFID,
        MaterialItemCodeMapFID,
        Rate
    )
    values
    (
        source.OrderId,
        source.MaterialItemCodeMapID,
        source.NewRate
    )

    when matched and source.NewRate <= 0 then delete

    when matched and source.NewRate > 0 then update set
        Rate = source.NewRate;

    -- Handle Charge and Credit updates
    if @inCharge is not null or @inCredit is not null
    begin
        merge GuaranteedPrice with (tablock) as target
        using (select @inOrderId as OrderFID) as source
            on target.OrderFID = source.OrderFID
        
        when matched then update set
            Charge = isnull(@inCharge, Charge),
            Credit = isnull(@inCredit, Credit)
        
        when not matched then insert
            (OrderFID, Charge, Credit)
        values
            (source.OrderFID, @inCharge, @inCredit);
    end

    -- Ensure GuaranteedPrice record exists when there are CPU items
    if not exists (select * from GuaranteedPrice where OrderFID = @inOrderId)
       and exists (select * from GuaranteedPriceCPU where OrderFID = @inOrderId)
    begin
        insert into GuaranteedPrice (OrderFID)
        select @inOrderId;
    end
end
GO
GRANT EXECUTE ON  [dbo].[MssWebUpdateOrderGuaranteePriceInfo] TO [MssExec]
GO