Delimiter $$
/**
 * Author:  armen
 */

Drop Procedure if Exists BUSINESS_CREDITSPROVIDERS_KEEP_1_UNEXPIRED_BY_REF $$
Create Procedure BUSINESS_CREDITSPROVIDERS_KEEP_1_UNEXPIRED_BY_REF (
    In account Int Unsigned
    , In entity Int Unsigned 
    , In creditProviderRef Varchar(36)
    , In useTransaction smallInt 
)
Begin
    bcpk1eby_main: begin
        bcpk1eby_init: begin
            Set @bcpk1ubr_account_min = Coalesce(account, 1);
            Set @bcpk1ubr_account_max = Coalesce(account, 1e6);

            Set @bcpk1ubr_entity_min = Coalesce(entity, 1);
            Set @bcpk1ubr_entity_max = Coalesce(entity, 1e5);

            Set @bcpk1ubr_creditProviderRef = Coalesce(NullIf(Trim(creditProviderRef), ''), '%');


            Set @bcpk1ubr_today = curdate();
            Set @bcpk1ubr_expiresDate = Date_sub(curdate(), Interval 1 day);

            Drop Temporary table if exists TMP_CREDITSPROVIDERS_KEEP_1_UNEXPIRED_BY_REF;
            Create Temporary table TMP_CREDITSPROVIDERS_KEEP_1_UNEXPIRED_BY_REF (
                        CREDITS_PROVIDERS_ID_CREDITS_PROVIDERS Int Unsigned Not Null
                        , ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                        , CREDITS_PROVIDERS_REF Varchar(36) 
                        , Index (CREDITS_PROVIDERS_ID_CREDITS_PROVIDERS)
                        , Index (ACCOUNTS_ID_ACCOUNTS)
                        , Index (CREDITS_PROVIDERS_REF)
                    )
                ;
        end;

        bcpk1eby_transaction_mng: begin
            Declare bcpk1eby_transactionStarted int default 0 ;
            DECLARE continue HANDLER FOR SQLWARNING
            Begin
                resignal;
            end ;

            DECLARE Exit HANDLER FOR SQLEXCEPTION
            Begin
                -- set errcount = errcount + 1000;
                if bcpk1eby_transactionStarted  <> 0 then
                    rollback;
                    set bcpk1eby_transactionStarted  = 0;
                End if;
                resignal;
            end ;

            if Coalesce(useTransaction, 0) <> 0 Then
                Start transaction;
                Set bcpk1eby_transactionStarted = 1;
            End if;

            bcpk1eby_find_data_to_keep: begin
            
                -- dans l'absolue, il faudrait selectionner les données à conserver en fonction 
                -- du duchamp CREDITS_PROVIDERS_UPDATE_DATE
                -- pour des raisons d'optimisation et de simplification on va utiliser 
                -- le max ID_CREDITS_PROVIDERS des non expirés
                -- du fait des traitements dans l'import l'application, 
                -- le risque d'eccart est faible par rapport une recherche sur max CREDITS_PROVIDERS_UPDATE_DATE

                Insert Into TMP_CREDITSPROVIDERS_KEEP_1_UNEXPIRED_BY_REF (
                         ACCOUNTS_ID_ACCOUNTS
                        , CREDITS_PROVIDERS_REF
                        , CREDITS_PROVIDERS_ID_CREDITS_PROVIDERS
                    )
                Select 
                    acc1.ID_ACCOUNTS, cp1.CREDITS_PROVIDERS_REF
                    , max(cp1.ID_CREDITS_PROVIDERS)
                From CREDITS_PROVIDERS cp1
                    Inner Join ACCOUNTS acc1
                        On cp1.ACCOUNTS_ID_ACCOUNTS = acc1.ID_ACCOUNTS
                Where acc1.ID_ACCOUNTS between @bcpk1ubr_account_min and @bcpk1ubr_account_max
                    And acc1.ENTITIES_ID_ENTITIES between @bcpk1ubr_entity_min and @bcpk1ubr_entity_max
                    And cp1.CREDITS_PROVIDERS_REF like @bcpk1ubr_creditProviderRef
                    And (
                        cp1.CREDITS_PROVIDERS_VAL_DATE >= @bcpk1ubr_today
                        or cp1.CREDITS_PROVIDERS_VAL_DATE Is null
                        -- detection d'annomlies dans les données de type date
                        or cp1.CREDITS_PROVIDERS_VAL_DATE = '0000-00-00'

                    )
                Group By acc1.ID_ACCOUNTS, cp1.CREDITS_PROVIDERS_REF
                ;

            end;
            bcpk1eby_update_data: begin
                Update CREDITS_PROVIDERS cp1
                    Inner Join ACCOUNTS acc1
                        On cp1.ACCOUNTS_ID_ACCOUNTS = acc1.ID_ACCOUNTS
                Set cp1.CREDITS_PROVIDERS_VAL_DATE = @bcpk1ubr_expiresDate
                Where 
                    acc1.ID_ACCOUNTS between @bcpk1ubr_account_min and @bcpk1ubr_account_max
                    And acc1.ENTITIES_ID_ENTITIES between @bcpk1ubr_entity_min and @bcpk1ubr_entity_max
                    And cp1.CREDITS_PROVIDERS_REF like @bcpk1ubr_creditProviderRef
                    And (
                        cp1.CREDITS_PROVIDERS_VAL_DATE >= @bcpk1ubr_today
                        or cp1.CREDITS_PROVIDERS_VAL_DATE Is null
                        -- detection d'annomlies dans les données de type date
                        or cp1.CREDITS_PROVIDERS_VAL_DATE = '0000-00-00'
                    )
                    And cp1.ID_CREDITS_PROVIDERS Not In (
                        Select 
                            src.CREDITS_PROVIDERS_ID_CREDITS_PROVIDERS
                        From TMP_CREDITSPROVIDERS_KEEP_1_UNEXPIRED_BY_REF src
                        Where 
                            acc1.ID_ACCOUNTS = src.ACCOUNTS_ID_ACCOUNTS
                            And cp1.CREDITS_PROVIDERS_REF = src.CREDITS_PROVIDERS_REF
                    )
                ;
            end ;

            if bcpk1eby_transactionStarted <> 0 Then
                commit;
                Set bcpk1eby_transactionStarted = 0;
            End if;

        end;
    end;
End $$
Delimiter ;

