CALL functions and external services

SELECT CALL(FUNCTION_NAME, parameters) AS result;
SELECT CALL(FUNCTION_NAME "parameter" FROM (subquery)) AS result;

-- Basic SSE event send
SELECT CALL(SSE_SEND "notification_channel" FROM (
    SELECT "New order #" || &order_id || " received!" AS message
    TYPE OBJECT
)) AS sent;
 
-- Sending a complex object
SELECT CALL(SSE_SEND "desktop_notifications" FROM (
    SELECT 
        "order_update" AS event_type,
        &order_id AS order_id,
        "processed" AS status,
        NOW() AS timestamp
    TYPE OBJECT
)) AS notification_result;
 
-- Multi-line message (from the q68.sql example)
SELECT CALL(SSE_SEND "Desktop" FROM (
    SELECT "title 📊 Quarterly report Q1 2026
    folder Work/Reports/2027
    template quarterly-report
    reference Q1 Goals
    reference Budget 2026
    tag report
    tag q1
    tag finance
 
    # Key achievements
 
    - 95% of tasks completed
    - Revenue growth of 23%
    - Launched 3 new products
 
    # Plans for Q2
 
    - Team expansion
    - Entering new markets" AS message TYPE OBJECT
)) AS result
TYPE OBJECT;

-- Different channels for different event types
SELECT CALL(SSE_SEND "user_notifications" FROM (
    SELECT 
        "user_login" AS event,
        &user.name AS username,
        &user.ip AS ip_address
    TYPE OBJECT
)) AS user_event;
 
SELECT CALL(SSE_SEND "system_alerts" FROM (
    SELECT 
        "high_memory_usage" AS alert_type,
        &metrics.memory_percent AS memory_usage,
        "critical" AS severity
    TYPE OBJECT
)) AS system_alert;
 
SELECT CALL(SSE_SEND "business_metrics" FROM (
    SELECT 
        "daily_sales" AS metric,
        COUNT(*) AS orders_count,
        SUM(total_amount) AS revenue
    FROM dictionary.orders
    WHERE DATE(created_at) = CURRENT_DATE
    TYPE OBJECT
)) AS metrics_update;

-- Simple notification to Telegram
SELECT CALL(TG_SEND_MESSAGE, 
    '{"chat_id": ' || &chat_id || ', "text": "Order ready!"}') AS sent;
 
-- Notification with formatting
SELECT CALL(TG_SEND_MESSAGE, 
    '{"chat_id": ' || &admin_chat || 
    ', "text": "🚨 New order #' || &order_id || 
    '\n💰 Amount: ' || &amount || ' RUB' ||
    '\n📧 Customer: ' || &customer_email || '"}') AS notification;
 
-- Notification with buttons
SELECT CALL(TG_SEND_MESSAGE,
    '{"chat_id": ' || &chat_id ||
    ', "text": "Confirm the operation:",
    "reply_markup": {
        "inline_keyboard": [[
            {"text": "✅ Confirm", "callback_data": "confirm_' || &operation_id || '"},
            {"text": "❌ Reject", "callback_data": "reject_' || &operation_id || '"}
        ]]
    }}') AS message_with_buttons;

-- Updating order status
SELECT CALL(TG_EDIT_MESSAGE,
    '{"chat_id": ' || &chat_id ||
    ', "message_id": ' || &message_id ||
    ', "text": "✅ Order #' || &order_id || ' completed!"}') AS updated;
 
-- Update with new buttons
SELECT CALL(TG_EDIT_MESSAGE,
    '{"chat_id": ' || &chat_id ||
    ', "message_id": ' || &message_id ||
    ', "text": "✅ Operation completed",
    "reply_markup": {
        "inline_keyboard": [[
            {"text": "📊 Show statistics", "callback_data": "stats_' || &operation_id || '"}
        ]]
    }}') AS updated_with_buttons;

-- Simple AI request
SELECT CALL(AI_CHAT_COMPLETION, 
    '{"model": "gpt-4", "prompt": "' || &question || '"}') AS ai_response;
 
-- AI analysis of order data
SELECT CALL(AI_CHAT_COMPLETION,
    '{"model": "gpt-4", 
      "prompt": "Analyze the following order data and give recommendations: ' || 
                'Items: ' || &items_count ||
                ', Amount: ' || &total_amount ||
                ', Region: ' || &region || '",
      "max_tokens": 500}') AS analysis;
 
-- AI generation of a product description (no CTE — subquery in FROM)
SELECT CALL(AI_CHAT_COMPLETION,
    '{"model": "gpt-4",
      "prompt": "Create an appealing description for the product: ' ||
                'Name: ' || pd.name ||
                ', Category: ' || pd.category || 
                ', Price: ' || pd.price ||
                ', Specifications: ' || pd.specifications || '",
      "max_tokens": 300}') AS generated_description
FROM (
    SELECT name, category, price, specifications
    FROM dictionary.products 
    WHERE ref = &product_ref
) AS pd;

-- Analyzing an uploaded image
SELECT CALL(AI_ANALYZE_IMAGE,
    '{"image_url": "' || &image_url || '",
      "question": "Describe the image contents and identify the product type"}') AS image_analysis;
 
-- Checking product quality from a photo
SELECT CALL(AI_ANALYZE_IMAGE,
    '{"image_url": "' || &product_photo ||
      "question": "Rate the product quality from the photo from 1 to 10 and describe visible defects",
      "model": "gpt-4-vision"}') AS quality_check;

-- Simple email
SELECT CALL(EMAIL_SEND,
    '{"to": "' || &customer_email ||
    '", "subject": "Order confirmed",
      "body": "Your order #' || &order_id || ' has been accepted for processing"}') AS email_sent;
 
-- HTML email with a template
SELECT CALL(EMAIL_SEND,
    '{"to": "' || &customer_email ||
    '", "subject": "Invoice for payment",
      "html_body": "<h2>Invoice #' || &invoice_id || '</h2><p>Amount: ' || &amount || ' RUB</p>",
      "attachments": ["' || &pdf_invoice_url || '"]}') AS invoice_sent;

-- SMS notification that an order is ready
SELECT CALL(SMS_SEND,
    '{"phone": "' || &customer_phone ||
    '", "message": "Your order #' || &order_id || ' is ready for pickup. Code: ' || &pickup_code || '"}') AS sms_sent;

-- POST webhook notification
SELECT CALL(WEBHOOK,
    '{"url": "' || &webhook_url ||
    '", "method": "POST", 
      "data": {"event": "order_completed", "order_id": ' || &order_id || ', "amount": ' || &amount || '}}') AS webhook_result;
 
-- Notifying a partner about a new order
SELECT CALL(WEBHOOK,
    '{"url": "https://partner.api.com/orders",
      "method": "POST",
      "headers": {"Authorization": "Bearer ' || &partner_token || '"},
      "data": ' || &order_data::TEXT || '}') AS partner_notification;
 
-- GET request to fetch exchange rates  
SELECT CALL(WEBHOOK,
    '{"url": "https://api.exchangerate.com/v1/latest/USD",
      "method": "GET"}') AS exchange_rates;

-- Processing a card payment
SELECT CALL(PAY_PROCESS,
    '{"amount": ' || &amount ||
    ', "currency": "RUB",
      "card_token": "' || &card_token ||
    '", "description": "Payment for order #' || &order_id || '"}') AS payment_result;
 
-- Refunding funds
SELECT CALL(PAY_REFUND,
    '{"transaction_id": "' || &transaction_id ||
    '", "amount": ' || &refund_amount ||
    ', "reason": "Refund at customer request"}') AS refund_result;

-- Uploading a file to cloud storage
SELECT CALL(FILE_UPLOAD,
    '{"file_data": "' || &base64_content ||
    '", "filename": "' || &filename ||
    '", "path": "/uploads/orders/' || &order_id || '/"}') AS upload_result;
 
-- Generating a PDF document
SELECT CALL(PDF_GENERATE,
    '{"template": "invoice",
      "data": ' || &invoice_data::TEXT ||
    ', "filename": "invoice_' || &invoice_id || '.pdf"}') AS pdf_generated;

-- Logging a user action
SELECT CALL(LOG_EVENT,
    '{"level": "info",
      "event": "user_login",
      "user_id": ' || &user_id ||
    ', "ip": "' || &user_ip ||
    '", "details": {"browser": "' || &user_agent || '"}}') AS logged;
 
-- Logging an error
SELECT CALL(LOG_EVENT,
    '{"level": "error",
      "event": "payment_failed",
      "order_id": ' || &order_id ||
    ', "error": "' || &error_message ||
    '", "stack_trace": "' || &stack_trace || '"}') AS error_logged;

-- Sending business metrics
SELECT CALL(METRICS_SEND,
    '{"metric": "daily_revenue",
      "value": ' || daily_revenue ||
    ', "timestamp": ' || EXTRACT(epoch FROM NOW()) ||
    ', "tags": {"store_id": "' || &store_id || '"}}') AS metric_sent
FROM (
    SELECT SUM(total_amount) AS daily_revenue
    FROM dictionary.orders
    WHERE DATE(created_at) = CURRENT_DATE
) AS daily_stats;

-- Comprehensive processing of a new order.
-- Ineron SQL has no CTEs — run all the steps as columns of one SELECT.
SELECT 
    -- 1. Log receipt of the order
    CALL(LOG_EVENT,
        '{"level": "info", "event": "order_received", "order_id": ' || &order_id || '}') AS log_result,
    
    -- 2. Notify the administrator  
    CALL(TG_SEND_MESSAGE,
        '{"chat_id": ' || &admin_chat ||
        ', "text": "📦 New order #' || &order_id ||
        '\n💰 Amount: ' || &total_amount || ' RUB' ||
        '\n👤 Customer: ' || &customer_name || '"}') AS admin_notification,
    
    -- 3. Send a confirmation to the customer
    CALL(EMAIL_SEND,
        '{"to": "' || &customer_email ||
        '", "subject": "Order confirmed #' || &order_id ||
        '", "body": "Thank you for your order! We have started processing it."}') AS customer_email,
    
    -- 4. Notify the partner system
    CALL(WEBHOOK,
        '{"url": "' || &partner_webhook ||
        '", "method": "POST",
          "data": {"event": "new_order", "order": ' || &order_data::TEXT || '}}') AS partner_webhook,
    
    -- 5. Send metrics
    CALL(METRICS_SEND,
        '{"metric": "orders_count", "value": 1, "tags": {"store": "' || &store_id || '"}}') AS metrics,
    
    'order_processing_completed' AS status
TYPE OBJECT;

-- Monitoring the system and sending alerts (no CTE — subquery in FROM)
SELECT 
    CASE 
        WHEN sc.memory_usage > 90 THEN
            CALL(TG_SEND_MESSAGE, 
                '{"chat_id": ' || &alert_chat || 
                ', "text": "🚨 CRITICAL: Memory ' || sc.memory_usage || '%"}')
        WHEN sc.cpu_usage > 85 THEN
            CALL(TG_SEND_MESSAGE,
                '{"chat_id": ' || &alert_chat ||
                ', "text": "⚠️ WARNING: CPU ' || sc.cpu_usage || '%"}')
        WHEN sc.orders_last_hour = 0 THEN
            CALL(TG_SEND_MESSAGE,
                '{"chat_id": ' || &alert_chat ||
                ', "text": "📊 INFO: No orders in the last hour"}')
        ELSE 'system_ok'
    END AS alert_result,
    
    -- Send metrics in any case
    CALL(METRICS_SEND,
        '{"metric": "system_health", 
          "value": 1,
          "tags": {"memory": ' || sc.memory_usage || ', "cpu": ' || sc.cpu_usage || '}}') AS metrics_sent
FROM (
    SELECT 
        (SELECT COUNT(*) FROM dictionary.orders WHERE created_at > NOW() - INTERVAL '1 hour') AS orders_last_hour,
        (SELECT COUNT(*) FROM dictionary.users WHERE last_activity > NOW() - INTERVAL '5 minutes') AS active_users,
        &system_memory_percent AS memory_usage,
        &system_cpu_percent AS cpu_usage
) AS sc
TYPE OBJECT;

-- Safe sending with error handling (no CTE — subquery in FROM)
SELECT 
    CASE 
        WHEN sa.result::TEXT LIKE '%error%' THEN 
            CALL(LOG_EVENT, '{"level": "error", "event": "telegram_send_failed", "details": "' || sa.result::TEXT || '"}')
        ELSE 
            'message_sent_successfully'
    END AS final_result
FROM (
    SELECT CALL(TG_SEND_MESSAGE,
        '{"chat_id": ' || &chat_id || ', "text": "' || &message || '"}') AS result
) AS sa
TYPE OBJECT;

-- A retry system for critical notifications (no CTE — subquery in FROM)
SELECT 
    CASE 
        WHEN rl.send_result::TEXT LIKE '%success%' THEN 
            'email_sent'
        WHEN rl.attempt < rl.max_attempts THEN
            'retry_needed'
        ELSE 
            CALL(LOG_EVENT, '{"level": "critical", "event": "email_send_failed_all_attempts"}')
    END AS status
FROM (
    SELECT 
        &attempt_number AS attempt,
        &max_attempts AS max_attempts,
        CALL(EMAIL_SEND, &email_params) AS send_result
) AS rl
TYPE OBJECT;

-- ✅ Good: escaping quotes
SELECT CALL(TG_SEND_MESSAGE, 
    '{"chat_id": ' || &chat_id || 
    ', "text": "' || REPLACE(&message, '"', '\\"') || '"}');
 
-- ❌ Bad: unescaped data (a vulnerability)
SELECT CALL(TG_SEND_MESSAGE,
    '{"chat_id": ' || &chat_id || ', "text": "' || &message || '"}');

-- ✅ Good: use objects for complex parameters
SELECT CALL(WEBHOOK,
    '{"url": "' || &webhook_url ||
    '", "method": "POST",
      "headers": {"Content-Type": "application/json"},
      "data": ' || &structured_data::TEXT || '}');

-- ✅ Good: log important CALL operations (no CTE — subquery in FROM)
SELECT 
    ns.result,
    CALL(LOG_EVENT, 
        '{"level": "info", "event": "notification_sent", "result": "' || ns.result::TEXT || '"}') AS logged
FROM (
    SELECT CALL(TG_SEND_MESSAGE, &params) AS result
) AS ns
TYPE OBJECT;