Object types and data structures

-- Basic users reference table
CREATE DICTIONARY IF NOT EXISTS users (
    ref UUID DEFAULT UUID() NOT NULL,
    id INTEGER NOT NULL,
    name TEXT(100) NOT NULL,
    email TEXT(255) UNIQUE,
    phone TEXT(20),
    created_at TIMESTAMP DEFAULT NOW(),
    active BOOLEAN DEFAULT true,
    PRIMARY KEY (ref),
    UNIQUE KEY (id)
);
 
-- Reference table with additional fields
CREATE DICTIONARY IF NOT EXISTS products (
    ref UUID DEFAULT UUID() NOT NULL,
    id INTEGER,
    name TEXT(200) NOT NULL,
    category_ref UUID,
    price DECIMAL(12,2),
    description TEXT,
    image_url TEXT,
    sku TEXT(50) UNIQUE,
    metadata TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (ref)
);

-- Inserting data
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,
    phone = EXCLUDED.phone,
    updated_at = NOW();
 
-- Querying reference tables
SELECT u.id, u.name, u.email, u.active
FROM dictionary.users AS u
WHERE u.active = true 
  AND u.created_at > &start_date
ORDER BY u.name
LIMIT 100 TYPE LIST;
 
-- Joined queries
SELECT 
    p.name AS product_name,
    c.name AS category_name,
    p.price,
    p.sku
FROM dictionary.products AS p
JOIN dictionary.categories AS c ON p.category_ref = c.ref
WHERE p.price BETWEEN &min_price AND &max_price
ORDER BY p.price DESC
TYPE LIST;

-- Order statuses
CREATE ENUM IF NOT EXISTS order_statuses (
    ref UUID DEFAULT UUID() NOT NULL,
    code TEXT(20) UNIQUE NOT NULL,
    name TEXT(100) NOT NULL,
    description TEXT,
    sort_order INTEGER DEFAULT 0,
    active BOOLEAN DEFAULT true,
    PRIMARY KEY (ref),
    UNIQUE KEY (code)
);
 
-- Populating the enumeration values
INSERT INTO enum.order_statuses (code, name, description, sort_order) VALUES
('new', 'New', 'The order has just been created', 1),
('confirmed', 'Confirmed', 'The order has been confirmed by a manager', 2),
('processing', 'Processing', 'The order is being processed', 3),
('shipped', 'Shipped', 'The order has been shipped to the customer', 4),
('delivered', 'Delivered', 'The order has been delivered', 5),
('cancelled', 'Cancelled', 'The order has been cancelled', 99);

-- Querying enumeration values
SELECT code, name, description, sort_order
FROM enum.order_statuses 
WHERE active = true
ORDER BY sort_order
TYPE LIST;
 
-- Using them in other objects
CREATE DICTIONARY IF NOT EXISTS orders (
    ref UUID DEFAULT UUID() NOT NULL,
    order_number TEXT(20) UNIQUE NOT NULL,
    customer_ref UUID,
    status_code TEXT DEFAULT 'new',
    total_amount DECIMAL(12,2),
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (ref)
);
 
-- Queries that use enumerations
SELECT 
    o.order_number,
    u.name AS customer_name,
    s.name AS status_name,
    o.total_amount
FROM dictionary.orders AS o
JOIN dictionary.users AS u ON o.customer_ref = u.ref
JOIN enum.order_statuses AS s ON o.status_code = s.code
WHERE s.code IN ('processing', 'shipped')
TYPE LIST;

-- System constants
CREATE CONST IF NOT EXISTS system_settings (
    ref UUID DEFAULT UUID() NOT NULL,
    key TEXT(100) UNIQUE NOT NULL,
    value TEXT,
    value_type TEXT(20) DEFAULT 'string',
    description TEXT,
    editable BOOLEAN DEFAULT false,
    group_name TEXT(50),
    PRIMARY KEY (ref),
    UNIQUE KEY (key)
);
 
-- Populating the constants
INSERT INTO const.system_settings (key, value, value_type, description, editable, group_name) VALUES
('app_name', 'LedgyX Platform', 'string', 'Application name', false, 'general'),
('max_file_size_mb', '100', 'integer', 'Maximum file size in MB', true, 'upload'),
('default_currency', 'USD', 'string', 'Default currency', true, 'finance'),
('api_rate_limit', '1000', 'integer', 'Request limit per hour', true, 'api'),
('maintenance_mode', 'false', 'boolean', 'Maintenance mode', true, 'general');
 
-- Getting constants
SELECT key, value, value_type, description
FROM const.system_settings 
WHERE group_name = &settings_group
ORDER BY key
TYPE LIST;
 
-- Using them in queries
SELECT 
    (SELECT value FROM const.system_settings WHERE key = 'app_name') AS app_name,
    COUNT(*) AS total_users,
    (SELECT value::NUMBER FROM const.system_settings WHERE key = 'max_file_size_mb') AS max_file_size
FROM dictionary.users
TYPE OBJECT;

-- Currency rates register
CREATE INFOREG IF NOT EXISTS currency_rates (
    ref UUID DEFAULT UUID() NOT NULL,
    period TIMESTAMP NOT NULL,
    currency_from TEXT(3) NOT NULL,
    currency_to TEXT(3) NOT NULL,
    rate DECIMAL(18,8) NOT NULL,
    source TEXT(50),
    updated_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (ref)
);
 
-- Product balances register
CREATE INFOREG IF NOT EXISTS product_balances (
    ref UUID DEFAULT UUID() NOT NULL,
    period TIMESTAMP NOT NULL,
    product_ref UUID NOT NULL,
    warehouse_ref UUID NOT NULL,
    quantity_in_stock DECIMAL(15,3) DEFAULT 0,
    reserved_quantity DECIMAL(15,3) DEFAULT 0,
    average_cost DECIMAL(12,2),
    last_movement_date TIMESTAMP,
    PRIMARY KEY (ref)
);
 
-- Querying information registers
-- Current currency rates
SELECT 
    currency_from,
    currency_to, 
    rate,
    period
FROM inforeg.currency_rates
WHERE period = (
    SELECT MAX(period) 
    FROM inforeg.currency_rates cr2 
    WHERE cr2.currency_from = currency_rates.currency_from 
      AND cr2.currency_to = currency_rates.currency_to
)
ORDER BY currency_from, currency_to
TYPE LIST;
 
-- Product balances as of a date
SELECT 
    p.name AS product_name,
    w.name AS warehouse_name,
    pb.quantity_in_stock,
    pb.reserved_quantity,
    (pb.quantity_in_stock - pb.reserved_quantity) AS available_quantity
FROM inforeg.product_balances pb
JOIN dictionary.products p ON pb.product_ref = p.ref
JOIN dictionary.warehouses w ON pb.warehouse_ref = w.ref
WHERE pb.period = &balance_date
  AND pb.quantity_in_stock > 0
ORDER BY p.name, w.name
TYPE LIST;

-- Main accounting register
CREATE LEDGER IF NOT EXISTS general_ledger (
    ref UUID DEFAULT UUID() NOT NULL,
    transaction_date TIMESTAMP NOT NULL,
    document_ref UUID,
    account_debit TEXT(20) NOT NULL,
    account_credit TEXT(20) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    currency TEXT(3) DEFAULT 'USD',
    description TEXT,
    analytical_dimensions TEXT,
    PRIMARY KEY (ref)
);
 
-- Cash flow register  
CREATE LEDGER IF NOT EXISTS cash_flow (
    ref UUID DEFAULT UUID() NOT NULL,
    period TIMESTAMP NOT NULL,
    cash_flow_item_ref UUID,
    organization_ref UUID,
    amount DECIMAL(15,2) NOT NULL,
    currency TEXT(3) DEFAULT 'USD',
    direction TEXT(10),
    document_ref UUID,
    analytical_data TEXT,
    PRIMARY KEY (ref)
);
 
-- Special fields for accounting registers
CREATE LEDGER IF NOT EXISTS inventory_accounting (
    ref UUID DEFAULT UUID() NOT NULL,
    period TIMESTAMP NOT NULL,
    product_ref UUID NOT NULL,
    warehouse_ref UUID NOT NULL,
    
    -- Resources (balances)
    quantity RESOURCE DECIMAL(15,3) DEFAULT 0,
    cost_amount RESOURCE DECIMAL(15,2) DEFAULT 0,
    
    -- Accounting dimensions  
    batch_number METERING TEXT(50),
    supplier_ref METERING UUID,
    expiry_date METERING TIMESTAMP,
    
    -- Additional properties
    serial_numbers PROPERTY TEXT,
    quality_grade PROPERTY TEXT(20),
    
    PRIMARY KEY (ref)
);

-- Postings for a document
INSERT INTO ledger.general_ledger (
    transaction_date, document_ref, account_debit, account_credit, 
    amount, currency, description
) VALUES
(&transaction_date, &doc_ref, '62.01', '51.01', &amount, 'USD', 'Receipt of payment from customer'),
(&transaction_date, &doc_ref, '51.01', '90.01', &bank_fee, 'USD', 'Bank fee');
 
-- Trial balance. Ineron SQL has no UNION — run the debit and credit turnovers
-- as two queries and merge them by account in your application.
SELECT 
    account_debit AS account,
    SUM(amount) AS debit_turnover
FROM ledger.general_ledger
WHERE transaction_date BETWEEN &start_date AND &end_date
GROUP BY account_debit
ORDER BY account_debit
TYPE LIST;
 
SELECT 
    account_credit AS account,
    SUM(amount) AS credit_turnover  
FROM ledger.general_ledger
WHERE transaction_date BETWEEN &start_date AND &end_date
GROUP BY account_credit
ORDER BY account_credit
TYPE LIST;
 
-- Queries with RESOURCE, METERING, PROPERTY
SELECT 
    p.name AS product_name,
    w.name AS warehouse_name,
    SUM(ia.quantity) AS total_quantity,
    SUM(ia.cost_amount) AS total_cost,
    ia.batch_number,
    s.name AS supplier_name
FROM ledger.inventory_accounting ia
JOIN dictionary.products p ON ia.product_ref = p.ref
JOIN dictionary.warehouses w ON ia.warehouse_ref = w.ref  
LEFT JOIN dictionary.suppliers s ON ia.supplier_ref = s.ref
WHERE ia.period = &balance_date
GROUP BY p.name, w.name, ia.batch_number, s.name
HAVING SUM(ia.quantity) > 0
ORDER BY p.name, ia.batch_number
TYPE LIST;

-- Header of a "Customer order" document
CREATE OPERATION IF NOT EXISTS customer_orders (
    ref UUID DEFAULT UUID() NOT NULL,
    document_number TEXT(20) UNIQUE NOT NULL,
    document_date TIMESTAMP NOT NULL,
    customer_ref UUID REFERENCES customers(ref) NOT NULL,
    manager_ref UUID REFERENCES employees(ref),
    status_code TEXT REFERENCES order_statuses(code) DEFAULT 'draft',
    total_amount DECIMAL(12,2) DEFAULT 0,
    currency TEXT(3) DEFAULT 'USD',
    payment_terms TEXT(50),
    delivery_address TEXT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    created_by_ref UUID REFERENCES users(ref),
    posted BOOLEAN DEFAULT false,
    posted_at TIMESTAMP,
    PRIMARY KEY (ref),
    INDEX idx_document_date (document_date),
    INDEX idx_customer (customer_ref),
    INDEX idx_status (status_code)
);
 
-- Tabular part of the document
CREATE OPERATION IF NOT EXISTS customer_order_items (
    ref UUID DEFAULT UUID() NOT NULL,
    document_ref UUID REFERENCES customer_orders(ref) NOT NULL,
    line_number INTEGER NOT NULL,
    product_ref UUID REFERENCES products(ref) NOT NULL,
    quantity DECIMAL(15,3) NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    discount_percent DECIMAL(5,2) DEFAULT 0,
    line_amount DECIMAL(12,2) NOT NULL,
    warehouse_ref UUID REFERENCES warehouses(ref),
    delivery_date DATE,
    PRIMARY KEY (ref),
    UNIQUE KEY document_line (document_ref, line_number),
    INDEX idx_product (product_ref)
);
 
-- Creating a document with a tabular part.
-- Ineron SQL has no data-modifying CTEs — derive a deterministic ref with uuid()
-- and reuse it to link the header and its line items across two statements.
INSERT INTO operation.customer_orders (
    ref, document_number, document_date, customer_ref, manager_ref, 
    total_amount, currency
) 
VALUES (uuid(&doc_number), &doc_number, &doc_date, &customer_ref, &manager_ref, &total_amount, 'USD');
 
INSERT INTO operation.customer_order_items (
    document_ref, line_number, product_ref, quantity, unit_price, line_amount
)
SELECT 
    uuid(&doc_number) AS document_ref,
    &line_number,
    &product_ref,
    &quantity,
    &unit_price,
    (&quantity * &unit_price) AS line_amount;
 
-- Posting the document
UPDATE operation.customer_orders 
SET 
    posted = true,
    posted_at = NOW(),
    status_code = 'confirmed'
WHERE ref = &document_ref
  AND posted = false;

-- Smart contract register
CREATE CONTRACT IF NOT EXISTS smart_contracts (
    ref UUID DEFAULT UUID() NOT NULL,
    contract_address ADDRESS NOT NULL,
    network_id INTEGER NOT NULL,
    contract_name TEXT(100),
    abi_json JSON,
    bytecode TEXT,
    deployment_block BIGINT,
    deployment_transaction TEXT,
    creator_address ADDRESS,
    verified BOOLEAN DEFAULT false,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (ref),
    UNIQUE KEY contract_network (contract_address, network_id),
    INDEX idx_network (network_id),
    INDEX idx_creator (creator_address)
);
 
-- Contract events
CREATE CONTRACT IF NOT EXISTS contract_events (
    ref UUID DEFAULT UUID() NOT NULL,
    contract_ref UUID REFERENCES smart_contracts(ref) NOT NULL,
    event_name TEXT(100) NOT NULL,
    event_signature TEXT,
    block_number BIGINT NOT NULL,
    transaction_hash TEXT NOT NULL,
    log_index INTEGER NOT NULL,
    event_data JSON,
    decoded_data JSON,
    processed BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (ref),
    UNIQUE KEY tx_log (transaction_hash, log_index),
    INDEX idx_contract (contract_ref),
    INDEX idx_block (block_number),
    INDEX idx_event (event_name)
);
 
-- Queries to CONTRACT objects
SELECT 
    sc.contract_name,
    sc.contract_address,
    sc.network_id,
    COUNT(ce.ref) AS events_count,
    MAX(ce.block_number) AS last_event_block
FROM contract.smart_contracts sc
LEFT JOIN contract.contract_events ce ON sc.ref = ce.contract_ref
WHERE sc.active = true
  AND sc.network_id = &target_network
GROUP BY sc.ref, sc.contract_name, sc.contract_address, sc.network_id
ORDER BY events_count DESC
TYPE LIST;

-- Product balances
quantity RESOURCE DECIMAL(15,3) DEFAULT 0,
weight RESOURCE DECIMAL(10,3) DEFAULT 0,
volume RESOURCE DECIMAL(10,6) DEFAULT 0,
 
-- Financial balances
balance_amount RESOURCE DECIMAL(15,2) DEFAULT 0,
debt_amount RESOURCE DECIMAL(15,2) DEFAULT 0,
 
-- Accumulators
total_sales RESOURCE DECIMAL(15,2) DEFAULT 0,
points_earned RESOURCE INTEGER DEFAULT 0

-- Product analytics
batch_number METERING TEXT(50),
supplier_ref METERING UUID REFERENCES suppliers(ref),
expiry_date METERING DATE,
warehouse_ref METERING UUID REFERENCES warehouses(ref),
 
-- Financial analytics  
project_ref METERING UUID REFERENCES projects(ref),
cost_center_ref METERING UUID REFERENCES cost_centers(ref),
currency_code METERING TEXT(3),
 
-- Counterparty analytics
region_ref METERING UUID REFERENCES regions(ref),
manager_ref METERING UUID REFERENCES employees(ref),
contract_ref METERING UUID REFERENCES contracts(ref)

-- Product properties
specifications PROPERTY JSON,
certificates PROPERTY JSON,
images PROPERTY JSON,
tags PROPERTY TEXT[],
 
-- Document properties
attachments PROPERTY JSON,
approval_history PROPERTY JSON,
custom_fields PROPERTY JSON,
 
-- User properties
preferences PROPERTY JSON,
metadata PROPERTY JSON,
permissions PROPERTY JSON

-- 1. Create the customer order header. Ineron SQL has no data-modifying CTEs —
--    use a deterministic uuid() ref to link the statements below.
INSERT INTO operation.customer_orders (
    ref, document_number, document_date, customer_ref, total_amount
) VALUES (uuid(&order_number), &order_number, NOW(), &customer_ref, 0);
 
-- Insert the line items, linked by the same deterministic ref
INSERT INTO operation.customer_order_items (
    document_ref, line_number, product_ref, quantity, unit_price, line_amount
)
SELECT 
    uuid(&order_number) AS document_ref,
    item.line_number,
    item.product_ref,
    item.quantity,
    p.price,
    item.quantity * p.price
FROM &order_items AS item(
    line_number INTEGER,
    product_ref UUID,
    quantity DECIMAL
)
JOIN dictionary.products p ON item.product_ref = p.ref;
 
-- Update the header total from the inserted line items
UPDATE operation.customer_orders 
SET total_amount = (
    SELECT SUM(oi.line_amount) 
    FROM operation.customer_order_items oi 
    WHERE oi.document_ref = uuid(&order_number)
)
WHERE ref = uuid(&order_number);
 
-- 2. Reserving products
INSERT INTO ledger.inventory_accounting (
    period, product_ref, warehouse_ref, quantity
)
SELECT 
    NOW(),
    oi.product_ref,
    &warehouse_ref,
    -oi.quantity  -- Decreasing the balance
FROM operation.customer_order_items oi
WHERE oi.document_ref = &order_ref;
 
-- 3. Creating financial postings
INSERT INTO ledger.general_ledger (
    transaction_date, document_ref, account_debit, account_credit, amount, description
) VALUES
(NOW(), &order_ref, '62.01', '90.01', &order_total, 'Shipment of goods to customer');

-- Product turnover with analytics
SELECT 
    p.name AS product_name,
    s.name AS supplier_name,
    w.name AS warehouse_name,
    SUM(CASE WHEN ia.quantity > 0 THEN ia.quantity ELSE 0 END) AS receipts,
    SUM(CASE WHEN ia.quantity < 0 THEN ABS(ia.quantity) ELSE 0 END) AS shipments,
    SUM(ia.cost_amount) AS balance_amount,
    AVG(CASE WHEN ia.quantity != 0 THEN ia.cost_amount / ia.quantity ELSE 0 END) AS average_cost,
    COUNT(DISTINCT ia.batch_number) AS batches_count
FROM ledger.inventory_accounting ia
JOIN dictionary.products p ON ia.product_ref = p.ref
LEFT JOIN dictionary.suppliers s ON ia.supplier_ref = s.ref
JOIN dictionary.warehouses w ON ia.warehouse_ref = w.ref
WHERE ia.period BETWEEN &start_date AND &end_date
GROUP BY p.ref, p.name, s.name, w.name
HAVING SUM(ABS(ia.quantity)) > 0
ORDER BY shipments DESC
TYPE LIST;