## ----include = FALSE----------------------------------------------------------
# Evaluate chunks only where the ducklake DuckDB extension is already
# installed. The probe never downloads anything, so building this vignette
# needs no network access.
ducklake_available <- ducklake::ducklake_extension_available()
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  eval = ducklake_available
)
# Use a unique temp directory for this vignette to avoid conflicts during R CMD check
vignette_temp_dir <- file.path(tempdir(), "modifying_tables_vignette")
dir.create(vignette_temp_dir, showWarnings = FALSE, recursive = TRUE)
knitr::opts_knit$set(root.dir = vignette_temp_dir)

## ----setup, message = FALSE---------------------------------------------------
library(ducklake)
library(dplyr)

# Setup for examples
# The ducklake extension only needs installing once per machine:
# install_ducklake()
attach_ducklake("modifying_tables_lake", lake_path = vignette_temp_dir)

# Load a sample dataset
with_transaction(
  create_table(mtcars, "cars"),
  author = "Data Engineer",
  commit_message = "Initial car data load"
)

## ----rows-setup---------------------------------------------------------------
fleet <- data.frame(
  car_id = 1:3,
  model = c("Corolla", "Civic", "Model 3"),
  mileage = c(42000, 38500, 12000)
)

with_transaction(
  create_table(fleet, "fleet"),
  author = "Fleet Manager",
  commit_message = "Initial fleet inventory"
)

## ----rows-insert--------------------------------------------------------------
new_cars <- data.frame(
  car_id = 4:5,
  model = c("Leaf", "Ioniq 5"),
  mileage = c(500, 120)
)

rows_insert(get_ducklake_table("fleet"), new_cars, by = "car_id")

get_ducklake_table("fleet") |> collect()

## ----rows-update--------------------------------------------------------------
correction <- data.frame(car_id = 2, mileage = 39000)

rows_update(get_ducklake_table("fleet"), correction, by = "car_id")

get_ducklake_table("fleet") |> filter(car_id == 2) |> collect()

## ----rows-delete--------------------------------------------------------------
sold <- data.frame(car_id = 1)

rows_delete(get_ducklake_table("fleet"), sold, by = "car_id")

get_ducklake_table("fleet") |> collect()

## ----rows-transaction---------------------------------------------------------
april_arrivals <- data.frame(car_id = 6, model = "ID.4", mileage = 60)
recalled <- data.frame(car_id = 4)

with_transaction({
  rows_insert(get_ducklake_table("fleet"), april_arrivals, by = "car_id")
  rows_delete(get_ducklake_table("fleet"), recalled, by = "car_id")
},
  author = "Fleet Manager",
  commit_message = "April intake; remove recalled Leaf"
)

# The full history: every change is versioned, wrapped or not
list_table_snapshots("fleet")

## ----rows-upsert--------------------------------------------------------------
service_batch <- data.frame(
  car_id = c(3, 7),
  model = c("Model 3", "Kona"),
  mileage = c(15200, 8000)
)

rows_upsert(get_ducklake_table("fleet"), service_batch, by = "car_id")

get_ducklake_table("fleet") |> arrange(car_id) |> collect()

## ----merge-into---------------------------------------------------------------
registry <- data.frame(
  car_id = c(3, 5, 8),
  model = c("Model 3", "Ioniq 5", "e-Golf"),
  mileage = c(15400, 900, 21000)
)

with_transaction(
  merge_into("fleet", registry, by = "car_id", delete_missing = TRUE),
  author = "Fleet Manager",
  commit_message = "Quarterly registry sync"
)

get_ducklake_table("fleet") |> arrange(car_id) |> collect()

## ----merge-into-changes-------------------------------------------------------
latest <- max(list_table_snapshots("fleet")$snapshot_id)
get_table_changes("fleet", latest, latest) |>
  select(change_type, car_id, model, mileage) |>
  collect()

## ----update-rows--------------------------------------------------------------
# Update mpg values for specific cars (4-cylinder cars get a 5% efficiency boost)
with_transaction(
  get_ducklake_table("cars") |>
    mutate(
      mpg = if_else(cyl == 4, mpg * 1.05, mpg)
    ) |>
    replace_table("cars"),
  author = "Data Engineer",
  commit_message = "Update MPG for 4-cylinder vehicles"
)

# Check version history - should show the new snapshot
list_table_snapshots("cars")

## ----add-columns--------------------------------------------------------------
with_transaction({
  add_table_column("cars", "hp_per_cyl", "DOUBLE")
  add_table_column("cars", "high_performance", "VARCHAR")

  get_ducklake_table("cars") |>
    mutate(
      hp_per_cyl = hp / cyl,
      high_performance = if_else(hp > 200, "Y", "N")
    ) |>
    ducklake_exec()
},
  author = "Data Engineer",
  commit_message = "Add HP per cylinder and performance flag"
)

# Verify new columns exist
get_ducklake_table("cars") |>
  filter(hp > 200) |>
  select(hp, cyl, hp_per_cyl, high_performance)

## ----schema-evolution---------------------------------------------------------
snapshot_before <- max(list_table_snapshots("cars")$snapshot_id)

rename_table_column("cars", from = "high_performance", to = "high_perf_flag")
drop_table_column("cars", "hp_per_cyl")

# Widen fleet's integer key without rewriting any data
set_column_type("fleet", "car_id", "BIGINT")

# Current schema reflects the rename and the drop
get_ducklake_table("cars") |> colnames()

# The pre-change snapshot still shows the old shape
get_ducklake_table_version("cars", snapshot_before) |> colnames()

## ----filter-------------------------------------------------------------------
# Keep only specific rows - creates a versioned snapshot
with_transaction(
  get_ducklake_table("cars") |>
    filter(cyl == 8) |>
    replace_table("cars"),
  author = "Data Engineer",
  commit_message = "Filter to V8 engines only"
)

# Show the filtered table
get_ducklake_table("cars")

# View version history - old versions still accessible via time travel
list_table_snapshots("cars")

## ----time-travel--------------------------------------------------------------
# Get the current version
current <- get_ducklake_table("cars") |> collect()

# List all snapshots to see available versions
snapshots <- list_table_snapshots("cars")
snapshots

# Access a specific previous version by snapshot_id
original_version <- get_ducklake_table_version(
  "cars", 
  snapshots$snapshot_id[1]
) |> collect()

# Compare: how many rows changed?
nrow(current)
nrow(original_version)

## ----cleanup, include=FALSE---------------------------------------------------
detach_ducklake("modifying_tables_lake")