---
title: "Defining Table Metadata with table_info()"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Defining Table Metadata with table_info()}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

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

```
---

## Introduction
In the DBmaps package, a foundational step is understanding the structure of individual tables. The primary goal of this is to define how tables can be transformed or summarized so they can be easily joined with other tables.

This vignette will walk you through using the table_info() function to capture this metadata. We will build a "Table of Tables" for a sample retail dataset where each defined aggregation prepares a table for a subsequent merge by grouping it on a potential join key.

---

## The `table_info()` Function

The table_info() function is the primary tool for creating this metadata. It captures descriptive information about a table and its analytical potential, returning a tidy data.table.

The key argument is key_outcome_specs, a list that defines how a table can be aggregated. A crucial rule in DBmaps is that every aggregation method must include one or more GroupingVariables, as this is what makes the resulting aggregated data mergeable.

---

## Example 1: The Customers Table

For the customers table, we want to define how to count the number of customers by region.
This prepares the data to be potentially joined with another table that also has a region column.
``` {r customers-example, echo = TRUE}
customers <- data.table(
  customer_id = c("C001", "C002", "C003", "C004", "C005"),
  region = c("Asia", "Europe", "Asia", "Americas", "Europe")
)

# Use table_info() to capture the metadata
customers_info_dt <- table_info(
  table_name = "customers",
  source_identifier = "customers.csv",
  identifier_columns = "customer_id",
  key_outcome_specs = list(
    list(
      OutcomeName = "CustomerCount",
      ValueExpression = 1,  # Each row = one customer
      AggregationMethods = list(
        # ** NOTE **: We only define aggregations that group by a variable.
        # The table-wide "TotalCustomers" was removed as it doesn't prepare
        # the table for a join on a key.
        list(
          AggregatedName = "CustomersByRegion",
          AggregationFunction = "sum",
          GroupingVariables = "region"  # Grouped by region
        )
      )
    )
  )
)

print(customers_info_dt)
```
---

## Example 2: The Products Table

Next, for the products table, our goal is to define how to count the number of products within each category.
This would allow us to join this product count to another table containing category-level information.

```{r products-example, echo = TRUE}
products <- data.table(
  product_id = c("P001", "P002", "P003", "P004", "P005", "P006"),
  category   = c("A", "B", "A", "C", "B", "C")
)

# Capture metadata via table_info():
products_info_dt <- table_info(
  table_name = "products",
  source_identifier = "products.csv",
  identifier_columns = "product_id",
  key_outcome_specs = list(
    list(
      OutcomeName = "ProductCount",
      ValueExpression = 1,  # Each row = one product
      AggregationMethods = list(
        list(
          AggregatedName = "ProductsPerCategory",
          AggregationFunction = "sum",
          GroupingVariables = "category"
        )
      )
    )
  )
)

print(products_info_dt)
```
---

## Example 3: The Transactions Table

The transactions table is our central fact table. We will define a "Revenue" outcome that can be aggregated by customer_id or product_id. 
These aggregations are the most common use case, as they prepare the transactions table to be joined with customers or products.
```{r transactions-example, echo = TRUE}
# Tiny in-memory mimic of "transactions.csv":
transactions <- data.table(
  transaction_id = c("T001", "T002", "T003", "T004", "T005"),
  customer_id = c("C001", "C002", "C001", "C003", "C004"),
  product_id = c("P001", "P002", "P001", "P003", "P002"),
  price = c(10, 20, 22, 11, 21),
  quantity = c(1, 2, 1, 3, 2)
)

transactions_info_dt <- table_info(
  table_name = "transactions",
  source_identifier = "transactions.csv",
  identifier_columns = "transaction_id",
  key_outcome_specs = list(
    list(
      OutcomeName = "Revenue",
      ValueExpression = quote(price * quantity),
      AggregationMethods = list(
        list(
          AggregatedName = "RevenueByCustomer",
          AggregationFunction = "sum",
          GroupingVariables = "customer_id"
        ),
        list(
          AggregatedName = "RevenueByProduct",
          AggregationFunction = "sum",
          GroupingVariables = "product_id"
        )
      )
    )
  )
)

print(transactions_info_dt)
```
---

## Assembling a Master Metadata Table

Finally, we can combine the metadata from each table into a single master data.table. 
This master table gives a complete, queryable overview of all the pre-defined, merge-ready aggregations in our database.
```{r all-example, echo = TRUE}
master_metadata_dt <- rbindlist(
  list(customers_info_dt, products_info_dt, transactions_info_dt)
)

# Print the combined master metadata
print(master_metadata_dt)

# Show the structure of the combined data.table
cat("\nStructure of the master metadata data.table:\n")
str(master_metadata_dt)
```