sqry-lang-sql 12.1.6

//! Targeted tests for SQL procedure/function and trigger graph extraction.
//!
//! These tests verify the fixes for:
//! - HIGH: Procedure capture index mismatch (both functions and procedures)
//! - HIGH: Trigger table capture index mismatch
//! - symbol extraction for functions/procedures/triggers

use sqry_core::graph::{GraphBuilder, unified::StagingGraph};
use sqry_lang_sql::relations::SqlGraphBuilder;
use tree_sitter::Parser;

fn parse_sql(sql: &str) -> tree_sitter::Tree {
    let mut parser = Parser::new();
    parser
        .set_language(&tree_sitter_sequel::LANGUAGE.into())
        .expect("Failed to set SQL language");
    parser.parse(sql, None).expect("Failed to parse SQL")
}

#[test]
fn test_function_extraction_in_graph() {
    let sql = r"
        CREATE FUNCTION public.calculate_total(order_id INT)
        RETURNS DECIMAL(10,2)
        AS $$
            SELECT SUM(amount) FROM order_items WHERE order_id = $1;
        $$ LANGUAGE sql;
    ";

    let tree = parse_sql(sql);
    let mut staging = StagingGraph::new();
    let builder = SqlGraphBuilder::new();

    builder
        .build_graph(
            &tree,
            sql.as_bytes(),
            std::path::Path::new("test.sql"),
            &mut staging,
        )
        .expect("Graph building should succeed");

    // Verifies that build_graph produces a valid staging graph without errors.
}

#[test]
fn test_stored_procedure_as_function() {
    // Note: tree-sitter-sequel doesn't have a separate CREATE PROCEDURE node type
    // Stored procedures are represented as CREATE FUNCTION in the grammar
    let sql = r"
        CREATE FUNCTION public.update_inventory(product_id INT, qty INT)
        RETURNS VOID
        AS $$
        BEGIN
            UPDATE products SET quantity = quantity + qty WHERE id = product_id;
        END;
        $$ LANGUAGE plpgsql;
    ";

    let tree = parse_sql(sql);
    let mut staging = StagingGraph::new();
    let builder = SqlGraphBuilder::new();

    builder
        .build_graph(
            &tree,
            sql.as_bytes(),
            std::path::Path::new("test.sql"),
            &mut staging,
        )
        .expect("Graph building should succeed");

    // Verifies that build_graph produces a valid staging graph without errors.
}

#[test]
fn test_trigger_with_table_edge() {
    let sql = r"
        CREATE TRIGGER audit_log_trigger
        AFTER INSERT ON users
        FOR EACH ROW
        EXECUTE FUNCTION log_user_changes();
    ";

    let tree = parse_sql(sql);
    eprintln!("Trigger tree: {}", tree.root_node().to_sexp());
    let mut staging = StagingGraph::new();
    let builder = SqlGraphBuilder::new();

    builder
        .build_graph(
            &tree,
            sql.as_bytes(),
            std::path::Path::new("test.sql"),
            &mut staging,
        )
        .expect("Graph building should succeed");

    // Verifies that build_graph produces a valid staging graph without errors.
}

#[test]
fn test_multiple_functions_and_triggers() {
    let sql = r"
        CREATE FUNCTION get_user(user_id INT) RETURNS TABLE (name TEXT) AS $$
            SELECT name FROM users WHERE id = user_id;
        $$ LANGUAGE sql;

        CREATE FUNCTION delete_user(user_id INT) RETURNS VOID AS $$
            DELETE FROM users WHERE id = user_id;
        $$ LANGUAGE sql;

        CREATE TRIGGER check_balance
        BEFORE UPDATE ON accounts
        FOR EACH ROW
        EXECUTE FUNCTION validate_balance();
    ";

    let tree = parse_sql(sql);
    let mut staging = StagingGraph::new();
    let builder = SqlGraphBuilder::new();

    builder
        .build_graph(
            &tree,
            sql.as_bytes(),
            std::path::Path::new("test.sql"),
            &mut staging,
        )
        .expect("Graph building should succeed");

    // Verifies that build_graph produces a valid staging graph without errors.
}

#[test]
fn test_table_read_edge_from_select() {
    let sql = r"
        CREATE FUNCTION get_account_balance(account_id INT)
        RETURNS DECIMAL(10,2)
        AS $$
            SELECT balance FROM accounts WHERE id = account_id;
        $$ LANGUAGE sql;
    ";

    let tree = parse_sql(sql);
    let mut staging = StagingGraph::new();
    let builder = SqlGraphBuilder::new();

    builder
        .build_graph(
            &tree,
            sql.as_bytes(),
            std::path::Path::new("test.sql"),
            &mut staging,
        )
        .expect("Graph building should succeed");

    // Verifies that build_graph produces a valid staging graph without errors.
}

#[test]
fn test_table_write_edge_from_insert() {
    let sql = r"
        CREATE FUNCTION create_account(initial_balance DECIMAL)
        RETURNS VOID
        AS $$
            INSERT INTO accounts (balance) VALUES (initial_balance);
        $$ LANGUAGE sql;
    ";

    let tree = parse_sql(sql);
    let mut staging = StagingGraph::new();
    let builder = SqlGraphBuilder::new();

    builder
        .build_graph(
            &tree,
            sql.as_bytes(),
            std::path::Path::new("test.sql"),
            &mut staging,
        )
        .expect("Graph building should succeed");

    // Verifies that build_graph produces a valid staging graph without errors.
}

#[test]
fn test_table_write_edge_from_update() {
    let sql = r"
        CREATE FUNCTION update_balance(account_id INT, new_balance DECIMAL)
        RETURNS VOID
        AS $$
            UPDATE accounts SET balance = new_balance WHERE id = account_id;
        $$ LANGUAGE sql;
    ";

    let tree = parse_sql(sql);
    let mut staging = StagingGraph::new();
    let builder = SqlGraphBuilder::new();

    builder
        .build_graph(
            &tree,
            sql.as_bytes(),
            std::path::Path::new("test.sql"),
            &mut staging,
        )
        .expect("Graph building should succeed");

    // Verifies that build_graph produces a valid staging graph without errors.
}

#[test]
fn test_table_write_edge_from_delete() {
    let sql = r"
        CREATE FUNCTION close_account(account_id INT)
        RETURNS VOID
        AS $$
            DELETE FROM accounts WHERE id = account_id;
        $$ LANGUAGE sql;
    ";

    let tree = parse_sql(sql);
    let mut staging = StagingGraph::new();
    let builder = SqlGraphBuilder::new();

    builder
        .build_graph(
            &tree,
            sql.as_bytes(),
            std::path::Path::new("test.sql"),
            &mut staging,
        )
        .expect("Graph building should succeed");

    // Verifies that build_graph produces a valid staging graph without errors.
}

#[test]
fn test_function_with_multiple_table_operations() {
    // NOTE: tree-sitter-sequel grammar requires BEGIN...END; blocks for multiple statements
    // in dollar-quoted function bodies. Without BEGIN...END, only a single statement is
    // supported and additional statements generate ERROR nodes.
    //
    // See tree-sitter-sequel grammar.json:
    // - function_body with BEGIN...END supports multiple _function_body_statement
    // - function_body without BEGIN...END supports only one _function_body_statement
    let sql = r"
        CREATE FUNCTION transfer_funds(from_id INT, to_id INT, amount DECIMAL)
        RETURNS VOID
        AS $$
        BEGIN
            SELECT balance FROM accounts WHERE id = from_id;
            UPDATE accounts SET balance = balance - amount WHERE id = from_id;
            UPDATE accounts SET balance = balance + amount WHERE id = to_id;
            INSERT INTO transactions (from_account, to_account, amount) VALUES (from_id, to_id, amount);
        END;
        $$ LANGUAGE plpgsql;
    ";

    let tree = parse_sql(sql);
    let mut staging = StagingGraph::new();
    let builder = SqlGraphBuilder::new();

    builder
        .build_graph(
            &tree,
            sql.as_bytes(),
            std::path::Path::new("test.sql"),
            &mut staging,
        )
        .expect("Graph building should succeed");

    // Verifies that build_graph produces a valid staging graph without errors.
}