LedgyX SQL - A revolutionary query language

-- The &users variable becomes a table with a schema!
SELECT u.id, u.name, u.email, u.active
FROM &users AS u(
    id INTEGER,
    name TEXT(100), 
    email TEXT(255),
    active BOOL
)
WHERE u.active = true
ORDER BY u.name
TYPE LIST;

-- LEDGER with parameters
SELECT account, balance, last_update
FROM LEDGER.account_balances(
    WHERE account_id = &user_id
    ORDER BY last_update DESC
    LIMIT 50
) AS balances
TYPE LIST;
 
-- CRYPTONET with filtering
SELECT "hash", "from", "to", "value"
FROM ETHEREUM.TRANSACTIONS.DATA(
    WHERE "blockNumber" > &start_block 
    AND "gasPrice" < &max_gas
    ORDER BY "blockNumber" DESC
    LIMIT 1000
) AS transactions
TYPE LIST;

-- Telegram Bot API
SELECT CALL(TG_SEND_MESSAGE, '{"chat_id": ' || &chat_id || ', "text": "Order ready!"}') AS result;
 
-- AI integration
SELECT CALL(AI_CHAT_COMPLETION, '{"model": "gpt-4", "prompt": "' || &question || '"}') AS ai_response;
 
-- Blockchain operations  
SELECT CALL(CONTRACT_CALL, '{"address": "' || &contract || '", "method": "balanceOf", "params": ["' || &wallet || '"]}') AS balance;

CREATE LEDGER IF NOT EXISTS inventory (
    ref UUID DEFAULT UUID() NOT NULL,
    period TIMESTAMP NOT NULL,
    product_id UUID NOT NULL,
    
    -- Resource fields (balances)
    quantity RESOURCE DECIMAL(15,3) DEFAULT 0,
    cost_amount RESOURCE DECIMAL(15,2) DEFAULT 0,
    
    -- Accounting dimensions  
    warehouse_id METERING UUID,
    supplier_id METERING UUID,
    batch_number METERING TEXT(50),
    
    -- Additional properties
    serial_numbers PROPERTY JSON,
    certificates PROPERTY JSON,
    quality_data PROPERTY JSON,
    
    PRIMARY KEY (ref)
);

-- System fields are available for all objects
SELECT 
    REF AS object_reference,      -- Object's unique reference
    ID AS numeric_id,             -- Numeric identifier
    PARENT_REF AS parent_link,    -- Reference to the parent
    OWNER_REF AS owner_link,      -- Reference to the owner
    STATE AS object_state,        -- Object state
    name,
    email
FROM dictionary.users
WHERE STATE = 'active'
TYPE LIST;

-- Practical examples
SELECT 
    UUID() AS new_id,
    UUID(&input_value) AS deterministic_id,
    NOW() AS current_timestamp,
    ISNULL(description, 'No description') AS safe_description,
    NULLREF() AS empty_reference
FROM dictionary.products;

-- FCOUNT for counting
SELECT FCOUNT(*) AS total_count FROM dictionary.users;
 
-- FSUBSTRING for working with strings  
SELECT FSUBSTRING(description, 1, 100) AS short_desc FROM dictionary.products;
 
-- FTRIM for trimming whitespace
SELECT FTRIM(user_input) AS clean_input FROM dictionary.form_data;

SELECT [ALLOWED] [options] fields [TYPE return_type]
FROM source[(parameters)] [AS alias]  
[WHERE conditions]
[GROUP BY fields] [HAVING conditions]
[ORDER BY fields [ASC|DESC]]
[LIMIT count] [OFFSET offset]
[TYPE return_type];

-- Reference tables
FROM dictionary.users
FROM dictionary.products
 
-- Registers with parameters
FROM LEDGER.transactions(
    WHERE account_id = &account
    ORDER BY date DESC
    LIMIT 100
)
 
-- Blockchain data
FROM ETHEREUM.TRANSACTIONS.DATA(WHERE "blockNumber" > &start)
 
-- Custom data as tables
FROM &input_data AS data(
    id INTEGER,
    value TEXT,
    amount DECIMAL(10,2)
)
 
-- Enumerations and constants
FROM enum.status_types
FROM const.system_settings

-- Insert with conflict handling
INSERT INTO dictionary.users (id, name, email, phone)
SELECT &id, &name, &email, &phone
ON CONFLICT(id) DO UPDATE SET
    name = EXCLUDED.name,
    email = EXCLUDED.email,
    updated_at = NOW();
 
-- Ignoring conflicts
INSERT INTO dictionary.tags (name, description)
VALUES (&tag_name, &description)
ON CONFLICT(name) DO NOTHING;
 
-- Error on conflict
INSERT INTO dictionary.unique_codes (code, data)
VALUES (&code, &data)
ON CONFLICT(code) DO ERROR;

-- Update with a subquery
UPDATE dictionary.products 
SET category_name = (
    SELECT name 
    FROM dictionary.categories 
    WHERE ref = products.category_ref
)
WHERE category_name IS NULL;
 
-- Update via JOIN
UPDATE dictionary.orders 
SET total_amount = calculated.total
FROM (
    SELECT 
        order_ref,
        SUM(quantity * price) AS total
    FROM &order_items AS items(
        order_ref UUID,
        quantity DECIMAL,
        price DECIMAL
    )
    GROUP BY order_ref
) AS calculated
WHERE orders.ref = calculated.order_ref;

-- Return a single object
SELECT id, name, email FROM dictionary.users 
WHERE id = &user_id 
TYPE OBJECT;
 
-- Return a list
SELECT id, name, price FROM dictionary.products 
WHERE category_id = &category
TYPE LIST;
 
-- API-formatted return  
SELECT 
    COUNT(*) AS total,
    AVG(price) AS average_price
FROM dictionary.products
TYPE API OBJECT;

-- Available CRYPTONET networks
ETHEREUM    -- Chain ID: 1
GOERLI      -- Chain ID: 5  
BSC         -- Chain ID: 56
MATIC       -- Chain ID: 137

-- Transactions with parameters
SELECT "hash", "from", "to", "value"::NUMBER / 1e18 AS eth_amount
FROM ETHEREUM.TRANSACTIONS.DATA(
    WHERE "blockNumber" BETWEEN &start_block AND &end_block
    ORDER BY "blockNumber" DESC
    LIMIT 1000
) AS txs
TYPE LIST;
 
-- Contract events
SELECT "topics", "data", "blockNumber"
FROM CONTRACT.events(
    WHERE "address" = &contract_address
    AND "blockNumber" > &from_block
    ORDER BY "blockNumber" ASC
) AS events  
TYPE LIST;

-- Telegram notifications
SELECT CALL(TG_SEND_MESSAGE, 
    '{"chat_id": ' || &chat_id || 
    ', "text": "Order #' || &order_id || ' processed!"}') AS sent;
 
-- AI analysis
SELECT CALL(AI_ANALYZE_IMAGE,
    '{"image_url": "' || &image_url || '",
      "question": "Describe the image contents"}') AS analysis;
 
-- Sending a webhook
SELECT CALL(WEBHOOK,
    '{"url": "' || &webhook_url || '",
      "method": "POST", 
      "data": ' || &payload::TEXT || '}') AS webhook_result;

-- Processing an order with custom data.
-- Ineron SQL has no CTEs (WITH ... AS) — compose steps with subqueries in FROM.
SELECT 
    op.order_id,
    op.product_name,
    op.quantity AS requested_qty,
    ISNULL(ic.available_qty, 0) AS available_qty,
    CASE 
        WHEN ISNULL(ic.available_qty, 0) >= op.quantity 
        THEN 'In stock'
        ELSE 'Insufficient stock'
    END AS status
FROM (
    -- Use custom data as a table
    SELECT 
        o.order_id,
        oi.product_id,
        p.name AS product_name,
        oi.quantity,
        oi.price
    FROM &order_data AS o(
        order_id UUID,
        customer_id UUID, 
        total_amount DECIMAL(12,2)
    )
    JOIN &order_items AS oi(
        order_id UUID,
        product_id UUID,
        quantity INTEGER,
        price DECIMAL(10,2)
    ) ON o.order_id = oi.order_id
    JOIN dictionary.products p ON oi.product_id = p.ref
) AS op
LEFT JOIN (
    -- Check balances via LEDGER
    SELECT 
        product_id,
        SUM(quantity) AS available_qty
    FROM LEDGER.inventory(
        WHERE warehouse_id = &warehouse_id
        AND quantity > 0
    ) AS inv
    GROUP BY product_id
) AS ic ON op.product_id = ic.product_id
ORDER BY op.order_id, op.product_name
TYPE LIST;

-- Top-level categories
SELECT 
    c.REF,
    c.ID,
    c.name,
    c.name AS full_path
FROM dictionary.categories AS c
WHERE c.PARENT_REF = NULLREF()
ORDER BY c.name
TYPE LIST;
 
-- Direct children of a category, with a two-level path via a self-join
SELECT 
    c.REF,
    c.ID,
    c.name,
    parent.name || ' > ' || c.name AS full_path
FROM dictionary.categories AS c
JOIN dictionary.categories AS parent ON c.PARENT_REF = parent.REF
WHERE c.PARENT_REF = &parent_ref::uuid
ORDER BY c.name
TYPE LIST;

CREATE DICTIONARY IF NOT EXISTS users (
    ref UUID DEFAULT UUID() NOT NULL,
    id INTEGER UNIQUE NOT NULL,
    name FIELD TEXT(100) NOT NULL,
    email FIELD TEXT(255) UNIQUE,
    phone FIELD TEXT(20),
    metadata PROPERTY JSON,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (ref)
);

-- Insert from custom variables
INSERT INTO dictionary.users (id, name, email, phone)
SELECT u.id, u.name, u.email, u.phone
FROM &user_data AS u(
    id INTEGER,
    name TEXT, 
    email TEXT,
    phone TEXT
)
ON CONFLICT(id) DO UPDATE SET
    name = EXCLUDED.name,
    email = EXCLUDED.email,
    updated_at = NOW();

-- GET /api/users/profile
SELECT 
    REF,
    ID,
    name,
    email,
    ISNULL(phone, 'Not specified') AS phone_display,
    created_at
FROM dictionary.users
WHERE ID = &user_id
TYPE OBJECT;