/**
*
* Maintains the provider's payment info needed for subsequent recurring billings for a given
* storage and plastic instrument record that may be needed for each subsequent billing that
* is charging the same plastic instrument (credit card).
*
*/
create procedure [dbo].[SaveRecurringPaymentInfoForStorage]
    @inStorageId int,
    @inInstrumentId int,
    @inProviderRecurringTransactionId nvarchar(64)
as
set nocount on;

declare @theRowsUpdated int
declare @theCurrentUtcDate datetime = getutcdate()

-- One record per StorageFID and InstrumentFID combination.
insert into StoragePaymentTransactionTracking
(
    StorageFID,
    InstrumentFID,
    FirstTransactionID,
    FirstTransactionUtcDate,
    LastTransactionID,
    LastTransactionUtcDate
)
select
    StorageFID = @inStorageId,
    InstrumentFID = @inInstrumentId,
    FirstTransactionID = @inProviderRecurringTransactionId,
    FirstTransactionUtcDate = @theCurrentUtcDate,
    LastTransactionID = @inProviderRecurringTransactionId,
    LastTransactionUtcDate = @theCurrentUtcDate
where not exists(
    select top 1 1
    from StoragePaymentTransactionTracking
    where StorageFID = @inStorageId and
        InstrumentFID = @inInstrumentId
)

set @theRowsUpdated = @@rowcount

if( @theRowsUpdated = 0 )
begin
    -- Record for this StorageFID and InstrumentFID exists so update
    -- just the last transaction info.
    update StoragePaymentTransactionTracking set
        LastTransactionID = @inProviderRecurringTransactionId,
        LastTransactionUtcDate = @theCurrentUtcDate
    where StorageFID = @inStorageId and
        InstrumentFID = @inInstrumentId
end
GO
GRANT EXECUTE ON  [dbo].[SaveRecurringPaymentInfoForStorage] TO [MssExec]
GO