// r8r: rust port of n8n.io
// Initial author: Michael Cochran <mcochran@cochranblock.org>
// Built for: Tanner Woodrum, intended cofounder
// License: Unlicense (public domain dedication, see UNLICENSE)
//
// `sqlite` — execute SQL against an embedded SQLite database. Bundled C
// library (no system sqlite required). Federal-friendly: no daemon, no
// network, single-file storage. Credential: sqlite = { path = "..." }
// where path is either a filesystem path or ":memory:".

use async_trait::async_trait;
use crate::core::error::NodeError;
use crate::core::types::*;
use crate::engine::{ExecuteCtx, Node, NodeFactory};
use rusqlite::types::ValueRef;
use serde_json::{json, Value};
use std::sync::Arc;
use tokio_rusqlite::Connection;

pub struct SqliteNode;

static META: NodeMetadata = NodeMetadata {
    name: "sqlite",
    display_name: "SQLite",
    version: &[1],
    group: &["database"],
    description: "Execute SQL against an embedded SQLite database",
    inputs: 1,
    outputs: 1,
    properties: &[],
    credentials: &[CredentialRef { name: "sqlite", required: true }],
};

#[async_trait]
impl Node for SqliteNode {
    fn metadata(&self) -> &'static NodeMetadata { &META }

    async fn execute(&self, ctx: &mut ExecuteCtx<'_>) -> Result<NodeOutput, NodeError> {
        let path = ctx.credential_field("path")?
            .as_str()
            .ok_or_else(|| NodeError::MissingCredential("sqlite.path".into()))?
            .to_string();
        let query = ctx.param_str("query")?;
        let operation = ctx.node_def.parameters.get("operation")
            .and_then(|v| v.as_str()).unwrap_or("execute_query").to_string();

        let conn = Connection::open(&path).await
            .map_err(|e| NodeError::Other(format!("sqlite open `{}`: {}", path, e)))?;

        match operation.as_str() {
            "execute_query" | "select" => {
                let rows = conn.call(move |c| {
                    let mut stmt = c.prepare(&query)?;
                    let cols: Vec<String> = stmt.column_names().iter().map(|s| (*s).to_string()).collect();
                    let mapped = stmt.query_map([], |row| {
                        let mut obj = serde_json::Map::new();
                        for (i, name) in cols.iter().enumerate() {
                            obj.insert(name.clone(), sqlite_value_to_json(row.get_ref(i)?));
                        }
                        Ok(Value::Object(obj))
                    })?;
                    let mut out = Vec::new();
                    for r in mapped { out.push(r?); }
                    Ok(out)
                }).await
                .map_err(|e| NodeError::Other(format!("sqlite query: {}", e)))?;
                let items: Vec<NodeItem> = rows.into_iter()
                    .map(|j| NodeItem { json: j, ..Default::default() })
                    .collect();
                Ok(vec![items])
            }
            "execute" | "insert" | "update" | "delete" => {
                let n = conn.call(move |c| Ok(c.execute(&query, [])?)).await
                    .map_err(|e| NodeError::Other(format!("sqlite execute: {}", e)))?;
                Ok(vec![vec![NodeItem {
                    json: json!({ "rows_affected": n }),
                    ..Default::default()
                }]])
            }
            "execute_batch" => {
                conn.call(move |c| { c.execute_batch(&query)?; Ok(()) }).await
                    .map_err(|e| NodeError::Other(format!("sqlite batch: {}", e)))?;
                Ok(vec![vec![NodeItem { json: json!({ "ok": true }), ..Default::default() }]])
            }
            other => Err(NodeError::Other(format!("sqlite: unknown operation `{}`", other))),
        }
    }
}

fn sqlite_value_to_json(v: ValueRef<'_>) -> Value {
    match v {
        ValueRef::Null => Value::Null,
        ValueRef::Integer(i) => Value::Number(i.into()),
        ValueRef::Real(f) => serde_json::Number::from_f64(f).map(Value::Number).unwrap_or(Value::Null),
        ValueRef::Text(b) => Value::String(String::from_utf8_lossy(b).into_owned()),
        ValueRef::Blob(b) => Value::String(format!("<blob:{} bytes>", b.len())),
    }
}

inventory::submit! { NodeFactory::new("sqlite", || Arc::new(SqliteNode)) }
