SELECT 
    CAST(p.birth_datetime AS DATE) AS birth_date,
    l.city,
    l.county,
    CAST(d.death_datetime AS DATE) AS death_date,
    CASE 
        WHEN p.ethnicity_source_value = 'Hispanic or Latino' THEN 'hispanic'
        WHEN p.ethnicity_source_value = 'Not Hispanic or Latino' THEN 'nonhispanic'
        ELSE p.ethnicity_source_value
    END AS ethnicity,
    p.gender_source_value AS gender,
    SUM(c.paid_by_payer + c.paid_by_primary) AS healthcare_coverage,
    c.total_charge AS healthcare_expenses,
    l.latitude,
    l.longitude,
    CASE 
        WHEN p.race_source_value = 'Black or African American' THEN 'black'
        WHEN p.race_source_value = 'White' THEN 'white'
        ELSE p.race_source_value
    END AS race,
    CONCAT(l.address_1, ' ', l.address_2) AS street_address,
    l.zip AS zip_code
FROM 
    "memory"."cocoon_schema"."stg_person" p
JOIN 
    "memory"."cocoon_schema"."stg_location" l ON p.location_id = l.location_id
LEFT JOIN 
    "memory"."cocoon_schema"."stg_death" d ON p.person_id = d.person_id
LEFT JOIN 
    "memory"."cocoon_schema"."stg_cost" c ON p.person_id = c.person_id
GROUP BY 
    p.person_id, p.birth_datetime, l.city, l.county, d.death_datetime, 
    p.ethnicity_source_value, p.gender_source_value, c.total_charge, 
    l.latitude, l.longitude, p.race_source_value, l.address_1, l.address_2, l.zip

SELECT 
    sco.condition_source_value AS allergen_code,
    sco.condition_source_value AS allergen_description,
    sco.condition_end_date AS allergy_end_date,
    sco.condition_start_date AS allergy_start_date
FROM 
    "memory"."cocoon_schema"."stg_condition_occurrence" AS sco

SELECT 
    po.procedure_occurrence_id AS care_plan_id,
    vo.visit_end_date AS end_date,
    vo.visit_start_date AS start_date,
    po.procedure_source_value AS treatment_code
FROM 
    "memory"."cocoon_schema"."stg_procedure_occurrence" po
JOIN 
    "memory"."cocoon_schema"."stg_visit_occurrence" vo
    ON po.visit_occurrence_id = vo.visit_occurrence_id

SELECT 
    co.condition_start_date AS illness_onset_date,
    (c.total_charge - c.total_paid) AS patient_outstanding_amount,
    vo.visit_start_date AS service_date
FROM 
    "memory"."cocoon_schema"."stg_visit_occurrence" vo
LEFT JOIN 
    "memory"."cocoon_schema"."stg_condition_occurrence" co
    ON vo.visit_occurrence_id = co.visit_occurrence_id
LEFT JOIN 
    "memory"."cocoon_schema"."stg_cost" c
    ON vo.person_id = c.person_id

SELECT 
    co.condition_end_date,
    co.condition_start_date
FROM 
    "memory"."cocoon_schema"."stg_condition_occurrence" co
JOIN 
    "memory"."cocoon_schema"."stg_visit_occurrence" vo
    ON co.visit_occurrence_id = vo.visit_occurrence_id

SELECT 
    sd.device_exposure_end_date AS usage_end_date,
    sd.device_exposure_start_date AS usage_start_date
FROM 
    "memory"."cocoon_schema"."stg_device" sd
JOIN 
    "memory"."cocoon_schema"."stg_visit_occurrence" svo
    ON sd.visit_occurrence_id = svo.visit_occurrence_id

SELECT 
    spo.procedure_cost AS base_cost,
    svo.visit_end_datetime AS encounter_end,
    svo.visit_start_datetime AS encounter_start,
    sc.paid_by_payer AS payer_coverage,
    spo.procedure_source_value AS procedure_code,
    sc.total_charge AS total_cost
FROM 
    "memory"."cocoon_schema"."stg_procedure_occurrence" spo
JOIN 
    "memory"."cocoon_schema"."stg_visit_occurrence" svo
    ON spo.visit_occurrence_id = svo.visit_occurrence_id
JOIN 
    "memory"."cocoon_schema"."stg_cost" sc
    ON spo.person_id = sc.person_id

SELECT 
    spo.procedure_cost AS base_cost,
    spo.procedure_datetime AS immunization_datetime
FROM 
    "memory"."cocoon_schema"."stg_procedure_occurrence" spo
LEFT JOIN 
    "memory"."cocoon_schema"."stg_person" sp ON spo.person_id = sp.person_id
LEFT JOIN 
    "memory"."cocoon_schema"."stg_visit_occurrence" svo ON spo.visit_occurrence_id = svo.visit_occurrence_id
LEFT JOIN 
    "memory"."cocoon_schema"."stg_provider" sprov ON spo.provider_id = sprov.provider_id
LEFT JOIN 
    "memory"."cocoon_schema"."stg_cost" sc ON spo.procedure_occurrence_id = sc.cost_id

SELECT 
    de.drug_exposure_end_date AS order_end_datetime,
    de.drug_exposure_start_date AS order_start_datetime,
    c.paid_by_payer AS payer_coverage_amount,
    c.total_charge AS total_cost
FROM 
    "memory"."cocoon_schema"."stg_drug_exposure" de
JOIN 
    "memory"."cocoon_schema"."stg_person" p ON de.person_id = p.person_id
JOIN 
    "memory"."cocoon_schema"."stg_visit_occurrence" vo ON de.visit_occurrence_id = vo.visit_occurrence_id
JOIN 
    "memory"."cocoon_schema"."stg_cost" c ON de.person_id = c.person_id

SELECT 
    obs.observation_source_value AS DESCRIPTION,
    obs.unit_source_value AS UNITS,
    CASE 
        WHEN obs.value_as_number IS NOT NULL THEN 'numeric'
        WHEN obs.value_as_string IS NOT NULL THEN 'text'
        ELSE 'unknown'
    END AS data_type,
    obs.observation_datetime AS observation_datetime,
    COALESCE(CAST(obs.value_as_number AS VARCHAR), obs.value_as_string) AS observation_value
FROM 
    "memory"."cocoon_schema"."stg_observation" obs
WHERE 
    obs.observation_source_value IS NOT NULL
    OR obs.unit_source_value IS NOT NULL
    OR obs.value_as_number IS NOT NULL
    OR obs.value_as_string IS NOT NULL
    OR obs.observation_datetime IS NOT NULL

SELECT 
    l.city AS CITY,
    l.state AS STATE,
    l.zip AS ZIP,
    cs.care_site_name AS facility_name,
    l.latitude AS latitude,
    l.longitude AS longitude,
    CONCAT(l.address_1, COALESCE(', ' || l.address_2, '')) AS street_address
FROM 
    "memory"."cocoon_schema"."stg_care_site" cs
JOIN 
    "memory"."cocoon_schema"."stg_location" l ON cs.location_id = l.location_id

SELECT 
    SUM(sc.paid_by_payer) AS covered_amount,
    SUM(DATEDIFF('month', CAST(spp.payer_plan_period_end_date AS DATE), CAST(spp.payer_plan_period_start_date AS DATE))) AS member_months,
    spp.payer_source_value AS payer_name,
    SUM(sc.paid_by_patient) AS uncovered_amount,
    COUNT(DISTINCT spp.payer_plan_period_id) AS unique_customers
FROM 
    "memory"."cocoon_schema"."stg_cost" sc
JOIN 
    "memory"."cocoon_schema"."stg_person" sp ON sc.person_id = sp.person_id
JOIN 
    "memory"."cocoon_schema"."stg_payer_plan_period" spp ON sp.person_id = spp.person_id
GROUP BY 
    spp.payer_source_value

SELECT 
    spo.procedure_cost AS procedure_cost,
    svo.visit_end_datetime AS procedure_end_time,
    spo.procedure_datetime AS procedure_start_time
FROM 
    "memory"."cocoon_schema"."stg_procedure_occurrence" spo
JOIN 
    "memory"."cocoon_schema"."stg_visit_occurrence" svo
    ON spo.visit_occurrence_id = svo.visit_occurrence_id

SELECT 
    l.city AS CITY,
    p.gender_source_value AS GENDER,
    p.specialty_source_value AS SPECIALITY,
    l.state AS STATE,
    l.zip AS ZIP,
    l.latitude,
    l.longitude,
    p.provider_name,
    CASE 
        WHEN l.address_2 IS NOT NULL THEN CONCAT(l.address_1, ' ', l.address_2)
        ELSE l.address_1
    END AS street_address
FROM 
    "memory"."cocoon_schema"."stg_provider" p
CROSS JOIN 
    "memory"."cocoon_schema"."stg_location" l