testthat::skip_on_cran()

test_that("ddbs_temp_conn: default in-memory connection", {
  conn <- ddbs_temp_conn()
  expect_true(DBI::dbIsValid(conn))
  # dbdir is NULL for in-memory connections in this environment
  expect_true(is.null(DBI::dbGetInfo(conn)$dbdir) || DBI::dbGetInfo(conn)$dbdir == ":memory:")
  
  # Auto-close check
  get_conn <- function() {
    c <- ddbs_temp_conn()
    c
  }
  ret_conn <- get_conn()
  expect_false(DBI::dbIsValid(ret_conn))
})

test_that("ddbs_temp_conn: file-based temporary connection", {
  conn <- ddbs_temp_conn(file = TRUE)
  expect_true(DBI::dbIsValid(conn))
  
  db_file <- attr(conn, "db_file")
  expect_true(is.character(db_file))
  expect_true(file.exists(db_file))
  
  # Cleanup check
  get_file_status <- function() {
    c <- ddbs_temp_conn(file = TRUE)
    f <- attr(c, "db_file")
    list(conn = c, file = f)
  }
  status <- get_file_status()
  expect_false(DBI::dbIsValid(status$conn))
  expect_false(file.exists(status$file))
})

test_that("ddbs_temp_conn: custom file path", {
  custom_path <- tempfile(fileext = ".myduck")
  conn <- ddbs_temp_conn(file = custom_path)
  expect_true(DBI::dbIsValid(conn))
  expect_equal(attr(conn, "db_file"), custom_path)
  expect_true(file.exists(custom_path))
  
  # Manual cleanup trigger (by exiting scope)
  # Use a separate path to avoid locking issues on Windows with the still-open 'conn'
  custom_path_cleanup <- tempfile(fileext = ".myduck_cleanup")
  test_custom_cleanup <- function(path) {
    c <- ddbs_temp_conn(file = path)
  }
  test_custom_cleanup(custom_path_cleanup)
  expect_false(file.exists(custom_path_cleanup))
  
  # Ensure we close the original connection too, safely
  if (DBI::dbIsValid(conn)) DBI::dbDisconnect(conn, shutdown = TRUE)
})

test_that("ddbs_temp_conn: custom path with cleanup = FALSE", {
  custom_path <- tempfile(fileext = ".persistent")
  
  test_no_cleanup <- function(path) {
    c <- ddbs_temp_conn(file = path, cleanup = FALSE)
    DBI::dbExecute(c, "CREATE TABLE t(id INT)")
  }
  
  test_no_cleanup(custom_path)
  
  # File should STILL exist
  expect_true(file.exists(custom_path))
  
  # Verify we can connect to it
  c2 <- DBI::dbConnect(duckdb::duckdb(), dbdir = custom_path)
  expect_true("t" %in% DBI::dbListTables(c2))
  DBI::dbDisconnect(c2, shutdown = TRUE)
  
  # Clean up manually
  unlink(custom_path)
})

test_that("ddbs_temp_conn: read_only file connection", {
  # Create a file first
  base_path <- tempfile(fileext = ".base")
  c1 <- DBI::dbConnect(duckdb::duckdb(), dbdir = base_path)
  DBI::dbExecute(c1, "CREATE TABLE t(id INT)")
  DBI::dbDisconnect(c1, shutdown = TRUE)
  
  # Use ddbs_temp_conn as read-only
  test_ro <- function(path) {
    c <- ddbs_temp_conn(file = path, read_only = TRUE, cleanup = FALSE)
    expect_error(DBI::dbExecute(c, "INSERT INTO t VALUES (1)"), "read-only")
  }
  test_ro(base_path)
  
  unlink(base_path)
})

test_that("ddbs_temp_conn: read_only with file=TRUE creates file first (regression)", {
  # Regression test: Previously failed because DuckDB can't open non-existent
  # files in read-only mode. The fix creates the file first, then opens as read-only.
  test_tempfile_readonly <- function() {
    conn <- ddbs_temp_conn(file = TRUE, read_only = TRUE, cleanup = FALSE)
    
    # Verify connection is valid
    expect_true(DBI::dbIsValid(conn))
    
    # Verify it's actually read-only by attempting a write operation
    expect_error(
      DBI::dbExecute(conn, "CREATE TABLE should_fail (id INT)"),
      "read-only"
    )
    
    # Verify spatial extension is loaded and works
    # result <- DBI::dbGetQuery(conn, "SELECT ST_Point(0, 0) as geom;")
    result <- DBI::dbGetQuery(conn, "SELECT ST_AsWKB(ST_Point(0, 0)) as geom;")
    expect_equal(nrow(result), 1)
    
    # Cleanup
    db_file <- attr(conn, "db_file")
    DBI::dbDisconnect(conn, shutdown = TRUE)
    unlink(db_file)
  }
  
  test_tempfile_readonly()
})