-- Metrics views for NCD analytics (MySQL 8)

CREATE OR REPLACE VIEW v_ncd_base_join AS
SELECT
    r.`Clinic_code` AS clinic_code,
    r.`Pid` AS pid,
    r.`FuchiaID` AS fuchia_id,
    r.`Gender` AS gender,
    r.`Reg_Date` AS reg_date,
    r.`Area_Division` AS area_division,
    r.`Township` AS township,
    r.`visit_Age` AS visit_age,
    r.`Current_Age` AS current_age,
    r.`1stBP` AS first_bp,
    r.`1stBP_date` AS first_bp_date,
    p.`Date of Birth` AS date_of_birth_raw
FROM ncd_pt_registers r
LEFT JOIN patients p
    ON p.`Pid` = r.`Pid`;

CREATE OR REPLACE VIEW v_ncd_followups_clean AS
SELECT
    f_raw.*,
    CASE
        WHEN f_raw.sbp_raw BETWEEN 50 AND 300 AND f_raw.dbp_raw BETWEEN 30 AND 200 THEN f_raw.sbp_raw
        ELSE NULL
    END AS sbp,
    CASE
        WHEN f_raw.sbp_raw BETWEEN 50 AND 300 AND f_raw.dbp_raw BETWEEN 30 AND 200 THEN f_raw.dbp_raw
        ELSE NULL
    END AS dbp
FROM (
    SELECT
        f.`id` AS followup_id,
        f.`Clinic_code` AS clinic_code,
        f.`Pid` AS pid,
        f.`FuchiaID` AS fuchia_id,
        f.`Visit_date` AS visit_date,
        f.`Reg_Date` AS reg_date,
        f.`Agey` AS visit_age,
        f.`Gender` AS gender,
        f.`Area_Division` AS area_division,
        f.`Township` AS township,
        f.`NCD_Diagnosis` AS ncd_diagnosis,
        f.`Type_cur_visit` AS type_cur_visit,
        f.`Late_visit` AS late_visit,
        f.`Late_duration` AS late_duration,
        f.`Late_duration_unit` AS late_duration_unit,
        f.`Late_follow` AS late_follow,
        f.`Late_fol_duration` AS late_fol_duration,
        f.`Time` AS visit_time,
        f.`own_clinic_Bp` AS bp_raw,
        f.`own_Bp_Stage` AS bp_stage,
        f.`ncdV_1st_tot_Diabetes` AS dm_1st_total,
        f.`FBS` AS fbs,
        f.`FBS_test_date` AS fbs_test_date,
        f.`Loaction_test` AS fbs_test_location,
        f.`ncdV_2nd_tot_Diabetes` AS dm_2nd_total,
        f.`2HPP` AS t2hpp,
        f.`2HPP_test_date` AS t2hpp_test_date,
        f.`Loaction_Test2` AS t2hpp_test_location,
        f.`Lab_res_Date` AS lab_res_date,
        f.`Alt` AS alt,
        f.`HBA1C` AS hba1c,
        f.`Uring_AC_ratio` AS uring_ac_ratio,
        f.`Glucose_res` AS glucose_res,
        f.`Protein_res` AS protein_res,
        f.`Creatinine` AS creatinine,
        f.`Creat_unit` AS creat_unit,
        f.`CRCL` AS crcl,
        f.`Total_cholesterol` AS total_cholesterol,
        f.`Total_cho_Unit` AS total_cholesterol_unit,
        f.`CVD_Risk` AS cvd_risk,
        f.`HDL` AS hdl,
        f.`HDL_unit` AS hdl_unit,
        f.`LDL` AS ldl,
        f.`LDL_unit` AS ldl_unit,
        f.`Triglyceride` AS triglyceride,
        f.`Triglyceride_unit` AS triglyceride_unit,
        f.`Pulse` AS pulse,
        f.`Pulse_rate` AS pulse_rate,
        f.`Diabetic_foot` AS diabetic_foot,
        f.`Diabetic_Neuropathy` AS diabetic_neuropathy,
        f.`Lifestyle advice` AS lifestyle_advice,
        f.`Medication changed` AS medication_changed,
        f.`Patient_adhe medic` AS patient_adherence,
        f.`Drug_Supply` AS drug_supply,
        f.`Symptom hypoglycemia` AS symptom_hypoglycemia,
        f.`Foth_medi` AS other_medication,
        f.`Foth_medi_spec` AS other_medication_spec,
        f.`Out_come` AS outcome,
        f.`Tout_mam_clinic` AS tout_mam_clinic,
        f.`death_date` AS death_date,
        f.`Tout_physician_data` AS tout_physician_data,
        f.`Ncd_Tout_icmv_location` AS ncd_tout_icmv_location,
        f.`Cause_of_death` AS cause_of_death,
        f.`Fup_doc_initial` AS fup_doc_initial,
        f.`Next_Appointment` AS next_appointment,
        f.`visit_type` AS visit_type,
        CASE
            WHEN f.`own_clinic_Bp` REGEXP '^[[:space:]]*[0-9]{2,3}[[:space:]]*/[[:space:]]*[0-9]{2,3}[[:space:]]*$'
                THEN CAST(SUBSTRING_INDEX(TRIM(f.`own_clinic_Bp`), '/', 1) AS UNSIGNED)
            ELSE NULL
        END AS sbp_raw,
        CASE
            WHEN f.`own_clinic_Bp` REGEXP '^[[:space:]]*[0-9]{2,3}[[:space:]]*/[[:space:]]*[0-9]{2,3}[[:space:]]*$'
                THEN CAST(SUBSTRING_INDEX(TRIM(f.`own_clinic_Bp`), '/', -1) AS UNSIGNED)
            ELSE NULL
        END AS dbp_raw
    FROM ncd_followups f
) AS f_raw;

CREATE OR REPLACE VIEW v_ncd_latest_followup_per_patient AS
SELECT *
FROM (
    SELECT
        f.*,
        ROW_NUMBER() OVER (PARTITION BY f.pid ORDER BY f.visit_date DESC, f.followup_id DESC) AS rn
    FROM v_ncd_followups_clean f
) ranked
WHERE ranked.rn = 1;

CREATE OR REPLACE VIEW v_ncd_monthly_summary_seed AS
SELECT
    DATE_FORMAT(r.`Reg_Date`, '%Y-%m-01') AS month_start,
    COUNT(*) AS new_regs,
    0 AS followups
FROM ncd_pt_registers r
WHERE r.`Reg_Date` IS NOT NULL
GROUP BY DATE_FORMAT(r.`Reg_Date`, '%Y-%m-01')

UNION ALL

SELECT
    DATE_FORMAT(f.`Visit_date`, '%Y-%m-01') AS month_start,
    0 AS new_regs,
    COUNT(*) AS followups
FROM ncd_followups f
WHERE f.`Visit_date` IS NOT NULL
GROUP BY DATE_FORMAT(f.`Visit_date`, '%Y-%m-01');
