/*
    Medicine reference lookup
    =========================

    Purpose:
    Search DATA_HUB.DWH."DimMedicineAndDevice" for dm+d products and their
    related BNF, VTM, VMP, AMP, and pack-level identifiers.

    Common uses:
    - Find the SNOMED product codes to use in a prescribing or dispensing query.
    - Check whether a medicine group is better captured by BNF, VTM, VMP, or
      explicit product codes.
    - Understand how products roll up through the dm+d hierarchy.

    Replace the SEARCH_TEXT and optional BNF_PREFIX before running.
*/

SET SEARCH_TEXT = 'tirzepatide';
SET BNF_PREFIX = NULL;

SELECT DISTINCT
    med."ProductSnomedCode",
    med."ProductDescription",
    med."ProductLevel",
    med."TherapeuticMoietySnomedCode",
    med."TherapeuticMoietyName",
    med."MedicinalLatestSnomedCode",
    med."ParentPresentationSnomedCode",
    med."BNFCode",
    med."BNFParagraphCode",
    med."RouteName",
    med."StrengthDescription",
    med."PackUnitDescription",
    med."IndicativePricePerUnit",
    med."DrugTariffPricePerUnit"
FROM DATA_HUB.DWH."DimMedicineAndDevice" med
WHERE (
        LOWER(med."ProductDescription") LIKE '%' || LOWER($SEARCH_TEXT) || '%'
     OR LOWER(med."TherapeuticMoietyName") LIKE '%' || LOWER($SEARCH_TEXT) || '%'
     OR med."ProductSnomedCode" = $SEARCH_TEXT
     OR med."TherapeuticMoietySnomedCode" = $SEARCH_TEXT
     OR med."MedicinalLatestSnomedCode" = $SEARCH_TEXT
)
  AND ($BNF_PREFIX IS NULL OR med."BNFCode" LIKE $BNF_PREFIX || '%')
ORDER BY
    med."TherapeuticMoietyName",
    med."MedicinalLatestSnomedCode",
    med."ProductLevel",
    med."ProductDescription";