what_core/database/

mod.rs

//! Database Adapter — unified interface for SQLite, Cloudflare D1, and Supabase backends
//!
//! Configured via `[database]` in `what.toml`. Defaults to SQLite when no config is present.

use serde_json::Value;
use std::collections::HashMap;

use crate::Result;

mod sqlite;
pub use sqlite::SqliteDatabase;

pub mod d1;
pub use d1::D1Database;

pub mod supabase;
pub use supabase::SupabaseDatabase;

/// Query options for collection retrieval
#[derive(Debug, Default, Clone)]
pub struct CollectionQuery {
    /// Sort expression: "field:asc" or "field:desc"
    pub sort: Option<String>,
    /// Filter expression: "field=value", "field>N", joined by "&" (AND) or "," (OR)
    pub filter: Option<String>,
    /// Full-text search term
    pub search: Option<String>,
    /// Fields to search in (comma-separated)
    pub search_fields: Option<String>,
    /// Max items to return
    pub limit: Option<usize>,
    /// Items to skip
    pub offset: Option<usize>,
    /// Mandatory scope filters AND-ed into every query regardless of the
    /// user-supplied `filter`. Set by authorization policies (owner/tenant
    /// scoping) — the user cannot widen past these. Each string uses the same
    /// mini-language as `filter` (comma = OR, `&` = AND).
    pub forced_filters: Vec<String>,
}

/// Unified database adapter — dispatches to SQLite, D1, or Supabase
#[derive(Clone)]
pub enum DatabaseAdapter {
    Sqlite(SqliteDatabase),
    D1(D1Database),
    Supabase(SupabaseDatabase),
}

impl DatabaseAdapter {
    /// Get all items from a collection (with optional query parameters)
    pub async fn get_collection(&self, name: &str) -> Option<Vec<Value>> {
        match self {
            Self::Sqlite(db) => db
                .get_collection(name)
                .await
                .map_err(|e| {
                    tracing::warn!("SQLite get_collection '{}' error: {}", name, e);
                    e
                })
                .ok(),
            Self::D1(db) => db
                .get_collection(name)
                .await
                .map_err(|e| {
                    tracing::warn!("D1 get_collection '{}' error: {}", name, e);
                    e
                })
                .ok(),
            Self::Supabase(db) => db
                .get_collection(name)
                .await
                .map_err(|e| {
                    tracing::warn!("Supabase get_collection '{}' error: {}", name, e);
                    e
                })
                .ok(),
        }
    }

    /// Get items with query options (sort, filter, search, limit, offset)
    pub async fn query_collection(
        &self,
        name: &str,
        query: &CollectionQuery,
    ) -> Option<Vec<Value>> {
        match self {
            Self::Sqlite(db) => db
                .query_collection(name, query)
                .await
                .map_err(|e| {
                    tracing::warn!("SQLite query_collection '{}' error: {}", name, e);
                    e
                })
                .ok(),
            Self::D1(db) => db
                .query_collection(name, query)
                .await
                .map_err(|e| {
                    tracing::warn!("D1 query_collection '{}' error: {}", name, e);
                    e
                })
                .ok(),
            Self::Supabase(db) => db
                .query_collection(name, query)
                .await
                .map_err(|e| {
                    tracing::warn!("Supabase query_collection '{}' error: {}", name, e);
                    e
                })
                .ok(),
        }
    }

    /// Find items by a field value
    pub async fn find_by(&self, collection: &str, field: &str, value: &Value) -> Vec<Value> {
        match self {
            Self::Sqlite(db) => db
                .find_by(collection, field, value)
                .await
                .unwrap_or_else(|e| {
                    tracing::warn!("SQLite find_by '{}.{}' error: {}", collection, field, e);
                    Vec::new()
                }),
            Self::D1(db) => db
                .find_by(collection, field, value)
                .await
                .unwrap_or_else(|e| {
                    tracing::warn!("D1 find_by '{}.{}' error: {}", collection, field, e);
                    Vec::new()
                }),
            Self::Supabase(db) => db
                .find_by(collection, field, value)
                .await
                .unwrap_or_else(|e| {
                    tracing::warn!("Supabase find_by '{}.{}' error: {}", collection, field, e);
                    Vec::new()
                }),
        }
    }

    /// Find a single item by field value
    pub async fn find_one_by(&self, collection: &str, field: &str, value: &Value) -> Option<Value> {
        match self {
            Self::Sqlite(db) => db
                .find_one_by(collection, field, value)
                .await
                .map_err(|e| {
                    tracing::warn!("SQLite find_one_by '{}.{}' error: {}", collection, field, e);
                    e
                })
                .ok()
                .flatten(),
            Self::D1(db) => db
                .find_one_by(collection, field, value)
                .await
                .map_err(|e| {
                    tracing::warn!("D1 find_one_by '{}.{}' error: {}", collection, field, e);
                    e
                })
                .ok()
                .flatten(),
            Self::Supabase(db) => db
                .find_one_by(collection, field, value)
                .await
                .map_err(|e| {
                    tracing::warn!(
                        "Supabase find_one_by '{}.{}' error: {}",
                        collection,
                        field,
                        e
                    );
                    e
                })
                .ok()
                .flatten(),
        }
    }

    /// Create an item in a collection
    pub async fn create(&self, collection: &str, item: Value) -> Result<Value> {
        match self {
            Self::Sqlite(db) => db.create(collection, item).await,
            Self::D1(db) => db.create(collection, item).await,
            Self::Supabase(db) => db.create(collection, item).await,
        }
    }

    /// Update an item by ID
    pub async fn update(
        &self,
        collection: &str,
        id: &Value,
        updates: Value,
    ) -> Result<Option<Value>> {
        match self {
            Self::Sqlite(db) => db.update(collection, id, updates).await,
            Self::D1(db) => db.update(collection, id, updates).await,
            Self::Supabase(db) => db.update(collection, id, updates).await,
        }
    }

    /// Delete an item by ID
    pub async fn delete(&self, collection: &str, id: &Value) -> Result<bool> {
        match self {
            Self::Sqlite(db) => db.delete(collection, id).await,
            Self::D1(db) => db.delete(collection, id).await,
            Self::Supabase(db) => db.delete(collection, id).await,
        }
    }

    /// Set a key-value pair
    pub async fn set(&self, key: &str, value: Value) -> Result<()> {
        match self {
            Self::Sqlite(db) => db.set(key, value).await,
            Self::D1(db) => db.set(key, value).await,
            Self::Supabase(db) => db.set(key, value).await,
        }
    }

    /// Get a value by key
    pub async fn get(&self, key: &str) -> Option<Value> {
        match self {
            Self::Sqlite(db) => db
                .get(key)
                .await
                .map_err(|e| {
                    tracing::warn!("SQLite get '{}' error: {}", key, e);
                    e
                })
                .ok()
                .flatten(),
            Self::D1(db) => db
                .get(key)
                .await
                .map_err(|e| {
                    tracing::warn!("D1 get '{}' error: {}", key, e);
                    e
                })
                .ok()
                .flatten(),
            Self::Supabase(db) => db
                .get(key)
                .await
                .map_err(|e| {
                    tracing::warn!("Supabase get '{}' error: {}", key, e);
                    e
                })
                .ok()
                .flatten(),
        }
    }

    /// Get all data as a template context
    pub async fn as_context(&self) -> HashMap<String, Value> {
        match self {
            Self::Sqlite(db) => db.as_context().await.unwrap_or_else(|e| {
                tracing::warn!("SQLite as_context error: {}", e);
                HashMap::new()
            }),
            Self::D1(db) => db.as_context().await.unwrap_or_else(|e| {
                tracing::warn!("D1 as_context error: {}", e);
                HashMap::new()
            }),
            Self::Supabase(db) => db.as_context().await.unwrap_or_else(|e| {
                tracing::warn!("Supabase as_context error: {}", e);
                HashMap::new()
            }),
        }
    }

    /// Replace an entire collection
    pub async fn set_collection(&self, name: &str, items: Vec<Value>) -> Result<()> {
        match self {
            Self::Sqlite(db) => db.set_collection(name, items).await,
            Self::D1(db) => db.set_collection(name, items).await,
            Self::Supabase(db) => db.set_collection(name, items).await,
        }
    }

    /// Load collection from a file path (no-op for database backends)
    pub async fn load_collection(
        &self,
        _name: &str,
        _path: impl AsRef<std::path::Path>,
    ) -> Result<()> {
        Ok(())
    }

    /// Atomically modify a key-value
    pub async fn atomic_modify<F>(&self, key: &str, f: F) -> Result<Value>
    where
        F: FnOnce(Option<&Value>) -> Value + Send + 'static,
    {
        match self {
            Self::Sqlite(db) => db.atomic_modify(key, f).await,
            Self::D1(db) => db.atomic_modify(key, f).await,
            Self::Supabase(db) => db.atomic_modify(key, f).await,
        }
    }

    /// Delete a key-value pair
    pub async fn remove(&self, key: &str) -> Result<Option<Value>> {
        match self {
            Self::Sqlite(db) => db.remove(key).await,
            Self::D1(db) => db.remove(key).await,
            Self::Supabase(db) => db.remove(key).await,
        }
    }
}

// ---------------------------------------------------------------------------
// In-memory query helpers (used by tests)
// ---------------------------------------------------------------------------

#[cfg(test)]
fn apply_query_in_memory(mut items: Vec<Value>, query: &CollectionQuery) -> Vec<Value> {
    // Filter
    if let Some(ref filter_expr) = query.filter {
        items = apply_filter(&items, filter_expr);
    }

    // Search
    if let Some(ref search_term) = query.search {
        if !search_term.is_empty() {
            let fields: Vec<&str> = query
                .search_fields
                .as_deref()
                .map(|s| s.split(',').collect())
                .unwrap_or_default();
            let term_lower = search_term.to_lowercase();
            items.retain(|item| {
                if fields.is_empty() {
                    // Search all string fields
                    if let Value::Object(map) = item {
                        map.values().any(|v| {
                            v.as_str()
                                .map(|s| s.to_lowercase().contains(&term_lower))
                                .unwrap_or(false)
                        })
                    } else {
                        false
                    }
                } else {
                    fields.iter().any(|field| {
                        item.get(field.trim())
                            .and_then(|v| v.as_str())
                            .map(|s| s.to_lowercase().contains(&term_lower))
                            .unwrap_or(false)
                    })
                }
            });
        }
    }

    // Sort
    if let Some(ref sort_expr) = query.sort {
        let (field, descending) = parse_sort_expr(sort_expr);
        items.sort_by(|a, b| {
            let va = a.get(&field);
            let vb = b.get(&field);
            let cmp = compare_json_values(va, vb);
            if descending { cmp.reverse() } else { cmp }
        });
    }

    // Offset
    if let Some(offset) = query.offset {
        if offset < items.len() {
            items = items[offset..].to_vec();
        } else {
            items.clear();
        }
    }

    // Limit
    if let Some(limit) = query.limit {
        items.truncate(limit);
    }

    items
}

#[cfg(test)]
fn parse_sort_expr(expr: &str) -> (String, bool) {
    if let Some((field, dir)) = expr.rsplit_once(':') {
        (field.to_string(), dir.eq_ignore_ascii_case("desc"))
    } else {
        (expr.to_string(), false)
    }
}

#[cfg(test)]
fn compare_json_values(a: Option<&Value>, b: Option<&Value>) -> std::cmp::Ordering {
    match (a, b) {
        (None, None) => std::cmp::Ordering::Equal,
        (None, Some(_)) => std::cmp::Ordering::Less,
        (Some(_), None) => std::cmp::Ordering::Greater,
        (Some(a), Some(b)) => {
            // Try numeric comparison first
            if let (Some(na), Some(nb)) = (a.as_f64(), b.as_f64()) {
                return na.partial_cmp(&nb).unwrap_or(std::cmp::Ordering::Equal);
            }
            // Fall back to string comparison
            let sa = a.as_str().unwrap_or("");
            let sb = b.as_str().unwrap_or("");
            sa.cmp(sb)
        }
    }
}

#[cfg(test)]
fn apply_filter(items: &[Value], filter_expr: &str) -> Vec<Value> {
    // Support OR (comma) at top level, AND (&) within groups
    let or_groups: Vec<&str> = filter_expr.split(',').collect();

    items
        .iter()
        .filter(|item| {
            or_groups.iter().any(|group| {
                let and_conditions: Vec<&str> = group.split('&').collect();
                and_conditions.iter().all(|cond| {
                    let cond = cond.trim();
                    evaluate_condition(item, cond)
                })
            })
        })
        .cloned()
        .collect()
}

#[cfg(test)]
fn evaluate_condition(item: &Value, cond: &str) -> bool {
    if let Some((field, val)) = cond.split_once(">=") {
        let field = field.trim();
        let val = val.trim();
        item.get(field)
            .map(|v| {
                if let (Some(n), Ok(target)) = (v.as_f64(), val.parse::<f64>()) {
                    n >= target
                } else {
                    v.as_str().unwrap_or("") >= val
                }
            })
            .unwrap_or(false)
    } else if let Some((field, val)) = cond.split_once("<=") {
        let field = field.trim();
        let val = val.trim();
        item.get(field)
            .map(|v| {
                if let (Some(n), Ok(target)) = (v.as_f64(), val.parse::<f64>()) {
                    n <= target
                } else {
                    v.as_str().unwrap_or("") <= val
                }
            })
            .unwrap_or(false)
    } else if let Some((field, val)) = cond.split_once('>') {
        let field = field.trim();
        let val = val.trim();
        item.get(field)
            .map(|v| {
                if let (Some(n), Ok(target)) = (v.as_f64(), val.parse::<f64>()) {
                    n > target
                } else {
                    v.as_str().unwrap_or("") > val
                }
            })
            .unwrap_or(false)
    } else if let Some((field, val)) = cond.split_once('<') {
        let field = field.trim();
        let val = val.trim();
        item.get(field)
            .map(|v| {
                if let (Some(n), Ok(target)) = (v.as_f64(), val.parse::<f64>()) {
                    n < target
                } else {
                    v.as_str().unwrap_or("") < val
                }
            })
            .unwrap_or(false)
    } else if let Some((field, val)) = cond.split_once('=') {
        let field = field.trim();
        let val = val.trim();
        item.get(field)
            .map(|v| match v {
                Value::String(s) => s == val,
                Value::Number(n) => n.to_string() == val,
                Value::Bool(b) => b.to_string() == val,
                _ => false,
            })
            .unwrap_or(false)
    } else {
        true // Unknown condition format — don't filter
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use serde_json::json;

    #[test]
    fn test_sort_asc() {
        let items = vec![
            json!({"name": "Charlie", "age": 30}),
            json!({"name": "Alice", "age": 25}),
            json!({"name": "Bob", "age": 28}),
        ];
        let query = CollectionQuery {
            sort: Some("name:asc".to_string()),
            ..Default::default()
        };
        let result = apply_query_in_memory(items, &query);
        assert_eq!(result[0]["name"], "Alice");
        assert_eq!(result[1]["name"], "Bob");
        assert_eq!(result[2]["name"], "Charlie");
    }

    #[test]
    fn test_sort_desc() {
        let items = vec![
            json!({"name": "Alice", "age": 25}),
            json!({"name": "Bob", "age": 28}),
            json!({"name": "Charlie", "age": 30}),
        ];
        let query = CollectionQuery {
            sort: Some("age:desc".to_string()),
            ..Default::default()
        };
        let result = apply_query_in_memory(items, &query);
        assert_eq!(result[0]["age"], 30);
        assert_eq!(result[1]["age"], 28);
        assert_eq!(result[2]["age"], 25);
    }

    #[test]
    fn test_filter_exact() {
        let items = vec![
            json!({"status": "published", "title": "A"}),
            json!({"status": "draft", "title": "B"}),
            json!({"status": "published", "title": "C"}),
        ];
        let query = CollectionQuery {
            filter: Some("status=published".to_string()),
            ..Default::default()
        };
        let result = apply_query_in_memory(items, &query);
        assert_eq!(result.len(), 2);
    }

    #[test]
    fn test_filter_comparison() {
        let items = vec![
            json!({"price": 10}),
            json!({"price": 25}),
            json!({"price": 50}),
        ];
        let query = CollectionQuery {
            filter: Some("price>20".to_string()),
            ..Default::default()
        };
        let result = apply_query_in_memory(items, &query);
        assert_eq!(result.len(), 2);
    }

    #[test]
    fn test_filter_and() {
        let items = vec![
            json!({"status": "published", "category": "tech"}),
            json!({"status": "published", "category": "food"}),
            json!({"status": "draft", "category": "tech"}),
        ];
        let query = CollectionQuery {
            filter: Some("status=published&category=tech".to_string()),
            ..Default::default()
        };
        let result = apply_query_in_memory(items, &query);
        assert_eq!(result.len(), 1);
    }

    #[test]
    fn test_filter_or() {
        let items = vec![
            json!({"status": "published"}),
            json!({"status": "draft"}),
            json!({"status": "archived"}),
        ];
        let query = CollectionQuery {
            filter: Some("status=published,status=draft".to_string()),
            ..Default::default()
        };
        let result = apply_query_in_memory(items, &query);
        assert_eq!(result.len(), 2);
    }

    #[test]
    fn test_search() {
        let items = vec![
            json!({"title": "Rust Programming", "content": "Learn Rust"}),
            json!({"title": "Python Basics", "content": "Learn Python"}),
            json!({"title": "Rust Web Dev", "content": "Build web apps"}),
        ];
        let query = CollectionQuery {
            search: Some("rust".to_string()),
            search_fields: Some("title,content".to_string()),
            ..Default::default()
        };
        let result = apply_query_in_memory(items, &query);
        assert_eq!(result.len(), 2);
    }

    #[test]
    fn test_limit_offset() {
        let items: Vec<Value> = (1..=10).map(|i| json!({"n": i})).collect();
        let query = CollectionQuery {
            offset: Some(3),
            limit: Some(2),
            ..Default::default()
        };
        let result = apply_query_in_memory(items, &query);
        assert_eq!(result.len(), 2);
        assert_eq!(result[0]["n"], 4);
        assert_eq!(result[1]["n"], 5);
    }

    #[test]
    fn test_combined_query() {
        let items = vec![
            json!({"title": "Rust A", "views": 100, "status": "published"}),
            json!({"title": "Rust B", "views": 50, "status": "draft"}),
            json!({"title": "Python", "views": 200, "status": "published"}),
            json!({"title": "Rust C", "views": 150, "status": "published"}),
        ];
        let query = CollectionQuery {
            filter: Some("status=published".to_string()),
            sort: Some("views:desc".to_string()),
            limit: Some(2),
            ..Default::default()
        };
        let result = apply_query_in_memory(items, &query);
        assert_eq!(result.len(), 2);
        assert_eq!(result[0]["title"], "Python");
        assert_eq!(result[1]["title"], "Rust C");
    }
}