---
title: "Installation and Basic Usage"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Installation and Basic Usage}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
header-includes:
  - |
    ```{=html}
    <style>
      body { background-color: #030712; color: #f3f4f6; }
      h1, h2, h3 { color: #f3f4f6; }
      h1 { background: linear-gradient(135deg, #dc2626 0%, #ef4444 50%, #b91c1c 100%); -webkit-background-clip: text; -webkit-text-fill-color: transparent; background-clip: text; }
      a { color: #ef4444; }
      a:hover { color: #dc2626; }
      code { background-color: #111827; color: #9ca3af; border: 1px solid #1f2937; }
      pre { background-color: #111827; color: #9ca3af; border: 1px solid #1f2937; }
      table { border-color: #1f2937; }
      th { background-color: #111827; color: #f3f4f6; border-color: #1f2937; }
      td { border-color: #1f2937; }
      blockquote { border-left-color: #dc2626; background-color: rgba(220, 38, 38, 0.1); }
    </style>
    ```
---

```{r setup, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
```

This vignette provides a guide to `lbugr` and performing basic operations, including creating a database, defining a schema, loading data from various sources, and executing Cypher queries.

## 1. Connecting to a Database

First, load the `lbugr` package and create a connection to a Ladybug database. You can create an in-memory database or specify a path to a database file on disk.

```{r, eval=FALSE}
library(lbugr)
con <- lb_connection(":memory:")
```

## 2. Data Types and Schema Definition

Ladybug supports a rich set of data types. When creating a schema, you need to map your R data types to the corresponding Ladybug `LogicalTypeID`.

### Ladybug to R Data Type Mapping

| **Ladybug `LogicalTypeID`** | **R Type Equivalent** | **Description** |
|-------------------|-------------------|----------------------------------|
| `BOOL` | `logical` | `TRUE`/`FALSE` values |
| `INT64` | `integer` | 64-bit signed integer |
| `DOUBLE` | `numeric` | Double-precision floating-point number |
| `STRING` | `character` | UTF-8 encoded string |
| `TIMESTAMP` | `POSIXct` | Date and time with timezone, stored as microseconds since epoch |
| `DATE` | `Date` | Date (year, month, day) |
| `INTERVAL` | `difftime` | Time interval (e.g., "1 year 2 months 3 days") |
| `UUID` | `character` | Universally Unique Identifier, stored as a string |
| `LIST` | `list` | Ordered collection of values of the same type |
| `MAP` | `list` (named list) | Unordered collection of key-value pairs |

### Creating a Complex Schema

You can define a schema with node and relationship tables using `lb_execute()`. Here's an example of a more complex schema:

```{r, eval=FALSE}
# Create a node table for users with various data types
lb_execute(con, paste("CREATE NODE TABLE User(userID UUID, name STRING,",
                        "age INT64, is_active BOOL, created_at TIMESTAMP,",
                        "last_login DATE, notes STRING[],",
                        "PRIMARY KEY (userID))"))

# Create a node table for products
lb_execute(con, "CREATE NODE TABLE Product(productID INT64, name STRING, PRIMARY KEY (productID))")

# Create a relationship table for user purchases
lb_execute(con, "CREATE REL TABLE Buys(FROM User TO Product, purchase_date DATE)")
```

## 3. Loading Data

You can load data into your Ladybug tables from R data frames or external files like CSV.

### Loading from a Data Frame

Use `lb_copy_from_df()` to load data from an R `data.frame`.

```{r, eval=FALSE}
library(jsonlite)
# Create data frames for nodes and relationships
users <- data.frame(
  userID = c("a1b2c3d4-e5f6-7890-1234-567890abcdef", "b2c3d4e5-f6a7-8901-2345-67890abcdef0"),
  name = c("Alice", "Bob"),
  age = c(35, 45),
  is_active = c(TRUE, FALSE),
  created_at = as.POSIXct(c("2023-01-15 10:30:00", "2022-11-20 14:00:00")),
  last_login = as.Date(c("2023-10-25", "2023-09-30")),
  stringsAsFactors = FALSE
)
# LIST types should be formatted as JSON strings
users$notes <- c(toJSON(c("note1", "note2")), toJSON("note3"))

products <- data.frame(
  productID = c(101, 102),
  name = c("Laptop", "Mouse")
)

buys <- data.frame(
  from_user = c("a1b2c3d4-e5f6-7890-1234-567890abcdef", "b2c3d4e5-f6a7-8901-2345-67890abcdef0"),
  to_product = c(101, 102),
  purchase_date = as.Date(c("2023-02-20", "2023-03-15"))
)

# Load data into Ladybug
lb_copy_from_df(con, users, "User")
lb_copy_from_df(con, products, "Product")
lb_copy_from_df(con, buys, "Buys")
```

### Loading from a CSV File

Use `lb_copy_from_csv()` to load data from a CSV file. For this to work, the file should be in the current working directory.

```{r, eval=FALSE}
# Create a CSV file in the project's root directory
csv_filename <- "products.csv"
write.csv(data.frame(productID = c(103, 104), name = c("Keyboard", "Monitor")),
          csv_filename, row.names = FALSE)

# Load data from the CSV file using just the filename
lb_copy_from_csv(con, csv_filename, "Product")

# Clean up the created file
unlink(csv_filename)
```

## 4. Executing Queries and Converting Results

You can execute Cypher queries using `lb_execute()` and convert the results into various R formats.

```{r, eval=FALSE}
# Execute a query to get users and their purchases
query_result <- lb_execute(con, "MATCH (u:User)-[b:Buys]->(p:Product) RETURN u.name, p.name, b.purchase_date")
```

### A Note on Query Results

The `QueryResult` object returned by `lb_execute()` acts as an iterator over the results. This means it can only be consumed once. Functions like `as.data.frame()`, `as_tibble()`, `lb_get_all()`, and the graph conversion functions will exhaust this iterator.

### Convert to Data Frame or Tibble

```{r, eval=FALSE}
# Convert to a data frame
df_result <- as.data.frame(query_result)
print(df_result)
#>      u.name p.name purchase_date
#> 1     Alice Laptop    2023-02-20
#> 2       Bob  Mouse    2023-03-15

# Convert to a tibble
library(tibble)
query_result <- lb_execute(con, "MATCH (u:User)-[b:Buys]->(p:Product) RETURN u.name, p.name, b.purchase_date")
tibble_result <- as_tibble(query_result)
print(tibble_result)
#> # A tibble: 2 x 3
#>   u.name p.name purchase_date
#>   <chr>  <chr>  <date>
#> 1 Alice  Laptop 2023-02-20
#> 2 Bob    Mouse  2023-03-15
```

### Use Query Results returned as list

```{r, eval=FALSE}
query_result <- lb_execute(con, "MATCH (u:User)-[b:Buys]->(p:Product) RETURN u.name, p.name, b.purchase_date")

result <- lb_get_all(query_result)
print(result)
#> [[1]]
#> [[1]]$u.name
#> [1] "Alice"
#> ...

# only fetch 1. result 
query_result <- lb_execute(con, "MATCH (u:User)-[b:Buys]->(p:Product) RETURN u.name, p.name, b.purchase_date")

result <- lb_get_n(query_result, 1)
print(result)
#> [[1]]
#> [[1]]$u.name
#> [1] "Alice"
#> ...

#Fetch next result
result <- lb_get_next(query_result)
print(result)
#> $u.name
#> [1] "Bob"
#> ...
```

### Convert to Graph Objects

For queries that return graph structures, you can convert the results into graph objects from packages like `igraph` or `tidygraph`. To do this, the query must return the node and relationship variables themselves, not just their properties.

```{r, eval=FALSE}
# Execute a query that returns a graph structure 
graph_query_result <- lb_execute(con, "MATCH (u:User)-[b:Buys]->(p:Product) RETURN u, p, b")
igraph_obj <- as_igraph(graph_query_result)
print(igraph_obj)
#> IGRAPH UN-- 3 2 -- 
#> + attr: name (v/c)
#> + edges (vertex names):
#> [1] Alice->Laptop Bob  ->Mouse

plot(igraph_obj,
     vertex.color = "#dc2626",
     vertex.label.color = "#f3f4f6",
     vertex.label.font = 2,
     edge.color = "#9ca3af",
     edge.arrow.size = 0.8,
     edge.arrow.width = 0.5,
     bg = "#030712",
     main = "igraph: User Purchases")

# Convert to a tidygraph object
tidygraph_obj <- as_tidygraph(graph_query_result)
print(tidygraph_obj)
#> # A tbl_graph: 3 nodes and 2 edges
#> #
#> # A directed acyclic simple graph with 3 nodes and 2 edges
#> #
#> # Node Data: 3 x 1 (active)
#>   name 
#>   <chr>
#> 1 Alice
#> 2 Bob  
#> 3 Laptop
#> #
#> # Edge Data: 2 x 3
#>    from    to purchase_date
#>   <int> <int> <date>
#> 1     1     3 2023-02-20
#> 2     2     4 2023-03-15

ggraph::ggraph(tidygraph_obj, layout = "kk") +
  ggraph::geom_edge_link(color = "#9ca3af", arrow = grid::arrow(angle = 30, length = grid::unit(3, "mm"))) +
  ggraph::geom_node_point(color = "#dc2626", size = 8) +
  ggraph::geom_node_text(ggplot2::aes(label = name), color = "#f3f4f6", size = 4, vjust = -1) +
  ggplot2::theme_void() +
  ggplot2::theme(plot.background = ggplot2::element_rect(fill = "#030712", color = NA))