AI and external services

-- Basic chat request
SELECT AI_CHAT_COMPLETION(
    'gpt-4',
    'Explain how blockchain works in simple terms',
    &temperature,
    &max_tokens
) AS ai_response
TYPE OBJECT;
 
-- Contextual dialog with history
SELECT AI_CHAT_COMPLETION(
    'gpt-3.5-turbo',
    &user_message,
    &conversation_history::JSON,
    0.7,
    1000
) AS chat_response
TYPE OBJECT;
 
-- Using it in an API endpoint
-- POST /api/chat/ask
INSERT INTO chat_logs (user_id, question, ai_answer, timestamp)
SELECT 
    &user_id,
    &question,
    AI_CHAT_COMPLETION('gpt-4', &question, 0.8, 500),
    NOW()
ON CONFLICT(user_id) DO UPDATE 
SET question = EXCLUDED.question,
    ai_answer = EXCLUDED.ai_answer,
    timestamp = EXCLUDED.timestamp;

-- Automatically creating descriptions
SELECT 
    product_name,
    AI_CREATE_COMPLETION(
        'text-davinci-003',
        'Create a marketing description for the product: ' || product_name,
        &temperature,
        &max_tokens
    ) AS generated_description
FROM dictionary.products 
WHERE description IS NULL
LIMIT 10 TYPE LIST;
 
-- Generating SQL queries from a description
SELECT AI_CREATE_COMPLETION(
    'code-davinci-002',
    'Write a SQL query for: ' || &user_request,
    0.1,
    200
) AS generated_sql
TYPE OBJECT;

-- Creating embeddings for search
SELECT 
    document_id,
    title,
    content,
    AI_CREATE_EMBEDDING('text-embedding-ada-002', content) AS embedding_vector
FROM dictionary.documents
WHERE embedding_vector IS NULL
LIMIT 100 TYPE LIST;
 
-- Semantic search by query (simplified)
SELECT 
    d.title,
    d.content,
    d.embedding_vector
FROM dictionary.documents d
WHERE d.embedding_vector IS NOT NULL
ORDER BY d.title
LIMIT 10 TYPE LIST;

-- Automatic moderation of messages
SELECT 
    message_id,
    user_message,
    AI_MODERATE_CONTENT(user_message) AS moderation_result
FROM dictionary.user_messages 
WHERE moderated = false
TYPE LIST;
 
-- API for checking content before publishing  
-- POST /api/content/moderate
SELECT 
    AI_MODERATE_CONTENT(&content) AS moderation_result
TYPE OBJECT;

-- Creating images from a description
SELECT 
    product_id,
    product_name,
    AI_GENERATE_IMAGE(
        'dall-e-3',
        'Create a professional product image: ' || product_name,
        '1024x1024',
        'high'
    ) AS generated_image_url
FROM dictionary.products
WHERE image_url IS NULL
LIMIT 5 TYPE LIST;
 
-- Generating user avatars
-- POST /api/user/generate-avatar
INSERT INTO dictionary.user_avatars (user_id, avatar_url, generated_at)
SELECT 
    &user_id,
    AI_GENERATE_IMAGE(
        'dall-e-2', 
        &avatar_description,
        '512x512',
        'standard'
    ),
    NOW()
ON CONFLICT(user_id) DO UPDATE 
SET avatar_url = EXCLUDED.avatar_url,
    generated_at = EXCLUDED.generated_at;

-- Analyzing uploaded images
SELECT 
    image_id,
    image_url,
    AI_ANALYZE_IMAGE(image_url, 'Describe what is shown in the picture') AS image_description,
    AI_ANALYZE_IMAGE(image_url, 'Are there people in the image?') AS contains_people
FROM dictionary.uploaded_images
WHERE analyzed = false
LIMIT 20 TYPE LIST;
 
-- Automatic categorization
UPDATE dictionary.uploaded_images 
SET 
    category = (
        SELECT AI_ANALYZE_IMAGE(image_url, 'Which category does this image belong to: nature, city, people, food, technology?')
    ),
    analyzed = true
WHERE analyzed = false;

-- Analyzing local image files
SELECT 
    file_path,
    AI_ANALYZE_IMAGE_FILE(file_path, 'Extract all text from the image') AS extracted_text,
    AI_ANALYZE_IMAGE_FILE(file_path, 'Describe the document contents') AS document_summary
FROM dictionary.scanned_documents
WHERE processed = false
TYPE LIST;

-- Transcribing audio calls
SELECT 
    call_id,
    audio_file_path,
    AI_TRANSCRIBE_AUDIO(audio_file_path, 'ru') AS transcription,
    duration_seconds
FROM dictionary.call_recordings
WHERE transcription IS NULL
ORDER BY call_date DESC
LIMIT 50 TYPE LIST;
 
-- API for transcription
-- POST /api/audio/transcribe  
SELECT 
    &file_id AS file_id,
    AI_TRANSCRIBE_AUDIO(&file_path, &language) AS transcript
TYPE OBJECT;

-- Translating audio into other languages
SELECT 
    audio_id,
    original_language,
    AI_TRANSLATE_AUDIO(audio_file_path, 'en') AS english_translation,
    AI_TRANSLATE_AUDIO(audio_file_path, 'es') AS spanish_translation
FROM dictionary.multilingual_content
WHERE translated = false
TYPE LIST;

-- AI_UPLOAD_FILE - Uploading files to the AI service
SELECT AI_UPLOAD_FILE(&file_path, 'training-data') AS upload_result
TYPE OBJECT;
 
-- AI_LIST_FILES - List of uploaded files  
SELECT AI_LIST_FILES() AS files_list
TYPE LIST;
 
-- AI_DELETE_FILE - Deleting files
SELECT AI_DELETE_FILE(&file_id) AS deletion_result  
TYPE OBJECT;

-- Sending notifications to users
SELECT 
    user_id,
    TG_SEND_MESSAGE(
        telegram_chat_id,
        'Your order #' || order_id || ' is ready for pickup!',
        'MarkdownV2'
    ) AS message_result
FROM dictionary.users u
JOIN dictionary.orders o ON u.user_id = o.user_id  
WHERE o.status = 'ready' AND o.notified = false
TYPE LIST;
 
-- API for sending messages
-- POST /api/telegram/send-message
INSERT INTO dictionary.telegram_messages (chat_id, message, sent_at, status)
SELECT 
    &chat_id,
    &message_text,
    NOW(),
    'sent';

-- Sending photos with a caption
SELECT 
    TG_SEND_PHOTO(
        &chat_id,
        &photo_url,
        'Your QR code for signing in to the system'
    ) AS photo_sent
TYPE OBJECT;
 
-- Sending documents
SELECT 
    TG_SEND_DOCUMENT(
        &chat_id,
        &document_path,
        'Report for ' || &report_period
    ) AS document_sent  
TYPE OBJECT;
 
-- Sending voice messages
SELECT 
    TG_SEND_VOICE(
        &chat_id,
        &voice_file_path,
        'Voice notification'
    ) AS voice_sent
TYPE OBJECT;
 
-- Sending audio files
SELECT 
    TG_SEND_AUDIO(
        &chat_id,
        &audio_file_path,
        'Music file',
        &performer,
        &title,
        &duration
    ) AS audio_sent
TYPE OBJECT;

-- Sending a menu with buttons
SELECT TG_SEND_INLINE_KEYBOARD(
    &chat_id,
    'Choose an action:',
    '[
        [{"text": "📊 Show statistics", "callback_data": "show_stats"}],
        [{"text": "🔄 Refresh data", "callback_data": "refresh_data"}],
        [{"text": "⚙️ Settings", "callback_data": "settings"}]
    ]'
) AS keyboard_sent
TYPE OBJECT;
 
-- Handling callback queries
-- POST /api/telegram/callback
SELECT TG_ANSWER_CALLBACK_QUERY(
    &callback_query_id,
    CASE &callback_data
        WHEN 'show_stats' THEN 'Loading statistics...'
        WHEN 'refresh_data' THEN 'Refreshing data...'  
        WHEN 'settings' THEN 'Opening settings...'
        ELSE 'Unknown command'
    END,
    true
) AS callback_answered
TYPE OBJECT;

-- Getting information about a file
SELECT 
    file_id,
    TG_GET_FILE(&file_id) AS file_info
TYPE OBJECT;
 
-- Downloading a file from a user
SELECT 
    TG_DOWNLOAD_FILE(&file_id, &local_path) AS download_result
TYPE OBJECT;
 
-- Combined operation
SELECT 
    TG_GET_AND_DOWNLOAD_FILE(&file_id, &destination_path) AS file_processed
TYPE OBJECT;

-- Sending real-time notifications
SELECT SSE_SEND(
    &client_id,
    'data_update',
    '{
        "type": "new_order",
        "order_id": ' || &order_id || ',
        "timestamp": "' || NOW() || '"
    }'
) AS sse_sent
TYPE OBJECT;
 
-- Notifications about system events
INSERT INTO dictionary.system_events (event_type, data, created_at)
SELECT 'user_login', &user_data, NOW();

-- Webhook for user registration
SELECT WEBHOOK(
    'POST',
    'https://external-system.com/api/new-user',
    '{"user_id":' || &user_id || ',"email":"' || &email || ',"registration_time":"' || NOW() || '"}',
    '{"Content-Type": "application/json", "Authorization": "Bearer ' || &api_token || '"}'
) AS webhook_result
TYPE OBJECT;
 
-- Webhook for an order status change
SELECT WEBHOOK(
    'POST',
    &webhook_url,
    '{"order_id":' || &order_id || ',"old_status":"' || &old_status || '","new_status":"' || &new_status || '","updated_at":"' || NOW() || '"}'
) AS status_webhook_result
TYPE OBJECT;

-- Chat bot with AI support
-- POST /api/telegram/chat-bot
SELECT 
    TG_SEND_MESSAGE(
        &chat_id,
        AI_CHAT_COMPLETION('gpt-3.5-turbo', &user_message, 0.7, 500),
        'Markdown'
    ) AS message_sent
TYPE OBJECT;

-- Automatic analysis and notifications (no CTE — subquery in FROM)
SELECT 
    ia.image_id,
    ia.analysis,
    CASE 
        WHEN ia.analysis LIKE '%suspicious%' THEN
            TG_SEND_MESSAGE(
                &security_chat_id,
                '🚨 Suspicious activity detected on camera #' || ia.image_id,
                'Markdown'
            )
        ELSE NULL
    END AS alert_sent
FROM (
    SELECT 
        image_id,
        AI_ANALYZE_IMAGE(image_url, 'Is there any suspicious activity in the image?') AS analysis
    FROM dictionary.security_cameras
    WHERE analyzed = false
    LIMIT 10
) AS ia
TYPE LIST;

-- Getting schemas for AI processing
SELECT AI_SCHEMAS() AS available_schemas
TYPE LIST;
 
-- Automatically generating schemas based on data
SELECT AI_SCHEMAS(&table_name, &sample_data) AS generated_schema
TYPE OBJECT;

-- A task queue for AI processing
CREATE DICTIONARY IF NOT EXISTS ai_tasks_queue (
    task_id UUID DEFAULT UUID(),
    task_type TEXT NOT NULL,
    input_data JSON NOT NULL,
    status TEXT DEFAULT 'pending',
    result JSON,
    created_at TIMESTAMP DEFAULT NOW(),
    processed_at TIMESTAMP,
    PRIMARY KEY (task_id)
);
 
-- Processing tasks in batches
UPDATE ai_tasks_queue 
SET 
    status = 'processing',
    result = CASE task_type
        WHEN 'text_completion' THEN 
            AI_CREATE_COMPLETION('gpt-3.5-turbo', input_data->>'prompt')::JSON
        WHEN 'image_analysis' THEN 
            AI_ANALYZE_IMAGE(input_data->>'image_url', input_data->>'question')::JSON
        WHEN 'audio_transcription' THEN 
            AI_TRANSCRIBE_AUDIO(input_data->>'file_path', input_data->>'language')::JSON
    END,
    processed_at = NOW()
WHERE status = 'pending'
  AND created_at < NOW() - INTERVAL '1 minute'
LIMIT 50;

-- Logging AI requests
CREATE DICTIONARY IF NOT EXISTS ai_usage_logs (
    log_id UUID DEFAULT UUID(),
    service_type TEXT NOT NULL,
    endpoint TEXT,
    input_tokens INTEGER,
    output_tokens INTEGER,
    cost_usd DECIMAL(10,4),
    response_time_ms INTEGER,
    success BOOLEAN,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);
 
-- AI usage statistics
SELECT 
    service_type,
    COUNT(*) as requests_count,
    SUM(cost_usd) as total_cost,
    AVG(response_time_ms) as avg_response_time,
    (COUNT(*) FILTER (WHERE success = true) * 100.0 / COUNT(*)) as success_rate
FROM ai_usage_logs
WHERE created_at > NOW() - INTERVAL '24 hours'
GROUP BY service_type
ORDER BY total_cost DESC
TYPE LIST;