#### For each update below start transaction. if the count looks right then commit or use rollback.
### In the updates below you can also run the sub-queries to make sure it is returning the right number of ids

# Leaf with previous versions
start transaction;
delete from obs
where encounter_id in (select encounter.encounter_id
                       from encounter
                                join encounter_type on encounter.encounter_type = encounter_type.encounter_type_id
                                join patient on encounter.patient_id = patient.patient_id
                                join patient_identifier pi on patient.patient_id = pi.patient_id
                       where encounter_type.name like '%[Avni]%'
                         and pi.identifier = 'PATIENT_IDENTIFIER')
  and obs_group_id is not null and obs.previous_version is not null;
commit;
# rollback

# All leaf obs
start transaction;
delete from obs
where encounter_id in (select encounter.encounter_id
                      from encounter
                               join encounter_type on encounter.encounter_type = encounter_type.encounter_type_id
                               join patient on encounter.patient_id = patient.patient_id
                               join patient_identifier pi on patient.patient_id = pi.patient_id
                                where encounter_type.name like '%[Avni]%'
                                and pi.identifier = 'PATIENT_IDENTIFIER')
and obs_group_id is not null;
commit;
# rollback

# Parent obs with previous versions
start transaction;
delete from obs
where encounter_id in (select encounter.encounter_id
                       from encounter
                                join encounter_type on encounter.encounter_type = encounter_type.encounter_type_id
                                join patient on encounter.patient_id = patient.patient_id
                                join patient_identifier pi on patient.patient_id = pi.patient_id
                       where encounter_type.name like '%[Avni]%'
                         and pi.identifier = 'PATIENT_IDENTIFIER') and obs.previous_version is not null;;
commit;
# rollback


# All parent obs
start transaction;
delete from obs
where encounter_id in (select encounter.encounter_id
                      from encounter
                               join encounter_type on encounter.encounter_type = encounter_type.encounter_type_id
                               join patient on encounter.patient_id = patient.patient_id
                               join patient_identifier pi on patient.patient_id = pi.patient_id
                                where encounter_type.name like '%[Avni]%'
                                and pi.identifier = 'PATIENT_IDENTIFIER');
commit;
# rollback

# Encounter Providers
start transaction;
delete from encounter_provider
    where encounter_id in (select encounter.encounter_id
                           from encounter
                                    join encounter_type on encounter.encounter_type = encounter_type.encounter_type_id
                                    join patient on encounter.patient_id = patient.patient_id
                                    join patient_identifier pi on patient.patient_id = pi.patient_id
                           where encounter_type.name like '%[Avni]%'
                             and pi.identifier = 'PATIENT_IDENTIFIER');
commit;
# rollback

# Encounters
start transaction;
delete from encounter
    where visit_id in (select visit.visit_id
                        from visit
                               join visit_type vt on visit.visit_type_id = vt.visit_type_id
                               join patient p on visit.patient_id = p.patient_id
                               join patient_identifier pi on p.patient_id = pi.patient_id
                                where vt.name like '%[Avni]%'
                                and pi.identifier = 'PATIENT_IDENTIFIER')
      and encounter_type in (select encounter_type_id from encounter_type where name like '%[Avni]%');
commit;
# rollback

# Visit attributes
start transaction;
delete from visit_attribute
    where visit_id in (select visit.visit_id
                      from visit
                               join visit_type vt on visit.visit_type_id = vt.visit_type_id
                               join patient p on visit.patient_id = p.patient_id
                               join patient_identifier pi on p.patient_id = pi.patient_id
                        where vt.name like '%[Avni]%'
                        and pi.identifier = 'PATIENT_IDENTIFIER');
commit;
# rollback

# Visits
start transaction;
delete from visit
    where patient_id = (select patient.patient_id from patient
                               join patient_identifier pi on patient.patient_id = pi.patient_id
                                where pi.identifier = 'PATIENT_IDENTIFIER')
      and visit_type_id in (select visit_type_id from visit_type where name like '%[Avni]%');;
commit;
# rollback
