-- ============================================================================
-- Form Rules Engine Examples
-- ============================================================================
-- Demonstrates field-level and form-level rules using the FormRulesEngine
-- Includes calculations, validations with HALT conditions, and async rules
-- Author: Cybex PTE LTD
-- Date: 2026-01-10
-- ============================================================================

-- Example 1: Invoice Form with VAT Calculations
-- ==============================================

-- Update invoice form fields with calculation rules
UPDATE form_elements 
SET validation = jsonb_set(
    COALESCE(validation, '{}'::jsonb),
    '{formula}',
    '"quantity * unit_price"'::jsonb
),
validation = jsonb_set(
    validation,
    '{format}',
    '"currency"'::jsonb
),
validation = jsonb_set(
    validation,
    '{dependencies}',
    '["quantity", "unit_price"]'::jsonb
)
WHERE form_id IN (SELECT id FROM forms WHERE name ILIKE '%invoice%')
AND field_name = 'line_total';

-- Add VAT calculation field
UPDATE form_elements 
SET validation = jsonb_set(
    COALESCE(validation, '{}'::jsonb),
    '{formula}',
    '"line_total * 0.125"'::jsonb
),
validation = jsonb_set(
    validation,
    '{format}',
    '"currency"'::jsonb
),
validation = jsonb_set(
    validation,
    '{dependencies}',
    '["line_total"]'::jsonb
),
is_required = false
WHERE form_id IN (SELECT id FROM forms WHERE name ILIKE '%invoice%')
AND field_name = 'vat_amount';

-- Example 2: Date Validation with HALT
-- ======================================

-- Add date validation rule to ensure due_date > invoice_date
UPDATE form_elements 
SET validation = jsonb_set(
    COALESCE(validation, '{}'::jsonb),
    '{rules}',
    '[
        {
            "expression": "new Date(value) > new Date(invoice_date)",
            "message": "Due date must be after invoice date",
            "halt": true
        }
    ]'::jsonb
)
WHERE field_name = 'due_date';

-- Example 3: Quantity Validation
-- ================================

-- Add quantity validation with min/max
UPDATE form_elements 
SET validation = jsonb_set(
    COALESCE(validation, '{}'::jsonb),
    '{min}',
    '1'::jsonb
),
validation = jsonb_set(
    validation,
    '{max}',
    '10000'::jsonb
),
validation = jsonb_set(
    validation,
    '{rules}',
    '[
        {
            "expression": "value > 0",
            "message": "Quantity must be positive",
            "halt": false
        },
        {
            "expression": "Number.isInteger(Number(value))",
            "message": "Quantity must be a whole number",
            "halt": true
        }
    ]'::jsonb
)
WHERE field_name IN ('quantity', 'qty');

-- Example 4: Email Validation with Built-in Helper
-- ==================================================

UPDATE form_elements 
SET validation = jsonb_set(
    COALESCE(validation, '{}'::jsonb),
    '{rules}',
    '[
        {
            "expression": "helpers.isEmail(value)",
            "message": "Please enter a valid email address",
            "halt": true
        }
    ]'::jsonb
)
WHERE field_name = 'email' OR field_name LIKE '%email%';

-- Example 5: Form-Level Validation (Cross-Field)
-- ================================================

-- Add form-level validation to forms table
UPDATE forms 
SET validation = jsonb_set(
    COALESCE(validation, '{}'::jsonb),
    '{rules}',
    '[
        {
            "expression": "total_amount > 0",
            "message": "Invoice total must be greater than zero",
            "halt": false
        },
        {
            "expression": "helpers.daysBetween(invoice_date, due_date) >= 0",
            "message": "Due date must be on or after invoice date",
            "halt": true
        }
    ]'::jsonb
)
WHERE name ILIKE '%invoice%';

-- Example 6: Discount Calculation
-- =================================

UPDATE form_elements 
SET validation = jsonb_set(
    COALESCE(validation, '{}'::jsonb),
    '{formula}',
    '"subtotal * (discount_percentage / 100)"'::jsonb
),
validation = jsonb_set(
    validation,
    '{format}',
    '"currency"'::jsonb
),
validation = jsonb_set(
    validation,
    '{dependencies}',
    '["subtotal", "discount_percentage"]'::jsonb
)
WHERE field_name = 'discount_amount';

-- Example 7: Grand Total with Multiple Dependencies
-- ===================================================

UPDATE form_elements 
SET validation = jsonb_set(
    COALESCE(validation, '{}'::jsonb),
    '{formula}',
    '"subtotal - discount_amount + vat_amount"'::jsonb
),
validation = jsonb_set(
    validation,
    '{format}',
    '"currency"'::jsonb
),
validation = jsonb_set(
    validation,
    '{dependencies}',
    '["subtotal", "discount_amount", "vat_amount"]'::jsonb
)
WHERE field_name IN ('grand_total', 'total_amount', 'final_amount');

-- Example 8: Conditional Validation
-- ===================================

UPDATE form_elements 
SET validation = jsonb_set(
    COALESCE(validation, '{}'::jsonb),
    '{rules}',
    '[
        {
            "expression": "payment_method !== \"credit\" || credit_limit > total_amount",
            "message": "Total amount exceeds available credit limit",
            "halt": true
        }
    ]'::jsonb
)
WHERE field_name = 'payment_method';

-- Example 9: Complex Calculation with Helper Functions
-- ======================================================

-- Add a field that calculates average item price
UPDATE form_elements 
SET validation = jsonb_set(
    COALESCE(validation, '{}'::jsonb),
    '{formula}',
    '"helpers.round(helpers.sum(line_items, \"unit_price\") / helpers.count(line_items), 2)"'::jsonb
),
validation = jsonb_set(
    validation,
    '{format}',
    '"currency"'::jsonb
),
validation = jsonb_set(
    validation,
    '{dependencies}',
    '["line_items"]'::jsonb
)
WHERE field_name = 'average_item_price';

-- Example 10: Date-based Calculation
-- ====================================

UPDATE form_elements 
SET validation = jsonb_set(
    COALESCE(validation, '{}'::jsonb),
    '{formula}',
    '"helpers.addDays(start_date, duration_days)"'::jsonb
),
validation = jsonb_set(
    validation,
    '{dependencies}',
    '["start_date", "duration_days"]'::jsonb
)
WHERE field_name = 'end_date';

-- Example 11: Percentage Formatting
-- ===================================

UPDATE form_elements 
SET validation = jsonb_set(
    COALESCE(validation, '{}'::jsonb),
    '{formula}',
    '"(paid_amount / total_amount)"'::jsonb
),
validation = jsonb_set(
    validation,
    '{format}',
    '"percentage"'::jsonb
),
validation = jsonb_set(
    validation,
    '{dependencies}',
    '["paid_amount", "total_amount"]'::jsonb
)
WHERE field_name = 'payment_percentage';

-- ============================================================================
-- Notes:
-- ============================================================================
-- 1. All formulas are JavaScript expressions evaluated in the FormRulesEngine
-- 2. Dependencies array lists field names that trigger recalculation
-- 3. HALT condition stops further validation when true
-- 4. Helper functions available: sum, avg, count, max, min, round, abs, 
--    daysBetween, addDays, isEmail, isPhone, matches, inRange, etc.
-- 5. Async validation requires custom validator function (not shown in SQL)
-- 6. Field-level rules run first, then form-level rules
-- 7. Format options: 'currency', 'percentage', 'decimal:N'
-- ============================================================================
