-- Example 1: Basic JavaScript procedure
CREATE OR REPLACE PROCEDURE get_customer_stats(customer_id VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    var sql = `SELECT COUNT(*) as order_count, 
                      SUM(amount) as total_amount
               FROM orders 
               WHERE customer_id = '${customer_id}'`;
    
    var stmt = snowflake.createStatement({sqlText: sql});
    var result = stmt.execute();
    
    if (result.next()) {
        return `Orders: ${result.getColumnValue(1)}, ` +
               `Total: $${result.getColumnValue(2)}`;
    }
    return 'No data found';
$$;

-- Example 2: JavaScript procedure with transaction
CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account VARCHAR,
    to_account VARCHAR,
    amount NUMBER
)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    try {
        // Begin transaction
        snowflake.createStatement({sqlText: "BEGIN TRANSACTION"}).execute();
        
        // Debit source account
        snowflake.createStatement({
            sqlText: `UPDATE accounts SET balance = balance - ${amount} 
                      WHERE account_id = '${from_account}'`
        }).execute();
        
        // Credit destination account
        snowflake.createStatement({
            sqlText: `UPDATE accounts SET balance = balance + ${amount} 
                      WHERE account_id = '${to_account}'`
        }).execute();
        
        // Commit transaction
        snowflake.createStatement({sqlText: "COMMIT"}).execute();
        
        return 'Transfer successful';
    } catch (err) {
        // Rollback on error
        snowflake.createStatement({sqlText: "ROLLBACK"}).execute();
        return 'Transfer failed: ' + err.message;
    }
$$;

-- Example 3: Python procedure with Snowpark
CREATE OR REPLACE PROCEDURE process_data_snowpark()
RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'main'
AS
$$
from snowflake.snowpark import Session
from snowflake.snowpark.functions import col, sum as sum_, count

def main(session: Session):
    # Read data
    df = session.table("raw_data")
    
    # Transform data
    result = df.filter(col("status") == "active") \
               .group_by("category") \
               .agg(
                   count("*").alias("record_count"),
                   sum_(col("amount")).alias("total_amount")
               ) \
               .collect()
    
    # Write results
    session.create_dataframe(result).write.mode("overwrite") \
        .save_as_table("processed_data")
    
    return {"status": "success", "rows_processed": len(result)}
$$;

-- Example 4: SQL procedure with error handling
CREATE OR REPLACE PROCEDURE safe_insert(
    table_name VARCHAR,
    data VARIANT
)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    error_msg VARCHAR;
BEGIN
    -- Attempt insert
    INSERT INTO IDENTIFIER(table_name) 
    SELECT * FROM TABLE(FLATTEN(input => :data));
    
    RETURN 'Insert successful';
    
EXCEPTION
    WHEN EXPRESSION_ERROR THEN
        RETURN 'Data type mismatch error';
    WHEN OTHER THEN
        error_msg := SQLERRM;
        INSERT INTO error_log (procedure_name, error_message, timestamp)
        VALUES ('safe_insert', :error_msg, CURRENT_TIMESTAMP());
        RETURN 'Insert failed: ' || :error_msg;
END;
$$;

-- Example 5: Procedure with retry logic
CREATE OR REPLACE PROCEDURE retry_operation(
    operation_sql VARCHAR,
    max_retries INTEGER DEFAULT 3
)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    retry_count INTEGER DEFAULT 0;
    wait_time INTEGER DEFAULT 1000;
    result VARCHAR;
BEGIN
    WHILE (retry_count < max_retries) DO
        BEGIN
            -- Execute operation
            EXECUTE IMMEDIATE :operation_sql;
            RETURN 'Operation successful';
            
        EXCEPTION
            WHEN OTHER THEN
                retry_count := retry_count + 1;
                IF (retry_count < max_retries) THEN
                    -- Exponential backoff
                    CALL SYSTEM$WAIT(:wait_time);
                    wait_time := wait_time * 2;
                END IF;
        END;
    END WHILE;
    
    RETURN 'Operation failed after ' || :max_retries || ' retries';
END;
$$;

-- Example 6: Call procedure
CALL get_customer_stats('CUST-001');
CALL transfer_funds('ACC-001', 'ACC-002', 1000.00);
CALL process_data_snowpark();
CALL safe_insert('target_table', PARSE_JSON('[{"id": 1, "name": "test"}]'));