-- CREATING A TEMPORAL TABLE WHERE WE APPLY THE GLOBLY METHODOLOGY OF REMOVING INTERNAL TRANSFERS
CREATE TABLE temp.temp_Flows (
    "Fiscal year period" varchar(20),
    "Company Code" varchar(5),
    "Account" varchar(20),
    "Generic Material" varchar(8),
    "Issue" float,
    "Transfers In" float,
    "Transfers Out" float,
    "Return" float
);
Without leading zero’s
“-- Insert into temp_Flows
INSERT INTO temp.temp_Flows
SELECT DISTINCT
    TO_CHAR(TO_DATE("Fiscal year period", 'MM.YYYY'), 'FMmm.YYYY') AS "Fiscal year period",
    "Company Code",
    "Account",
    "Generic Material",
    COALESCE(SUM(CASE WHEN "Metric" IN ('s (in)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Issue",
    COALESCE(SUM(CASE WHEN "Metric" IN ('tDD (in)', 'tDE (in)', 'tED (in)', 'tEE (in)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Transfers In",
    COALESCE(SUM(CASE WHEN "Metric" IN ('tDD (Out)', 'tDE (Out)', 'tED (Out)', 'tEE (Out)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Transfers Out",
    COALESCE(SUM(CASE WHEN "Metric" IN ('r (out)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Return"
FROM (
    SELECT
        "Fiscal year period",
        "Company Code",
        "Contra Account",
        "Account",
        "Generic Material",
        "Metric",
        COALESCE("Metric Value", 0) AS "Metric Value"
    FROM model."IMETA_Global_Field_Flows_Full_dataset_(Client)_TA_BW"
    -- Applying the Global Methodology for Transfers
    WHERE "Account" <> "Contra Account"
        AND "Metric" IN ('tDD (Out)', 'tDE (Out)', 'tEE (Out)', 'tED (Out)', 'tDD (in)', 'tDE (in)', 'tEE (in)', 'tED (in)')
    -- Bringing in other data excluding the filtering of inta and contra movements/internal flows
    UNION ALL
    SELECT
        "Fiscal year period",
        "Company Code",
        "Contra Account",
        "Account",
        "Generic Material",
        "Metric",
        COALESCE("Metric Value", 0) AS "Metric Value"
    FROM model."IMETA_Global_Field_Flows_Full_dataset_(Client)_TA_BW"
    WHERE "Metric" IN ('r (out)', 's (in)', 'r (star) (in)', 'rDP (in)', 'rEP (in)', 'sPE (out)', 'sPD (out)', 'i (star) out (q+dDE (star)-out)', 'y (Based on P.Ord.)', 'x', 'z', 'q (in) - (s (in))')
) AS Table_1
WHERE "Company Code" IN ('AE10', 'AE20', 'SA10');”


-- Insert into temp_Flows
INSERT INTO temp.temp_Flows
SELECT DISTINCT
"Fiscal year period",
"Company Code",
"Account",
"Generic Material",
COALESCE(SUM(CASE WHEN "Metric" IN ('s (in)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Issue",
COALESCE(SUM(CASE WHEN "Metric" IN ('tDD (in)', 'tDE (in)', 'tED (in)', 'tEE (in)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Transfers In",
COALESCE(SUM(CASE WHEN "Metric" IN ('tDD (Out)', 'tDE (Out)', 'tED (Out)', 'tEE (Out)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Transfers Out",
COALESCE(SUM(CASE WHEN "Metric" IN ('r (out)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Return"
FROM (
SELECT
"Fiscal year period",
"Company Code",
"Contra Account",
"Account",
"Generic Material",
"Metric",
COALESCE("Metric Value", 0) AS "Metric Value"
FROM model."IMETA_Global_Field_Flows_Full_dataset_(Client)_TA_BW"
-- Applying the Global Methodology for Transfers
WHERE "Account" <> "Contra Account"
AND "Metric" IN ('tDD (Out)', 'tDE (Out)', 'tEE (Out)', 'tED (Out)', 'tDD (in)', 'tDE (in)', 'tEE (in)', 'tED (in)')
-- Bringing in other data excluding the filtering of inta and contra movements/internal flows
UNION ALL
SELECT
"Fiscal year period",
"Company Code",
"Contra Account",
"Account",
"Generic Material",
"Metric",
COALESCE("Metric Value", 0) AS "Metric Value"
FROM model."IMETA_Global_Field_Flows_Full_dataset_(Client)_TA_BW"
WHERE "Metric" IN ('r (out)', 's (in)', 'r (star) (in)', 'rDP (in)', 'rEP (in)', 'sPE (out)',
'sPD (out)', 'i (star) out (q+dDE (star)-out)', 'y (Based on P.Ord.)', 'x', 'z', 'q (in) - (s (in))')
) AS Table_1
WHERE "Company Code" IN ('AE10', 'AE20', 'SA10');

-------


--CREATE  TABLE "temp.temp_FlowsFact" (
    "Fiscal_year_period" varchar(20),
    "Calendar_day" DATE,
    "Financial_year" varchar(5),
    "Period" varchar(8),
    "Account" varchar(20),
    --"Generic_Material" varchar(8),
    "Issue" float,
    "Transfers_In" float,
    "Transfers_Out" float,
    "Return" float
);

-- Table: temp.temp_flowsfact

-- DROP TABLE IF EXISTS temp.temp_flowsfact;

CREATE TABLE IF NOT EXISTS temp.temp_flowsfact
(
    "Fiscal year period" character varying(20) COLLATE pg_catalog."default",
    "Calendar_day" date,
    "Financial year" character varying(5) COLLATE pg_catalog."default",
    "Period" character varying(8) COLLATE pg_catalog."default",
    "Account" character varying(20) COLLATE pg_catalog."default",
    "Issue" double precision,
    "Transfers In" double precision,
    "Transfers Out" double precision,
    "Return" double precision
);
INSERT INTO temp.temp_flowsfact
SELECT * FROM (
    SELECT DISTINCT
        FLOWS."Fiscal year period",
        Calendar_Table."last day of period",
        Calendar_Table."financial year",
        Calendar_Table."period",
        FLOWS."Account",
        ISSUE.issues,
        TRANS_IN."Transfers In",
        TRANS_OUT."Transfers Out",
        RETURNS."return"
    FROM
        temp.temp_flows AS FLOWS
    -- Bringing in Issue fact
    LEFT OUTER JOIN (
        SELECT "Account", "Fiscal year period", COALESCE(SUM("Issue"), 0) AS issues
        FROM temp.temp_flows AS "issues"
        GROUP BY "Fiscal year period", "Account"
    ) AS ISSUE ON FLOWS."Account" = ISSUE."Account" AND FLOWS."Fiscal year period" = ISSUE."Fiscal year period"
    -- Bringing in Transfers In fact
    LEFT OUTER JOIN (
        SELECT "Account", "Fiscal year period", COALESCE(SUM("Transfers In"), 0) AS "Transfers In"
        FROM temp.temp_flows AS "trans_in"
        GROUP BY "Fiscal year period", "Account"
    ) AS TRANS_IN ON FLOWS."Account" = TRANS_IN."Account" AND FLOWS."Fiscal year period" = TRANS_IN."Fiscal year period"
    -- Bringing in Transfers out fact
    LEFT OUTER JOIN (
        SELECT "Account", "Fiscal year period", COALESCE(SUM("Transfers Out"), 0) AS "Transfers Out"
        FROM temp.temp_flows AS "trans_out"
        GROUP BY "Fiscal year period", "Account"
    ) AS TRANS_OUT ON FLOWS."Account" = TRANS_OUT."Account" AND FLOWS."Fiscal year period" = TRANS_OUT."Fiscal year period"
    -- Bringing in return fact
    LEFT OUTER JOIN (
        SELECT "Account", "Fiscal year period", COALESCE(SUM("Return"), 0) AS "return"
        FROM temp.temp_flows AS "returns"
        GROUP BY "Fiscal year period", "Account"
    ) AS RETURNS ON FLOWS."Account" = RETURNS."Account" AND FLOWS."Fiscal year period" = RETURNS."Fiscal year period"
    -- Bringing in last day of reporting Date for ease of reporting and calculations of rolling numbers
    LEFT OUTER JOIN (
        SELECT 
            CAST("date" AS DATE) AS "date",
            CONCAT('20', SUBSTRING("fy", 3, 2)) AS "financial year",
            "period",
            FIRST_VALUE("date") OVER (PARTITION BY "fy", "period" ORDER BY "date") AS "first day of period",
            FIRST_VALUE("date") OVER (PARTITION BY "fy", "period" ORDER BY "date" DESC) AS "last day of period",
            (DATE_PART('day', FIRST_VALUE("date") OVER (PARTITION BY "fy", "period" ORDER BY "date" DESC)) - DATE_PART('day', FIRST_VALUE("date") OVER (PARTITION BY "fy", "period" ORDER BY "date"))) + 1 AS "number of days"
        FROM 
            prod."IMETA - Calendar Days Data_View_Other"
    ) AS Calendar_Table ON LEFT(FLOWS."Fiscal year period", 3) = CONCAT('0', REPLACE(Calendar_Table."period", 'P', '')) AND RIGHT(FLOWS."Fiscal year period", 4) = Calendar_Table."financial year"
) AS FlowsFact;

INSERT INTO temp.temp_FieldFlowsFact
SELECT
    "Account",
    "Calendar_day",
    "Financial_year",
    "Period",
    CASE
        WHEN "Metric" like '%R12M%' THEN 'R12M'
        WHEN "Metric" like '%R6M%' THEN 'R6M'
        WHEN "Metric" like '%R3M%' THEN 'R3M'
        ELSE 'Periodic'
    END AS "Period type",
    "Metric",
    "FTR%" AS "Metric Value"
FROM
(
    -- Calculating Rolling FTRs
    SELECT
        "Account",
        "Calendar_day",
        "Financial_year",
        "Period",
        "Issue",
        SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Issues",
        SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Issues",
        SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Issues",
        "Transfers_In",
        SUM("Transfers_In") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Transfers In",
        SUM("Transfers_In") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers In",
        SUM("Transfers_In") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers In",
        "Transfers_Out",
        SUM("Transfers_Out") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Transfers Out",
        SUM("Transfers_Out") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers Out",
        SUM("Transfers_Out") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers Out",
        "Return",
        SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Return",
        SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Return",
        SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Return",
        -- Prevent division by zero
        CASE
            WHEN ("Issue" + "Transfers_In") = 0 THEN 0
            ELSE ROUND((("Transfers_Out" + "Return") / ("Issue" + "Transfers_In")::NUMERIC) * 100)::NUMERIC(10,2)
        END AS "FTR%",
        NULL AS "Metric" -- Replace NULL with the appropriate calculation or column reference
    FROM temp.temp_flowsfact
) AS FACT;
INSERT INTO temp.temp_FieldFlowsFact_with_Channels
SELECT 
    "Account", 
    "Financial year", 
    "Period",
    CASE 
        WHEN "Metric" LIKE '%R12M%' THEN 'R12M'
        WHEN "Metric" LIKE '%R6M%' THEN 'R6M'
        WHEN "Metric" LIKE '%R3M%' THEN 'R3M'
        ELSE 'Periodic'
    END AS "Period type",
    CASE 
        WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) BETWEEN 98 AND 100 THEN '98 - 100'
        WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) BETWEEN 90 AND 97 THEN '90 - 97'
        WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) BETWEEN 70 AND 89 THEN '70 - 89'
        WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) BETWEEN 51 AND 69 THEN '51 - 69'
        WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) BETWEEN 0 AND 50 THEN 'Below 50'
        WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) > 100 THEN 'Above 100'
        WHEN "Metric" IS NULL THEN 'Uncategorised'  -- Include null values in the "Metric" column
        ELSE 'Uncategorised'
    END AS "Pricing channel",
    CASE 
        WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 98 AND 100 THEN 'E'
        WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 90 AND 97 THEN 'D'
        WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 70 AND 89 THEN 'C'
        WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 51 AND 69 THEN 'B'
        WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 0 AND 50 THEN 'A'
        WHEN ROUND("Metric Value"::numeric, 0) > 100 THEN 'F'
        WHEN "Metric" IS NULL THEN 'Uncategorised'  -- Include null values in the "Metric" column
        ELSE 'Uncategorised'
    END AS "Pricing Category"
FROM temp.temp_fieldflowsfact
WHERE "Metric" LIKE '%FTR%' OR "Metric" IS NULL  -- Include rows with null values in the "Metric" column
ORDER BY "Calendar day";
INSERT INTO temp.temp_FinalTable
SELECT DISTINCT
    FlowsFact."Account",
    Customer."Account Name",
    Customer."Level 4 Name" AS "Reporting Parent",
    Customer."Level 3 Name" AS "CRM Parent",
    FlowsFact."Financial year",
    FlowsFact."Period",
    FlowsFact."Period type",
    FlowsFact."Metric",
    FlowsFact."Metric Value",
    CHANNEL."Pricing channel",
    CHANNEL."Pricing Category",
    CASE
        WHEN CHANNEL."Pricing Category" = 'E' AND FlowsFact."Metric" = 'R12M FTR%' THEN '-'
        WHEN CHANNEL."Pricing Category" = 'D' AND FlowsFact."Metric" = 'R12M FTR%' THEN '1.87'
        WHEN CHANNEL."Pricing Category" = 'C' AND FlowsFact."Metric" = 'R12M FTR%' THEN '5.13'
        WHEN CHANNEL."Pricing Category" = 'B' AND FlowsFact."Metric" = 'R12M FTR%' THEN '10.4'
        WHEN CHANNEL."Pricing Category" = 'A' AND FlowsFact."Metric" = 'R12M FTR%' THEN '21.79'
        ELSE '' -- This may change depending on future reporting requirements
    END AS "Weighted Avg Rate"
FROM
    temp.temp_fieldflowsfact AS FlowsFact
    INNER JOIN (
        SELECT
            account,
            "Account Name",
            "Level 4 Name",
            "Level 3 Name"
        FROM
            prod."imeta - customer daily issues and returns analysis_model_sap bw"
        WHERE
            "Company Code" IN ('AE10', 'AE20', 'SA10')
    ) AS Customer ON FlowsFact."Account" = Customer.account
    INNER JOIN temp.temp_fieldflowsfact_with_channels AS CHANNEL ON CHANNEL."Account" = FlowsFact."Account"
        AND CHANNEL."Financial year" = FlowsFact."Financial year"
        AND CHANNEL."Period" = FlowsFact."Period"
        AND CHANNEL."Period type" = FlowsFact."Period type"
WHERE
    FlowsFact."Financial year" = '2024'; -- Remove filter