test_that("createConceptSetTempTable rejects non-DatabaseConnectorConnection", {
  expect_error(
    createConceptSetTempTable(
      connection = list(),
      csQueries = list(a = "SELECT 1"),
      vocabularyDatabaseSchema = "vocab"
    ),
    "must be a DatabaseConnector connection object"
  )
  expect_error(
    createConceptSetTempTable(
      connection = NULL,
      csQueries = list(a = "SELECT 1"),
      vocabularyDatabaseSchema = "vocab"
    ),
    "must be a DatabaseConnector connection object"
  )
})

test_that("createConceptSetTempTable rejects bad vocabularyDatabaseSchema", {
  conn <- fake_connection()
  expect_error(
    createConceptSetTempTable(conn, list(a = "SELECT 1"), vocabularyDatabaseSchema = ""),
    "must be a single non-empty character string"
  )
  expect_error(
    createConceptSetTempTable(conn, list(a = "SELECT 1"), vocabularyDatabaseSchema = NA_character_),
    "must be a single non-empty character string"
  )
  expect_error(
    createConceptSetTempTable(conn, list(a = "SELECT 1"), vocabularyDatabaseSchema = c("a", "b")),
    "must be a single non-empty character string"
  )
  expect_error(
    createConceptSetTempTable(conn, list(a = "SELECT 1"), vocabularyDatabaseSchema = 123),
    "must be a single non-empty character string"
  )
})

test_that("createConceptSetTempTable executes SQL for valid inputs", {
  conn <- fake_connection()
  state <- new.env(parent = emptyenv())
  state$sqls <- character()

  local_namespace_mock("DatabaseConnector", "renderTranslateExecuteSql", function(connection, sql, ...) {
    state$sqls <- c(state$sqls, sql)
    invisible(NULL)
  })

  withr::local_options(list(OdysseusCostModule.quiet = TRUE))

  result <- createConceptSetTempTable(
    connection = conn,
    csQueries = list(diabetes = "SELECT 'diabetes' AS cs_name, 201820 AS concept_id"),
    vocabularyDatabaseSchema = "cdm_v5"
  )

  expect_null(result)
  expect_true(length(state$sqls) >= 1)
  expect_true(any(grepl("SELECT \\* INTO #temp_cs_table", state$sqls)))
})

test_that("createConceptSetTempTable unions multiple queries", {
  conn <- fake_connection()
  state <- new.env(parent = emptyenv())
  state$sqls <- character()

  local_namespace_mock("DatabaseConnector", "renderTranslateExecuteSql", function(connection, sql, ...) {
    state$sqls <- c(state$sqls, sql)
    invisible(NULL)
  })

  withr::local_options(list(OdysseusCostModule.quiet = TRUE))

  createConceptSetTempTable(
    connection = conn,
    csQueries = list(
      a = "SELECT 'a' AS cs_name, 1 AS concept_id",
      b = "SELECT 'b' AS cs_name, 2 AS concept_id"
    ),
    vocabularyDatabaseSchema = "vocab"
  )

  create_sql <- state$sqls[1]
  expect_true(grepl("UNION ALL", create_sql))
})