/*
    Practice-level prescribing summary by BNF prefix
    ================================================

    Purpose:
    Produce a compact practice/month summary for prescribing under a BNF prefix.

    Good first query for:
    - "How much prescribing activity is there for this BNF section?"
    - "Which practices have the most patients/items/cost for this medicine area?"
    - A simple numerator for charts or practice packs.
*/

SET START_DATE = '2025-04-01';
SET END_DATE = '2026-03-31';
SET BNF_PREFIX = '0403';

WITH practices AS (
    -- Default reporting geography: active Norfolk and Suffolk parent GP practices.
    SELECT DISTINCT
        "OrganisationCode" AS "PracticeCode",
        "OrganisationName" AS "PracticeName",
        "PCNName",
        "PlaceName",
        "AllianceName"
    FROM DATA_HUB.DWH."DimOrganisationAndSite"
    WHERE "OrganisationSubType" = 'GP Practice'
      AND "IsSiteActive" = 'Yes'
      AND "IsSiteNorfolkAndSuffolk" = 'Yes'
      AND "SiteCode" = "OrganisationCode"
),
prescribing AS (
    -- Aggregate after joining to the medicine dimension so BNF matching is dm+d-backed.
    SELECT
        DATE_TRUNC('MONTH', rx."DateMedicationStart")::DATE AS "MonthStartDate",
        p."PracticeCode",
        p."PracticeName",
        p."PCNName",
        p."PlaceName",
        p."AllianceName",
        med."BNFParagraphCode",
        COUNT(DISTINCT rx."PersonKey") AS "Patients",
        COUNT(*) AS "PrescriptionRows",
        SUM(TRY_CAST(rx."Quantity" AS FLOAT)) AS "TotalQuantity",
        SUM(rx."EstPrice") AS "EstimatedCost"
    FROM REPORTING_DATASETS_ICB.SCRATCHPAD."MEDS__UnifiedPrescribingTable" rx
    INNER JOIN DATA_HUB.DWH."DimMedicineAndDevice" med
        ON rx."SNOMEDCode" = med."ProductSnomedCode"
       AND med."BNFCode" LIKE $BNF_PREFIX || '%'
    INNER JOIN practices p
        ON rx."CurrentGeneralPractice" = p."PracticeCode"
    WHERE rx."DateMedicationStart" BETWEEN $START_DATE AND $END_DATE
      AND rx."PersonKey" IS NOT NULL
    GROUP BY
        DATE_TRUNC('MONTH', rx."DateMedicationStart")::DATE,
        p."PracticeCode",
        p."PracticeName",
        p."PCNName",
        p."PlaceName",
        p."AllianceName",
        med."BNFParagraphCode"
)
SELECT
    "MonthStartDate",
    "PracticeCode",
    "PracticeName",
    "PCNName",
    "PlaceName",
    "AllianceName",
    "BNFParagraphCode",
    "Patients",
    "PrescriptionRows",
    "TotalQuantity",
    "EstimatedCost"
FROM prescribing
ORDER BY
    "MonthStartDate",
    "PracticeName",
    "BNFParagraphCode";