/**
*
* Gets a list of all storage items the are currently in an open billing state that use
* our Plastic payment system for a specific electronic merchant.  Used to convert the
* payment tokens from one provider to another.  @inProviderId can be null which means
* to pull all the tokens from any provider that is of type @inProviderTypeId (which must
* be specified).
*
*/
create procedure [dbo].[TransferOpenStoragePaymentMethodToNewProvider]
    @inOldInstrumentId int,
    @inStoragePriKey int,
    @inOldProviderId int,
    @inOldProviderTypeCode varchar(4),
    @inNewProviderId int,
    @inNewEncryptedToken varchar(1000),
    @inNewEncryptedHolderName varchar(1000),
    @inNewEncryptedBillingAddress varchar(1000),
    @inNewEncryptedBillingAddress2 varchar(1000),
    @inNewEncryptedBillingCity varchar(1000),
    @inNewEncryptedBillingState varchar(255),
    @inNewEncryptedBillingPostalCode varchar(255),
    @inNewEncryptedBillingCountry varchar(255),
    @inNewEncryptedBillingAccessory varchar(1000),
    @inNewExpirationDate datetime,
    @outSuccessFlag bit output
as
set nocount on;
set @outSuccessFlag = 0

declare @theOldTokenTypeCode varchar(2)
declare @theNewProviderTypeId int
declare @theNewTokenTypeExternalCode varchar(32)
declare @theNewTokenTypeId int

declare @inNewInstrumentId int
declare @theNewInstrumentInsertCount int

-- This could be quick and dirty if the same PlasticInstrument
-- was previously converted into a new token for the new provider.
select
    @inNewInstrumentId = NewInstrument.InstrumentID
from PlasticInstrument as OldInstrument
inner join PlasticInstrument as NewInstrument on NewInstrument.InstrumentID = OldInstrument.ConversionFID
where OldInstrument.InstrumentID = @inOldInstrumentId and
    NewInstrument.ProviderFID = @inNewProviderId

if( @inNewInstrumentId is not null )
begin
    -- Update the storage record to use the new instrument.
    update Storage set
        InstrumentFID = @inNewInstrumentId
    where Storage.StoragePriKey = @inStoragePriKey

    -- All done!
    set @outSuccessFlag = 1
    return
end

-- We should always get these two since PlasticInstrument.TokenTypeFID is not null
select
    @theOldTokenTypeCode = OldTokenType.Code,
    @theNewProviderTypeId = NewProvider.ProviderTypeFID
from PlasticInstrument as OldInstrument
inner join PlasticProvider as NewProvider on NewProvider.ProviderID = @inNewProviderId
inner join PlasticTokenType as OldTokenType on OldTokenType.TokenTypeID = OldInstrument.TokenTypeFID
where OldInstrument.InstrumentID = @inOldInstrumentId

-- Custom logic will always be needed for this...
if( @inOldProviderTypeCode = 'CSIP' )
begin
    -- Logic for Csi-Pay token types converting to Pyxis token types
    if( @theOldTokenTypeCode is not null )
    begin
        set @theNewTokenTypeExternalCode = case @theOldTokenTypeCode
            when 'AC' then 'Amex'
            when 'A0' then 'Amex'
            when 'BC' then 'Checking'
            when 'VE' then 'Checking'
            when 'BS' then 'Savings'
            when 'VV' then 'Savings'
            when 'DC' then 'Discover'
            when 'DD' then 'Discover'
            when 'NC' then 'DinersClub'
            when 'D1' then 'DinersClub'
            when 'BD' then 'DebitCard'
            when 'D2' then 'DebitCard'
            when '10' then 'JCB'
            when 'J0' then 'JCB'
            when 'MC' then 'MasterCard'
            when 'MD' then 'MasterCard'
            when 'MP' then 'MasterCard'
            when 'MF' then 'MasterCard'
            when 'M0' then 'MasterCard'
            when 'VC' then 'Visa'
            when 'VD' then 'Visa'
            when 'VP' then 'Visa'
            when 'V0' then 'Visa'
            else null    -- For any others, Keep the TokenType as is
        end

        if( @theNewTokenTypeExternalCode is not null )
        begin
            select
                @theNewTokenTypeId = PlasticTokenTypeGatewayMap.TokenTypeFID
            from PlasticTokenTypeGatewayMap
            where PlasticTokenTypeGatewayMap.ProviderTypeFID = @theNewProviderTypeId and
                PlasticTokenTypeGatewayMap.ExternalCode = @theNewTokenTypeExternalCode
        end
    end
end

-- Create a new instrument using the data from the old instrument
-- but setting it to point to the new provider with the new token.
-- We only do this if the old instrument is still the one on the
-- Storage record.
insert into PlasticInstrument
(
    ProviderFID,
    HolderName,
    BillingAddress,
    BillingAddress2,
    BillingCity,
    BillingState,
    BillingPostalCode,
    Token,
    Accessory,
    LastFour,
    TokenTypeFID,
    CustomerNumber,
    OrderFID,
    Inactive,
    FirstUsedDate,
    LastUsedDate,
    NickName,
    OnlineOkForFutureInv,
    OnlineOkDate,
    HolderLastName,
    BillingCountry,
    GatewayCustomerID,
    ExpirationDate
)
select
    ProviderFID = @inNewProviderId,
    HolderName = @inNewEncryptedHolderName,
    BillingAddress = @inNewEncryptedBillingAddress,
    BillingAddress2 =@inNewEncryptedBillingAddress2,
    BillingCity = @inNewEncryptedBillingCity,
    BillingState = @inNewEncryptedBillingState,
    BillingPostalCode = @inNewEncryptedBillingPostalCode,
    Token = @inNewEncryptedToken,
    Accessory = @inNewEncryptedBillingAccessory,
    LastFour = OldInstrument.LastFour,
    TokenTypeFID = isnull( @theNewTokenTypeId, OldInstrument.TokenTypeFID ),
    CustomerNumber = OldInstrument.CustomerNumber,
    OrderFID = OldInstrument.OrderFID,
    Inactive = OldInstrument.Inactive,
    FirstUsedDate = OldInstrument.FirstUsedDate,
    LastUsedDate = OldInstrument.LastUsedDate,
    NickName = OldInstrument.NickName,
    OnlineOkForFutureInv = OldInstrument.OnlineOkForFutureInv,
    OnlineOkDate = OldInstrument.OnlineOkDate,
    HolderLastName = OldInstrument.HolderLastName,
    BillingCountry = @inNewEncryptedBillingCountry,
    GatewayCustomerID = OldInstrument.GatewayCustomerID,
    ExpirationDate = @inNewExpirationDate
from PlasticInstrument as OldInstrument
inner join Storage on Storage.StoragePriKey = @inStoragePriKey and
    Storage.InstrumentFID = @inOldInstrumentId
inner join PlasticProvider as NewProvider on NewProvider.ProviderID = @inNewProviderId
where OldInstrument.InstrumentID = @inOldInstrumentId

set @theNewInstrumentInsertCount = @@rowcount

if( @theNewInstrumentInsertCount = 1 )
begin
    set @inNewInstrumentId = scope_identity()

    -- Update the storage record to use the new instrument.
    update Storage set
        InstrumentFID = @inNewInstrumentId
    where Storage.StoragePriKey = @inStoragePriKey

    -- Mark the old instrument as now being inactive and record
    -- what the new instrument is that it was converted into.
    update OldInstrument set
        Inactive = 1,
        ConversionFID = @inNewInstrumentId
    from PlasticInstrument as OldInstrument
    where OldInstrument.InstrumentID = @inOldInstrumentId

    set @outSuccessFlag = 1
end
GO
GRANT EXECUTE ON  [dbo].[TransferOpenStoragePaymentMethodToNewProvider] TO [MssExec]
GO