Delimiter $$
/**
 * Author:  armen
 * Created: 30 novembre 2023
 */
# Delimiter $$
Drop Procedure if Exists BUSINESS_FI_ANA_INIT_HISTO_SALES_SUMS $$
Create Procedure BUSINESS_FI_ANA_INIT_HISTO_SALES_SUMS(In idaccounts Int unsigned)
Begin
    bfaihss_main: Begin
        set @bfaihss_idaccounts_min = Coalesce(idaccounts, 0);
        set @bfaihss_idaccounts_max = Coalesce(idaccounts, 1000000);
        -- debug
        -- set @bfaihss_idaccounts_min = 0; set @bfaihss_idaccounts_max = 1000000;

        bfaihss_prepdata: BEGIN
            Drop Temporary Table if Exists TMP_FI_ANA_HISTO_SALES;
            Create Temporary Table TMP_FI_ANA_HISTO_SALES (
                        ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                        , FI_ANA_HISTO_DATE Date Not null
                        , FI_ANA_SALES Decimal(19,4) Not Null Default 0
                        , FI_ANA_UNBOUND_SALES Decimal(19,4) Not Null Default 0

                        , Index (ACCOUNTS_ID_ACCOUNTS, FI_ANA_HISTO_DATE)
#                         , Index (ACCOUNTS_ID_ACCOUNTS)
                        , Index (FI_ANA_HISTO_DATE)
                    )
                ;
            Insert Into TMP_FI_ANA_HISTO_SALES (
                    ACCOUNTS_ID_ACCOUNTS, FI_ANA_HISTO_DATE, FI_ANA_SALES, FI_ANA_UNBOUND_SALES
                )
            Select
                    ACCOUNTS_ID_ACCOUNTS, FI_ANA_HISTO_DATE
                    , Coalesce(FI_ANA_SALES, 0)
                    , Coalesce(FI_ANA_UNBOUND_SALES, 0)
            From FI_ANA_HISTO
            Where ACCOUNTS_ID_ACCOUNTS Between @bfaihss_idaccounts_min And @bfaihss_idaccounts_max
            ;
        end ;
        bfaihss_updt_sales: BEGIN
            Update
                FI_ANA_HISTO tar
            Set
               tar.FI_ANA_SALES_SUM = Coalesce(tar.FI_ANA_SALES, 0)
                       + Coalesce(
                               (
                                   Select Sum(src.FI_ANA_SALES)
                                   From TMP_FI_ANA_HISTO_SALES src
                                   Where tar.ACCOUNTS_ID_ACCOUNTS = src.ACCOUNTS_ID_ACCOUNTS
                                     And tar.FI_ANA_HISTO_DATE < src.FI_ANA_HISTO_DATE
                                   Group By src.ACCOUNTS_ID_ACCOUNTS
                               )
                           , 0
                        )
            Where tar.ACCOUNTS_ID_ACCOUNTS Between @bfaihss_idaccounts_min And @bfaihss_idaccounts_max
            ;
        end ;
        bfaihss_updt_ubsales: BEGIN
            -- explain
            Update
                FI_ANA_HISTO tar
            Set
               tar.FI_ANA_UNBOUND_SALES_SUM = Coalesce(tar.FI_ANA_UNBOUND_SALES, 0)
                       + Coalesce(
                               (
                                   Select Sum(src.FI_ANA_UNBOUND_SALES)
                                   From TMP_FI_ANA_HISTO_SALES src
                                   Where tar.ACCOUNTS_ID_ACCOUNTS = src.ACCOUNTS_ID_ACCOUNTS
                                     And tar.FI_ANA_HISTO_DATE < src.FI_ANA_HISTO_DATE
                                   Group By src.ACCOUNTS_ID_ACCOUNTS
                               )
                           , 0
                        )
            Where tar.ACCOUNTS_ID_ACCOUNTS Between @bfaihss_idaccounts_min And @bfaihss_idaccounts_max
            ;
        end ;
    end ;
End $$
Delimiter ;
