/******************************************************************************************
Getting Messy with SQL Reporting: Preventive Breast Imaging
RCHC Analytics Academy: 5/12/2022
Created by: Nicholas Alonzo (nicholas@diversifyds.org)
Description:
  A solution for the hands-on code-along script to work with preventive breast imaging demo
  tables. Keep in mind this is just one of many possible solutions - everyone approaches
  a problem differently.
*******************************************************************************************/

-- all female patients 45 and older including their assigned doctor and clinic
DROP TABLE IF EXISTS universe;
CREATE TEMPORARY TABLE universe AS
  SELECT
    pts.id AS patient_id,
    clinics.name AS patients_assigned_clinic,
    CONCAT_WS(', ', doctors.last_name, doctors.first_name) AS patients_assigned_doctor,
    pts.mrn AS patients_mrn,
    pts.first_name || ' ' || pts.last_name AS patient_name,
    pts.date_of_birth AS patient_dob,
    EXTRACT(YEAR FROM AGE(pts.date_of_birth)) AS patient_age,
    pts.preferred_language AS patient_language,
    pts.cell_phone AS patient_cell_phone
  FROM demo_patients AS pts
    INNER JOIN demo_clinics AS clinics ON pts.clinic_id = clinics.id
    INNER JOIN demo_doctors AS doctors ON pts.doctor_id = doctors.id
  WHERE pts.birth_sex = 'Female' AND
        EXTRACT(YEAR FROM AGE(pts.date_of_birth)) BETWEEN 45 AND 75;

CREATE INDEX id_universe_index ON universe (patient_id);


-- identify how to join external data
/*
demo_external_breast_imaging: patient_identifier
Medical record number of the patient from the health center EHR in a 10-character sequence padded with leading 0’s
*/
-- option 1: LTRIM()
SELECT
  universe.patient_id,
  external_img.notes
FROM universe
  INNER JOIN demo_external_breast_imaging AS external_img ON
    universe.patients_mrn = LTRIM(external_img.patient_identifier, '0');


-- option 2: LPAD()
SELECT
  universe.patient_id,
  external_img.notes
FROM universe
  INNER JOIN demo_external_breast_imaging AS external_img ON
    LPAD(universe.patients_mrn, 10, '0') = external_img.patient_identifier;


-- option 3: using first 10 characters of first/last name and casting date of birth
-- note that the first and last name would have to be selected in the universe query
SELECT
  universe.patient_id,
  external_img.notes
FROM universe
  -- option 3.1
  -- individually match the first and last name together and cast date of birth
  INNER JOIN demo_external_breast_imaging AS external_img ON (
      LEFT(UPPER(universe.patient_first_name), 10) = external_img.patient_first_name AND
      LEFT(UPPER(universe.patient_last_name), 10) = external_img.patient_last_name AND
      universe.patient_dob = TO_DATE(external_img.patient_birth_date, 'YYYYMMDD'))
  -- option 3.2
  -- concat the first and last name together and cast date of birth
  INNER JOIN demo_external_breast_imaging AS external_img ON (
      UPPER(LEFT(universe.patient_first_name, 10) || LEFT(universe.patient_last_name, 10)) =
      external_img.patient_last_name || external_img.patient_first_name AND
      universe.patient_dob = TO_DATE(external_img.patient_birth_date, 'YYYYMMDD'));


-- extracting out imaging date
-- step 1: find the pattern (the first date after imaging date)
SELECT notes
FROM demo_external_breast_imaging;
/*
Imaging date performed 7/22/20
Imaging date: performed 2/1/20
Imaging date completed on 2/26/2021
Imaging date finished 10/3/2020
Imaging date done on 4/6/20
Imaging date: finished 2/25/22
*/
-- step 2: create the regular expression and test
SELECT SUBSTRING('Imaging date: finished 2/25/22', 'Imaging date.*?(\d{1,2}/\d{1,2}/\d{2,4})');


-- matching the imaging name
-- step 1: find the pattern
SELECT notes
FROM demo_external_breast_imaging;
/*
Bilateral screening
Bilateral screen
Uni-Left screening
Uni-Left screen
*/
SELECT 'Bilateral screening' ~* 'bilateral';
SELECT 'Uni-Left screening' ~* 'uni.?left';


-- extracting out the followup date
-- step 1: find the pattern
SELECT notes
FROM demo_external_breast_imaging;
/*
Follow-up in 1 year: 7/22/2021
Follow up on 7/4/23
F/u on 10/3/22
f.u screening on 6/2/22
*/
-- step 2: create the regular expression and test
SELECT SUBSTRING('Follow up on 7/4/23', 'Follow.up.*?(\d{1,2}/\d{1,2}/\d{2,4}\y)');
SELECT SUBSTRING('Follow up on 7/4/23', 'F.u.*?(\d{1,2}/\d{1,2}/\d{2,4}\y)');


-- matching the result
-- step 1: find the pattern
SELECT notes
FROM demo_external_breast_imaging;
/*
BIRADS 1
bi-rads 1
birads 2
BI-RADS 3
bi-rads 4
*/
-- step 2: create the regular expression and test
SELECT 'bi-rads 4' ~* 'bi.?rads \d';


-- all external brest imaging details extracted/manipulated from the notes to match format in EHR data
DROP TABLE IF EXISTS universe_external_breast_imaging;
CREATE TEMPORARY TABLE universe_external_breast_imaging AS
  SELECT
    universe.patient_id,
    'Outside Referral Imaging' :: TEXT AS imaging_center,
    SUBSTRING(external_img.notes, 'Imaging date.*?(\d{1,2}/\d{1,2}/\d{2,4}\y)') :: DATE AS imaging_date,
    CASE
      WHEN external_img.notes ~* 'bilateral'
        THEN 'Bilateral'
      WHEN external_img.notes ~* 'uni.?left'
        THEN 'Unilateral Left'
      WHEN external_img.notes ~* 'uni.?right'
        THEN 'Unilateral Right'
      ELSE 'N/A'
    END :: TEXT AS imaging_name,
    CASE
      WHEN external_img.notes ~* 'bi.?rads 1'
        THEN 'BI-RADS 1'
      WHEN external_img.notes ~* 'bi.?rads 2'
        THEN 'BI-RADS 2'
      WHEN external_img.notes ~* 'bi.?rads 3'
        THEN 'BI-RADS 3'
      WHEN external_img.notes ~* 'bi.?rads 4'
        THEN 'BI-RADS 4'
      ELSE 'N/A'
    END :: TEXT AS imaging_result,
    CASE
      WHEN external_img.notes ~* 'follow.up.*?(\d{1,2}/\d{1,2}/\d{2,4}\y)'
        THEN SUBSTRING(LOWER(external_img.notes), 'follow.up.*?(\d{1,2}/\d{1,2}/\d{2,4}\y)')
      WHEN external_img.notes ~* 'f.u.*?(\d{1,2}/\d{1,2}/\d{2,4}\y)'
        THEN SUBSTRING(LOWER(external_img.notes), 'f.u.*?(\d{1,2}/\d{1,2}/\d{2,4}\y)')
    END :: DATE AS imaging_followup_date
  FROM universe
    INNER JOIN demo_external_breast_imaging AS external_img ON
      universe.patients_mrn = LTRIM(external_img.patient_identifier, '0');


-- all breast imaging performed internally and externally
DROP TABLE IF EXISTS universe_all_breast_imaging;
CREATE TEMPORARY TABLE universe_all_breast_imaging AS
  SELECT
    demo_breast_imaging.patient_id,
    demo_clinics.name AS imaging_center,
    demo_breast_imaging.name AS imaging_name,
    demo_breast_imaging.date AS imaging_date,
    demo_breast_imaging.result AS imaging_result,
    demo_breast_imaging.followup_date AS imaging_follow_up_date
  FROM demo_breast_imaging
    INNER JOIN demo_clinics ON demo_breast_imaging.clinic_id = demo_clinics.id
  UNION
  SELECT
    patient_id,
    imaging_center,
    imaging_name,
    imaging_date,
    imaging_result,
    imaging_followup_date
  FROM universe_external_breast_imaging;


-- most recent imaging of patient including the status
SELECT DISTINCT ON (universe.patient_id)
  universe.patients_assigned_clinic,
  universe.patients_assigned_doctor,
  universe.patients_mrn,
  universe.patient_name,
  universe.patient_dob,
  universe.patient_age,
  universe.patient_language,
  universe.patient_cell_phone,
  imaging.imaging_center,
  imaging.imaging_date,
  imaging.imaging_name,
  imaging.imaging_result,
  imaging.imaging_follow_up_date,
  CASE
    WHEN imaging.imaging_follow_up_date < CURRENT_DATE
      THEN 'Overdue'
    WHEN imaging.imaging_follow_up_date = CURRENT_DATE
      THEN 'Due Today'
    WHEN imaging.imaging_follow_up_date > CURRENT_DATE
      THEN 'Up To Date'
    ELSE 'No Imaging'
  END AS status
FROM universe
  LEFT JOIN universe_all_breast_imaging AS imaging ON universe.patient_id = imaging.patient_id
ORDER BY universe.patient_id, imaging.imaging_date DESC;


-- What else is left to do?
/*
Add a helpful description
Add visualizations
Create more sophisticated regular expressions
Add additional values in status, due within a month?
Add additional columns of flags for filtering
Add diagnosis related data
etc...
*/