vtorio/prisma/migrations/20250722041246_add_cintas_calculate_summary_procedure/migration.sql

-- Create the cintas_calculate_summary stored procedure
create or replace procedure cintas_calculate_summary(IN provided_import_id bigint)
    language plpgsql
as
$$
BEGIN
    CREATE TEMP TABLE weekSums
    (
        week       int          not null,
        amount     decimal      not null,
        amountType varchar(100) not null
    );

    -- current week
    insert into weekSums (week, amount, amountType)
    select aw.week,
           coalesce(sum(aw.week_sum), 0),
           'trr4wk'
    from cintas_install_calendar i
             inner join lateral (
        select case when i.install_date <> '' then i.install_date::date end,
               case
                   when i.install_date <> '' then date_part('week', i.install_date::date)
                   when i.opportunity_status = 'Pending - Install Not Scheduled' and type = 'FS' then 100 + 22
                   when i.opportunity_status = 'Pending - Install Not Scheduled' and type = 'UR' then 99 + 22
               end,
               case when i.trr <> '' then i.trr::decimal else 0 end,
               case when i.paper_chem_wk1 <> '' then i.paper_chem_wk1::decimal else 0 end,
               case when i.paper_chem_wk2 <> '' then i.paper_chem_wk2::decimal else 0 end,
               case when i.paper_chem_wk3 <> '' then i.paper_chem_wk3::decimal else 0 end,
               case when i.paper_chem_wk4 <> '' then i.paper_chem_wk4::decimal else 0 end,
               case when i.power_add <> '' then i.power_add::decimal else 0 end
        ) w (install_date, week, trr, paper_chem_wk1, paper_chem_wk2, paper_chem_wk3, paper_chem_wk4, power_add) on true
             inner join lateral (
        select case when w.week is null then null when w.week >= 23 then w.week - 22 else w.week + 30 end,
               w.trr + w.paper_chem_wk1
        ) aw (week, week_sum) on true
             inner join lateral (
        select case when aw.week > 1 then aw.week + 1 end,
               case when aw.week > 2 then aw.week + 2 end,
               case when aw.week > 3 then aw.week + 3 end
        ) ow (week_minus_1, week_minus_2, week_minus_3) on true
    where i."importId" = provided_import_id
      and rtrim(opportunity_status) <> ''
      and w.week is not null
    group by aw.week
    ;

    -- week - 1
    insert into weekSums (week, amount, amountType)
    select ow.week_minus_1,
           sum(w.paper_chem_wk2),
           'week-1'
    from cintas_install_calendar i
             inner join lateral (
        select case when i.install_date <> '' then i.install_date::date end,
               case
                   when i.install_date <> '' then date_part('week', i.install_date::date)
                   when i.opportunity_status = 'Pending - Install Not Scheduled' and type = 'FS' then 100 + 22
                   when i.opportunity_status = 'Pending - Install Not Scheduled' and type = 'UR' then 99 + 22
               end,
               case when i.trr <> '' then i.trr::decimal else 0 end,
               case when i.paper_chem_wk1 <> '' then i.paper_chem_wk1::decimal else 0 end,
               case when i.paper_chem_wk2 <> '' then i.paper_chem_wk2::decimal else 0 end,
               case when i.paper_chem_wk3 <> '' then i.paper_chem_wk3::decimal else 0 end,
               case when i.paper_chem_wk4 <> '' then i.paper_chem_wk4::decimal else 0 end,
               case when i.power_add <> '' then i.power_add::decimal else 0 end
        ) w (install_date, week, trr, paper_chem_wk1, paper_chem_wk2, paper_chem_wk3, paper_chem_wk4, power_add) on true
             inner join lateral (
        select case when w.week is null then null when w.week >= 23 then w.week - 22 else w.week + 30 end,
               w.trr + w.paper_chem_wk1
        ) aw (week, week_sum) on true
             inner join lateral (
        select case when aw.week > 1 then aw.week + 1 end,
               case when aw.week > 2 then aw.week + 2 end,
               case when aw.week > 3 then aw.week + 3 end
        ) ow (week_minus_1, week_minus_2, week_minus_3) on true
    where i."importId" = provided_import_id
      and rtrim(opportunity_status) <> ''
      and ow.week_minus_1 is not null
      and aw.week not in (99, 100, 101)
    group by ow.week_minus_1
    ;

    -- week - 2
    insert into weekSums (week, amount, amountType)
    select ow.week_minus_2,
           sum(w.paper_chem_wk3),
           'week-2'
    from cintas_install_calendar i
             inner join lateral (
        select case when i.install_date <> '' then i.install_date::date end,
               case
                   when i.install_date <> '' then date_part('week', i.install_date::date)
                   when i.opportunity_status = 'Pending - Install Not Scheduled' and type = 'FS' then 100 + 22
                   when i.opportunity_status = 'Pending - Install Not Scheduled' and type = 'UR' then 99 + 22
                end,
               case when i.trr <> '' then i.trr::decimal else 0 end,
               case when i.paper_chem_wk1 <> '' then i.paper_chem_wk1::decimal else 0 end,
               case when i.paper_chem_wk2 <> '' then i.paper_chem_wk2::decimal else 0 end,
               case when i.paper_chem_wk3 <> '' then i.paper_chem_wk3::decimal else 0 end,
               case when i.paper_chem_wk4 <> '' then i.paper_chem_wk4::decimal else 0 end,
               case when i.power_add <> '' then i.power_add::decimal else 0 end
        ) w (install_date, week, trr, paper_chem_wk1, paper_chem_wk2, paper_chem_wk3, paper_chem_wk4, power_add) on true
             inner join lateral (
        select case when w.week is null then null when w.week >= 23 then w.week - 22 else w.week + 30 end,
               w.trr + w.paper_chem_wk1
        ) aw (week, week_sum) on true
             inner join lateral (
        select case when aw.week > 1 then aw.week + 1 end,
               case when aw.week > 2 then aw.week + 2 end,
               case when aw.week > 3 then aw.week + 3 end
        ) ow (week_minus_1, week_minus_2, week_minus_3) on true
    where i."importId" = provided_import_id
      and rtrim(opportunity_status) <> ''
      and ow.week_minus_2 is not null
      and aw.week not in (99, 100, 101)
    group by ow.week_minus_2
    ;

    -- week - 3
    insert into weekSums (week, amount, amountType)
    select ow.week_minus_3,
           sum(w.paper_chem_wk4),
           'week-3'
    from cintas_install_calendar i
             inner join lateral (
        select case when i.install_date <> '' then i.install_date::date end,
               case
                   when i.install_date <> '' then date_part('week', i.install_date::date)
                   when i.opportunity_status = 'Pending - Install Not Scheduled' and type = 'FS' then 100 + 22
                   when i.opportunity_status = 'Pending - Install Not Scheduled' and type = 'UR' then 99 + 22
                end,
               case when i.trr <> '' then i.trr::decimal else 0 end,
               case when i.paper_chem_wk1 <> '' then i.paper_chem_wk1::decimal else 0 end,
               case when i.paper_chem_wk2 <> '' then i.paper_chem_wk2::decimal else 0 end,
               case when i.paper_chem_wk3 <> '' then i.paper_chem_wk3::decimal else 0 end,
               case when i.paper_chem_wk4 <> '' then i.paper_chem_wk4::decimal else 0 end,
               case when i.power_add <> '' then i.power_add::decimal else 0 end
        ) w (install_date, week, trr, paper_chem_wk1, paper_chem_wk2, paper_chem_wk3, paper_chem_wk4, power_add) on true
             inner join lateral (
        select case when w.week is null then null when w.week >= 23 then w.week - 22 else w.week + 30 end,
               w.trr + w.paper_chem_wk1
        ) aw (week, week_sum) on true
             inner join lateral (
        select case when aw.week > 1 then aw.week + 1 end,
               case when aw.week > 2 then aw.week + 2 end,
               case when aw.week > 3 then aw.week + 3 end
        ) ow (week_minus_1, week_minus_2, week_minus_3) on true
    where i."importId" = provided_import_id
      and rtrim(opportunity_status) <> ''
      and ow.week_minus_3 is not null
      and aw.week not in (99, 100, 101)
    group by ow.week_minus_3
    ;

    -- power adds
    insert into weekSums (week, amount, amountType)
    select aw.week,
           sum(w.power_add),
           'poweradd'
    from cintas_install_calendar i
             inner join lateral (
        select case when i.install_date <> '' then i.install_date::date end,
               case
                   when i.install_date <> '' then date_part('week', i.install_date::date)
                   when i.opportunity_status = 'Pending - Install Not Scheduled' and type = 'FS' then 100 + 22
                   when i.opportunity_status = 'Pending - Install Not Scheduled' and type = 'UR' then 99 + 22
                end,
               case when i.trr <> '' then i.trr::decimal else 0 end,
               case when i.paper_chem_wk1 <> '' then i.paper_chem_wk1::decimal else 0 end,
               case when i.paper_chem_wk2 <> '' then i.paper_chem_wk2::decimal else 0 end,
               case when i.paper_chem_wk3 <> '' then i.paper_chem_wk3::decimal else 0 end,
               case when i.paper_chem_wk4 <> '' then i.paper_chem_wk4::decimal else 0 end,
               case when i.power_add <> '' then i.power_add::decimal else 0 end
        ) w (install_date, week, trr, paper_chem_wk1, paper_chem_wk2, paper_chem_wk3, paper_chem_wk4, power_add) on true
             inner join lateral (
        select case when w.week is null then null when w.week >= 23 then w.week - 22 else w.week + 30 end,
               w.trr + w.paper_chem_wk1
        ) aw (week, week_sum) on true
             inner join lateral (
        select case when aw.week > 1 then aw.week + 1 end,
               case when aw.week > 2 then aw.week + 2 end,
               case when aw.week > 3 then aw.week + 3 end
        ) ow (week_minus_1, week_minus_2, week_minus_3) on true
    where i."importId" = provided_import_id
      and rtrim(opportunity_status) <> ''
      and aw.week is not null
      and aw.week not in (99, 100, 101)
    group by aw.week
    ;
    
    -- pending totals
    insert into weekSums (week, amount, amountType)
    select aw.week,
           sum(w.power_add),
           'poweradd'
    from cintas_install_calendar i
             inner join lateral (
        select case when i.install_date <> '' then i.install_date::date end,
               case
                   when i.install_date <> '' then date_part('week', i.install_date::date)
                   when i.opportunity_status = 'Pending - Install Not Scheduled' and type = 'FS' then 100 + 22
                   when i.opportunity_status = 'Pending - Install Not Scheduled' and type = 'UR' then 99 + 22
                end,
               case when i.trr <> '' then i.trr::decimal else 0 end,
               case when i.paper_chem_wk1 <> '' then i.paper_chem_wk1::decimal else 0 end,
               case when i.paper_chem_wk2 <> '' then i.paper_chem_wk2::decimal else 0 end,
               case when i.paper_chem_wk3 <> '' then i.paper_chem_wk3::decimal else 0 end,
               case when i.paper_chem_wk4 <> '' then i.paper_chem_wk4::decimal else 0 end,
               case when i.power_add <> '' then i.power_add::decimal else 0 end
        ) w (install_date, week, trr, paper_chem_wk1, paper_chem_wk2, paper_chem_wk3, paper_chem_wk4, power_add) on true
             inner join lateral (
        select case when w.week is null then null when w.week >= 23 then w.week - 22 else w.week + 30 end,
               w.trr + w.paper_chem_wk1
        ) aw (week, week_sum) on true
             inner join lateral (
        select case when aw.week > 1 then aw.week + 1 end,
               case when aw.week > 2 then aw.week + 2 end,
               case when aw.week > 3 then aw.week + 3 end
        ) ow (week_minus_1, week_minus_2, week_minus_3) on true
    where i."importId" = provided_import_id
      and rtrim(opportunity_status) <> ''
      and aw.week is not null
      and aw.week not in (99, 100, 101)
    group by aw.week
    ;

    insert into cintas_intall_calendar_summary ("importId", week, "trrTotal", "fourWkAverages", "trrPlus4Wk", "powerAdds", "weekId", "createdAt", "updatedAt")
    select distinct
        provided_import_id,
        case when t.week = 100 then 'Pending - FS'
            when t.week = 99 then 'Pending - UR'
            else concat('Week ', t.week::varchar(100))
        end "Week",
        cast(sum(case when t.amountType <> 'poweradd' and t.amountType = 'trr4wk' then t.amount else 0 end) over (partition by t.week) as decimal(9,0)) "TRR Total",
        cast(sum(case when t.amountType <> 'poweradd' and t.amountType <> 'trr4wk' then t.amount else 0 end) over (partition by t.week) as decimal(9,0)) "4WK Averages",
        cast(sum(case when t.amountType <> 'poweradd' then t.amount else 0 end) over (partition by t.week) as decimal(9,0)) "TRR + 4WK",
        cast(sum(case when t.amountType = 'poweradd' then t.amount else 0 end) over (partition by t.week) as decimal(9,0)) "Power Adds",
        t.week "WeekId",
        now(),
        now()
    from weekSums t
    ;
    
    insert into cintas_intall_calendar_summary ("importId", week, "trrTotal", "fourWkAverages", "trrPlus4Wk", "powerAdds", "weekId", "createdAt", "updatedAt")
    select provided_import_id,
        'Pending - Total' "Week",
        sum(s."trrTotal") "TRR Total",
        sum(s."fourWkAverages") "4WK Averages",
        sum(s."trrPlus4Wk") "TRR + 4WK",
        sum(s."powerAdds") "Power Adds",
        101 "weekId",
        now(),
        now()
    from cintas_intall_calendar_summary s
    where "importId" = provided_import_id
    and "weekId" in (99, 100)
    ;

    drop table weekSums;
END
$$;