# ===========================================================================
# .returnSqlBlock — SQL template loading
# ===========================================================================

test_that(".returnSqlBlock returns non-empty SQL with aggregation", {
  sql <- OdysseusCharacterizationModule:::.returnSqlBlock(aggregated = TRUE)
  expect_true(is.character(sql))
  expect_true(nchar(sql) > 0)
  # Should contain both raw and aggregation parts

  expect_true(grepl("#domain_raw_results", sql))
  expect_true(grepl("covariate_id", sql, ignore.case = TRUE))
  expect_true(grepl("mean_value", sql, ignore.case = TRUE))
})

test_that(".returnSqlBlock returns raw-only SQL without aggregation", {
  sql <- OdysseusCharacterizationModule:::.returnSqlBlock(aggregated = FALSE)
  expect_true(is.character(sql))
  expect_true(nchar(sql) > 0)
  expect_true(grepl("#domain_raw_results", sql))
  # Should NOT contain aggregation
  expect_false(grepl("mean_value", sql, ignore.case = TRUE))
})

test_that(".returnSqlBlock SQL contains expected placeholders", {
  sql <- OdysseusCharacterizationModule:::.returnSqlBlock(aggregated = TRUE)

  expected_params <- c(
    "@cohort_database_schema", "@cohort_table",
    "@table_database_schema", "@table",
    "@concept_id_col", "@date_col",
    "@person_id_col",
    "@start_day", "@end_day",
    "@analysis_id", "@cohort_id",
    "@vocabulary_database_schema"
  )

  for (param in expected_params) {
    expect_true(
      grepl(param, sql, fixed = TRUE),
      info = paste("Missing placeholder:", param)
    )
  }
})

test_that(".returnSqlBlock raw SQL contains conditional blocks", {
  sql <- OdysseusCharacterizationModule:::.returnSqlBlock(aggregated = FALSE)

  # SqlRender conditional syntax
  expect_true(grepl("@overlap_clause", sql, fixed = TRUE))
  expect_true(grepl("@concept_set", sql, fixed = TRUE))
  expect_true(grepl("@atc", sql, fixed = TRUE))
})


# ===========================================================================
# renderSpecSql — spec rendering
# ===========================================================================

# Helper: build a real spec via singleNodeSetting
make_test_spec <- function(aggregated = TRUE) {
  plan <- planAnalysis(
    analysisWindows = defineAnalysisWindows(startDays = -30, endDays = -1),
    useBaseFeatures = list(
      condition_occurrence = list(include = TRUE, type = "start"),
      drug_exposure = list(include = FALSE),
      condition_era = list(include = FALSE),
      drug_era = list(include = FALSE),
      procedure_occurrence = list(include = FALSE),
      observation = list(include = FALSE),
      device_exposure = list(include = FALSE),
      visit_occurrence = list(include = FALSE),
      measurement = list(include = FALSE)
    ),
    useCohortFeatures = list(include = FALSE),
    useConceptSetFeatures = list(include = FALSE)
  )

  singleNodeSetting(
    plan = plan,
    cohortId = 1L,
    cohortDatabaseSchema = "results",
    cohortTable = "cohort",
    cdmDatabaseSchema = "cdm",
    vocabularyDatabaseSchema = "cdm",
    aggregated = aggregated
  )
}


test_that("renderSpecSql rejects non-singleNodeSpec", {
  expect_error(renderSpecSql(list()), "singleNodeSpec")
})

test_that("renderSpecSql produces rendered SQL without unresolved @params", {
  specs <- make_test_spec()
  s <- specs[[1]]

  rendered <- renderSpecSql(s)
  expect_true(is.character(rendered))
  expect_true(nchar(rendered) > 0)

  # No unresolved @param tokens should remain
  # SqlRender leaves @tokens that it couldn't match — check major ones
  expect_false(grepl("@cohort_database_schema", rendered, fixed = TRUE))
  expect_false(grepl("@cohort_table\\b", rendered))
  expect_false(grepl("@table_database_schema", rendered, fixed = TRUE))
  expect_false(grepl("@concept_id_col", rendered, fixed = TRUE))
  expect_false(grepl("@cohort_id\\b", rendered))
  expect_false(grepl("@analysis_id\\b", rendered))
})

test_that("renderSpecSql embeds correct schema and table", {
  specs <- make_test_spec()
  rendered <- renderSpecSql(specs[[1]])

  expect_true(grepl("results\\.cohort", rendered))
  expect_true(grepl("cdm\\.condition_occurrence", rendered))
})

test_that("renderSpecSql embeds correct concept_id_col", {
  specs <- make_test_spec()
  rendered <- renderSpecSql(specs[[1]])

  expect_true(grepl("condition_concept_id", rendered))
})

test_that("renderSpecSql embeds analysis_id", {
  specs <- make_test_spec()
  s <- specs[[1]]
  rendered <- renderSpecSql(s)

  expect_true(grepl(as.character(s$analysisId), rendered, fixed = TRUE))
})

test_that("renderSpecSql non-overlap uses start-date logic", {
  specs <- make_test_spec()
  rendered <- renderSpecSql(specs[[1]])

  expect_true(grepl("condition_start_date", rendered))
  expect_true(grepl("DATEADD", rendered, ignore.case = TRUE))
})

test_that("renderSpecSql with aggregated=TRUE includes aggregation block", {
  specs <- make_test_spec(aggregated = TRUE)
  rendered <- renderSpecSql(specs[[1]])
  expect_true(grepl("covariate_id", rendered, ignore.case = TRUE))
  expect_true(grepl("mean_value", rendered, ignore.case = TRUE))
})

test_that("renderSpecSql with aggregated=FALSE excludes aggregation", {
  specs <- make_test_spec(aggregated = FALSE)
  rendered <- renderSpecSql(specs[[1]])
  expect_false(grepl("mean_value", rendered, ignore.case = TRUE))
})

test_that("renderSpecSql supports targetDialect translation", {
  skip_if_not_installed("SqlRender")
  specs <- make_test_spec()
  rendered <- renderSpecSql(specs[[1]], targetDialect = "postgresql")
  expect_true(is.character(rendered))
  expect_true(nchar(rendered) > 0)
  # PostgreSQL translation should still produce valid SQL
  expect_true(grepl("INSERT INTO", rendered, ignore.case = TRUE))
})


# ===========================================================================
# renderAllSpecSql — batch rendering
# ===========================================================================

test_that("renderAllSpecSql renders all specs", {
  specs <- make_test_spec()
  result <- renderAllSpecSql(specs)

  expect_true(is.character(result))
  expect_length(result, length(specs))
  expect_true(all(nchar(result) > 0))
  expect_true(all(!is.na(names(result))))
})

test_that("renderAllSpecSql names match analysis IDs", {
  specs <- make_test_spec()
  result <- renderAllSpecSql(specs)

  expected_ids <- vapply(specs, function(s) as.character(s$analysisId), character(1L))
  expect_equal(names(result), expected_ids)
})

test_that("renderAllSpecSql rejects non-singleNodeSettingList", {
  expect_error(renderAllSpecSql(list()), "singleNodeSettingList")
})

test_that("renderAllSpecSql supports dialect translation", {
  skip_if_not_installed("SqlRender")
  specs <- make_test_spec()
  result <- renderAllSpecSql(specs, targetDialect = "postgresql")
  expect_length(result, length(specs))
})


# ===========================================================================
# singleNodeSpec now carries new fields
# ===========================================================================

test_that("singleNodeSpec has tableDatabaseSchema and personIdCol", {
  specs <- make_test_spec()
  s <- specs[[1]]

  expect_equal(s$tableDatabaseSchema, "cdm")
  expect_equal(s$personIdCol, "person_id")
  expect_equal(s$vocabularyDatabaseSchema, "cdm")
})

test_that("singleNodeSpec sql field is non-empty string", {
  specs <- make_test_spec()
  s <- specs[[1]]

  expect_true(is.character(s$sql))
  expect_true(nchar(s$sql) > 0)
})

test_that("cohort source spec uses subject_id for personIdCol", {
  plan <- planAnalysis(
    analysisWindows = defineAnalysisWindows(startDays = -30, endDays = -1),
    useBaseFeatures = list(drug_exposure = list(include = FALSE),
      condition_occurrence = list(include = FALSE),
      condition_era = list(include = FALSE), drug_era = list(include = FALSE),
      procedure_occurrence = list(include = FALSE),
      observation = list(include = FALSE),
      device_exposure = list(include = FALSE),
      visit_occurrence = list(include = FALSE),
      measurement = list(include = FALSE)
    ),
    useCohortFeatures = list(
      include = TRUE, type = "start",
      cohortIds = c(10L), cohortNames = c("Test"),
      cohortTable = "feat_tbl", covariateSchema = "results"
    ),
    useConceptSetFeatures = list(include = FALSE)
  )

  specs <- singleNodeSetting(
    plan = plan, cohortId = 1L,
    cohortDatabaseSchema = "r", cohortTable = "c",
    cdmDatabaseSchema = "cdm", vocabularyDatabaseSchema = "cdm"
  )

  s <- specs[[1]]
  expect_equal(s$personIdCol, "subject_id")
  expect_equal(s$tableDatabaseSchema, "results")
  expect_equal(s$source, "cohort")
})

test_that("concept set source spec uses person_id for personIdCol", {
  plan <- planAnalysis(
    analysisWindows = defineAnalysisWindows(startDays = -30, endDays = -1),
    useBaseFeatures = list(drug_exposure = list(include = FALSE),
      condition_occurrence = list(include = FALSE),
      condition_era = list(include = FALSE), drug_era = list(include = FALSE),
      procedure_occurrence = list(include = FALSE),
      observation = list(include = FALSE),
      device_exposure = list(include = FALSE),
      visit_occurrence = list(include = FALSE),
      measurement = list(include = FALSE)
    ),
    useCohortFeatures = list(include = FALSE),
    useConceptSetFeatures = list(
      include = TRUE, type = "binary",
      conceptSets = list(
        test_cs = list(
          items = list(list(concept = list(CONCEPT_ID = 100L))),
          tables = "condition_occurrence"
        )
      )
    )
  )

  specs <- singleNodeSetting(
    plan = plan, cohortId = 1L,
    cohortDatabaseSchema = "r", cohortTable = "c",
    cdmDatabaseSchema = "cdm", vocabularyDatabaseSchema = "vocab"
  )

  s <- specs[[1]]
  expect_equal(s$personIdCol, "person_id")
  expect_equal(s$tableDatabaseSchema, "cdm")
  expect_equal(s$vocabularyDatabaseSchema, "vocab")
})


# ===========================================================================
# Overlap rendering
# ===========================================================================

test_that("renderSpecSql overlap mode uses date_end_col", {
  plan <- planAnalysis(
    analysisWindows = defineAnalysisWindows(startDays = -30, endDays = -1),
    useBaseFeatures = list(
      condition_era = list(include = TRUE, type = "overlap"),
      drug_exposure = list(include = FALSE),
      condition_occurrence = list(include = FALSE),
      drug_era = list(include = FALSE),
      procedure_occurrence = list(include = FALSE),
      observation = list(include = FALSE),
      device_exposure = list(include = FALSE),
      visit_occurrence = list(include = FALSE),
      measurement = list(include = FALSE)
    ),
    useCohortFeatures = list(include = FALSE),
    useConceptSetFeatures = list(include = FALSE)
  )

  specs <- singleNodeSetting(
    plan = plan, cohortId = 1L,
    cohortDatabaseSchema = "r", cohortTable = "c",
    cdmDatabaseSchema = "cdm", vocabularyDatabaseSchema = "cdm"
  )

  rendered <- renderSpecSql(specs[[1]])
  expect_true(grepl("condition_era_end_date", rendered))
})


# ===========================================================================
# ATC spec SQL rendering
# ===========================================================================

test_that("renderSpecSql ATC spec includes ATC join", {
  plan <- planAnalysis(
    analysisWindows = defineAnalysisWindows(startDays = -30, endDays = -1),
    useBaseFeatures = list(
      drug_exposure = list(include = TRUE, atc = TRUE, atcLevels = c(3L)),
      condition_occurrence = list(include = FALSE),
      condition_era = list(include = FALSE),
      drug_era = list(include = FALSE),
      procedure_occurrence = list(include = FALSE),
      observation = list(include = FALSE),
      device_exposure = list(include = FALSE),
      visit_occurrence = list(include = FALSE),
      measurement = list(include = FALSE)
    ),
    useCohortFeatures = list(include = FALSE),
    useConceptSetFeatures = list(include = FALSE)
  )

  specs <- singleNodeSetting(
    plan = plan, cohortId = 1L,
    cohortDatabaseSchema = "r", cohortTable = "c",
    cdmDatabaseSchema = "cdm", vocabularyDatabaseSchema = "cdm"
  )

  rendered <- renderSpecSql(specs[[1]])
  expect_true(grepl("concept_ancestor", rendered))
  expect_true(grepl("atc_ca.ancestor_concept_id", rendered, fixed = TRUE))
  expect_true(grepl("ATC 3rd", rendered, fixed = TRUE))
})