## ----include = FALSE----------------------------------------------------------
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

## ----eval=FALSE---------------------------------------------------------------
# library(syrona)
# 
# db <- syrona_connect_pg(
#   host         = "localhost",        # via the SSH tunnel
#   port         = 5432,
#   dbname       = "omop",
#   user         = "your_user",
#   cdm_schema   = "ohdsi_cdm_202511", # ask your DB admin if unsure
#   write_schema = "results_your_user" # must be writable
# )

## ----eval=FALSE---------------------------------------------------------------
# # A quick row count on person - confirms your connection and schema access
# DBI::dbGetQuery(db$con, "SELECT COUNT(*) FROM ohdsi_cdm_202511.person")

## ----eval=FALSE---------------------------------------------------------------
# DBI::dbGetQuery(db$con,
#   "SELECT table_name FROM information_schema.tables
#    WHERE table_schema = 'ohdsi_cdm_202511'
#    ORDER BY table_name")

## ----eval=FALSE---------------------------------------------------------------
# db <- syrona_connect("path/to/omop.duckdb", read_only = FALSE)

## ----eval=FALSE---------------------------------------------------------------
# list_care_sites(db$con, cdm_schema = "ohdsi_cdm_202511")
# #> # A tibble: 8 x 3
# #>   care_site_id care_site_name        n_patients
# #>          <int> <chr>                      <int>
# #> 1          101 Central Hospital           45000
# #> 2          205 University Clinic          28000
# #> 3          312 Regional Hospital          15000
# #> ...

## ----eval=FALSE---------------------------------------------------------------
# create_caresite_cohort(
#   con           = db$con,
#   care_site_id  = 101,
#   cohort_id     = 1,
#   cohort_schema = "results_your_user",
#   cdm_schema    = "ohdsi_cdm_202511"
# )
# #> v Cohort 1 (care_site 101): 45000 rows inserted.
# 
# create_caresite_cohort(
#   con           = db$con,
#   care_site_id  = 205,
#   cohort_id     = 2,
#   cohort_schema = "results_your_user",
#   cdm_schema    = "ohdsi_cdm_202511"
# )
# #> v Cohort 2 (care_site 205): 28000 rows inserted.

## ----eval=FALSE---------------------------------------------------------------
# cohort_summary(db$con, cohort_id = 1, cohort_schema = "results_your_user")
# #> # A tibble: 1 x 5
# #>   cohort_definition_id n_entries n_persons min_start  max_end
# #>                  <int>     <int>     <int> <date>     <date>
# #> 1                    1     45000     45000 2012-01-03 2019-12-28
# 
# cohort_summary(db$con, cohort_id = 2, cohort_schema = "results_your_user")

## ----eval=FALSE---------------------------------------------------------------
# extract_all(
#   dataset_name  = "Central_Hospital",
#   db            = db,
#   cohort_id     = 1,
#   cohort_schema = "results_your_user"
# )
# #> i Applying cohort filter (cohort_id = 1)...
# #>
# #> -- Extracting dataset: Central_Hospital [conditions, procedures, drugs] --
# #>
# #> * Extracting denominators (ACHILLES-116)...
# #> * Extracting demographics...
# #> * Extracting death counts (ACHILLES-504)...
# #> * Extracting condition prevalence (ACHILLES-404)...
# #> * Extracting condition info...
# #> * Extracting condition chapters...
# #> * Extracting condition attributes...
# #> * Extracting procedure prevalence...
# #> ...
# #> v Saved to data/sources/Central_Hospital/

## ----eval=FALSE---------------------------------------------------------------
# extract_all("Central_Hospital", db = db, cohort_id = 1,
#             cohort_schema = "results_your_user",
#             domains = "conditions")

## ----eval=FALSE---------------------------------------------------------------
# list.files("data/sources/Central_Hospital/")
# #>  [1] "_metadata.csv"             "condition_attributes.csv"
# #>  [3] "condition_chapters.csv"    "condition_info.csv"
# #>  [5] "condition_prevalence.csv"  "death_counts.csv"
# #>  [7] "demographics.csv"          "drug_attributes.csv"
# #>  [9] "drug_chapters.csv"         "drug_info.csv"
# #> [11] "drug_prevalence.csv"       "procedure_attributes.csv"
# #> [13] "procedure_chapters.csv"    "procedure_info.csv"
# #> [15] "procedure_prevalence.csv"

## ----eval=FALSE---------------------------------------------------------------
# d1 <- load_dataset("Central_Hospital")
# nrow(d1$condition_info)            # number of distinct conditions
# sum(d1$demographics$patient_count) # total F+M persons

## ----eval=FALSE---------------------------------------------------------------
# extract_all(
#   dataset_name  = "University_Clinic",
#   db            = db,
#   cohort_id     = 2,
#   cohort_schema = "results_your_user"
# )

## ----eval=FALSE---------------------------------------------------------------
# list_datasets()
# #> [1] "Central_Hospital" "University_Clinic"

## ----eval=FALSE---------------------------------------------------------------
# compare_all(
#   d1 = "Central_Hospital",
#   d2 = "University_Clinic"
# )
# #> -- Comparing Central_Hospital vs University_Clinic --
# #> * conditions: yearly -> meta_agegroups -> meta_by_sex -> meta_summary
# #> * procedures: yearly -> meta_agegroups -> meta_by_sex -> meta_summary
# #> * drugs: yearly -> meta_agegroups -> meta_by_sex -> meta_summary
# #> v Saved to data/comparisons/Central_Hospital_vs_University_Clinic/

## ----eval=FALSE---------------------------------------------------------------
# list.files("data/comparisons/Central_Hospital_vs_University_Clinic/")
# #>  [1] "_metadata.csv"
# #>  [2] "condition_meta_agegroups.csv"
# #>  [3] "condition_meta_by_sex.csv"
# #>  [4] "condition_meta_summary.csv"
# #>  [5] "condition_yearly.csv"
# #>  [6] "drug_meta_agegroups.csv"
# #>  [7] "drug_meta_by_sex.csv"
# #>  [8] "drug_meta_summary.csv"
# #>  [9] "drug_yearly.csv"
# #> [10] "procedure_meta_agegroups.csv"
# #> [11] "procedure_meta_by_sex.csv"
# #> [12] "procedure_meta_summary.csv"
# #> [13] "procedure_yearly.csv"
# 
# list_comparisons()
# #> [1] "Central_Hospital_vs_University_Clinic"

## ----eval=FALSE---------------------------------------------------------------
# comp <- load_comparison("Central_Hospital", "University_Clinic")
# comp$condition_meta_summary |>
#   dplyr::arrange(dplyr::desc(abs(log2_pr))) |>
#   dplyr::select(concept_name, log2_pr, ci_low, ci_high, fold_diff) |>
#   head(10)

## ----eval=FALSE---------------------------------------------------------------
# run_app()

## ----eval=FALSE---------------------------------------------------------------
# # Optional: drop the cohorts you created
# delete_cohort(db$con, cohort_id = 1, cohort_schema = "results_your_user")
# delete_cohort(db$con, cohort_id = 2, cohort_schema = "results_your_user")
# 
# # Always disconnect from the database
# syrona_disconnect(db)

## ----eval=FALSE---------------------------------------------------------------
# DBI::dbGetQuery(db$con,
#   "SELECT nspname,
#           has_schema_privilege(current_user, nspname, 'USAGE') AS can_use,
#           has_schema_privilege(current_user, nspname, 'CREATE') AS can_create
#    FROM pg_namespace
#    WHERE nspname NOT LIKE 'pg_%'
#    ORDER BY nspname")

## ----eval=FALSE---------------------------------------------------------------
# DBI::dbGetQuery(db$con,
#   "SELECT table_name FROM information_schema.tables
#    WHERE table_schema = 'ohdsi_cdm_202511'
#    ORDER BY table_name")

## ----eval=FALSE---------------------------------------------------------------
# options(syrona.data_dir = "/path/to/folder/that/contains/data/")
# run_app()