-- Workflow Engine v1.0 - Order workflow reference implementation
-- Run after 01_workflow_schema.sql and 02_workflow_master_data.sql.
-- This seed is idempotent and intentionally contains no module-specific PHP.

SET NAMES utf8mb4;
START TRANSACTION;

-- Reusable static option sets. The datasource engine reads configuration_json.
INSERT INTO workflow_datasource
    (code, name, datasource_type, configuration_json, cache_seconds, status, created_at)
VALUES
    ('order_type', 'Order Type', 'STATIC', '[{"value":"SALE","label":"Sales Order"},{"value":"SAMPLE","label":"Sample Order"},{"value":"RETURN","label":"Return Order"}]', 0, 1, NOW()),
    ('order_priority', 'Order Priority', 'STATIC', '[{"value":"NORMAL","label":"Normal"},{"value":"HIGH","label":"High"},{"value":"URGENT","label":"Urgent"}]', 0, 1, NOW()),
    ('payment_terms', 'Payment Terms', 'STATIC', '[{"value":"PREPAID","label":"Prepaid"},{"value":"NET_15","label":"Net 15"},{"value":"NET_30","label":"Net 30"},{"value":"NET_45","label":"Net 45"}]', 0, 1, NOW()),
    ('shipping_method', 'Shipping Method', 'STATIC', '[{"value":"PICKUP","label":"Customer Pickup"},{"value":"STANDARD","label":"Standard Delivery"},{"value":"EXPRESS","label":"Express Delivery"}]', 0, 1, NOW())
ON DUPLICATE KEY UPDATE
    name = VALUES(name), datasource_type = VALUES(datasource_type), configuration_json = VALUES(configuration_json),
    cache_seconds = VALUES(cache_seconds), status = VALUES(status), deleted_at = NULL, updated_at = NOW();

INSERT INTO workflow
    (code, name, description, uuid, status, created_at)
VALUES
    ('ORDER', 'Order', 'Reference workflow for creating and submitting customer orders.', UUID(), 'ACTIVE', NOW())
ON DUPLICATE KEY UPDATE
    name = VALUES(name), description = VALUES(description), status = VALUES(status), deleted_at = NULL,
    updated_at = NOW(), workflow_id = LAST_INSERT_ID(workflow_id);
SET @workflow_id = LAST_INSERT_ID();

INSERT INTO workflow_version
    (workflow_id, version_no, status, published_at, change_notes, created_at)
VALUES
    (@workflow_id, 1, 'ACTIVE', NOW(), 'Initial Order workflow reference definition.', NOW())
ON DUPLICATE KEY UPDATE
    status = VALUES(status), published_at = VALUES(published_at), change_notes = VALUES(change_notes),
    updated_at = NOW(), workflow_version_id = LAST_INSERT_ID(workflow_version_id);
SET @workflow_version_id = LAST_INSERT_ID();

INSERT INTO workflow_group
    (workflow_version_id, code, title, description, icon, is_skippable, status, sort_order, created_at)
VALUES
    (@workflow_version_id, 'ORDER_DETAILS', 'Order Details', 'Core order identification and commercial details.', 'ki-outline ki-document', 0, 1, 10, NOW()),
    (@workflow_version_id, 'CUSTOMER', 'Customer', 'Customer and billing information.', 'ki-outline ki-profile-user', 0, 1, 20, NOW()),
    (@workflow_version_id, 'ITEMS', 'Items', 'Order line items and calculated totals.', 'ki-outline ki-basket', 0, 1, 30, NOW()),
    (@workflow_version_id, 'DELIVERY', 'Delivery', 'Delivery method, address and schedule.', 'ki-outline ki-truck', 0, 1, 40, NOW()),
    (@workflow_version_id, 'REVIEW', 'Review & Submit', 'Final review before submission.', 'ki-outline ki-check-circle', 0, 1, 50, NOW())
ON DUPLICATE KEY UPDATE
    title = VALUES(title), description = VALUES(description), icon = VALUES(icon), is_skippable = VALUES(is_skippable),
    status = VALUES(status), sort_order = VALUES(sort_order), updated_at = NOW();

SELECT workflow_group_id INTO @group_order_details FROM workflow_group WHERE workflow_version_id = @workflow_version_id AND code = 'ORDER_DETAILS';
SELECT workflow_group_id INTO @group_customer FROM workflow_group WHERE workflow_version_id = @workflow_version_id AND code = 'CUSTOMER';
SELECT workflow_group_id INTO @group_items FROM workflow_group WHERE workflow_version_id = @workflow_version_id AND code = 'ITEMS';
SELECT workflow_group_id INTO @group_delivery FROM workflow_group WHERE workflow_version_id = @workflow_version_id AND code = 'DELIVERY';
SELECT workflow_group_id INTO @group_review FROM workflow_group WHERE workflow_version_id = @workflow_version_id AND code = 'REVIEW';

INSERT INTO workflow_block
    (workflow_group_id, code, title, description, layout_columns, status, sort_order, created_at)
VALUES
    (@group_order_details, 'ORDER_HEADER', 'Order Header', 'Identification and commercial terms.', 12, 1, 10, NOW()),
    (@group_customer, 'CUSTOMER_DETAILS', 'Customer Details', 'Customer contact and invoice details.', 12, 1, 10, NOW()),
    (@group_items, 'ORDER_LINES', 'Order Lines', 'Add one or more items to this order.', 12, 1, 10, NOW()),
    (@group_delivery, 'DELIVERY_DETAILS', 'Delivery Details', 'Delivery preference and destination.', 12, 1, 10, NOW()),
    (@group_review, 'ORDER_SUMMARY', 'Order Summary', 'Confirm the supplied order information.', 12, 1, 10, NOW())
ON DUPLICATE KEY UPDATE
    title = VALUES(title), description = VALUES(description), layout_columns = VALUES(layout_columns), status = VALUES(status),
    sort_order = VALUES(sort_order), updated_at = NOW();

SELECT workflow_block_id INTO @block_order_header FROM workflow_block WHERE workflow_group_id = @group_order_details AND code = 'ORDER_HEADER';
SELECT workflow_block_id INTO @block_customer_details FROM workflow_block WHERE workflow_group_id = @group_customer AND code = 'CUSTOMER_DETAILS';
SELECT workflow_block_id INTO @block_order_lines FROM workflow_block WHERE workflow_group_id = @group_items AND code = 'ORDER_LINES';
SELECT workflow_block_id INTO @block_delivery_details FROM workflow_block WHERE workflow_group_id = @group_delivery AND code = 'DELIVERY_DETAILS';
SELECT workflow_block_id INTO @block_order_summary FROM workflow_block WHERE workflow_group_id = @group_review AND code = 'ORDER_SUMMARY';

SELECT workflow_component_type_id INTO @component_form FROM workflow_component_type WHERE code = 'FORM';
SELECT workflow_component_type_id INTO @component_repeater FROM workflow_component_type WHERE code = 'REPEATER';
SELECT workflow_component_type_id INTO @component_summary FROM workflow_component_type WHERE code = 'SUMMARY';
SELECT workflow_datasource_id INTO @ds_order_type FROM workflow_datasource WHERE code = 'order_type';
SELECT workflow_datasource_id INTO @ds_order_priority FROM workflow_datasource WHERE code = 'order_priority';
SELECT workflow_datasource_id INTO @ds_payment_terms FROM workflow_datasource WHERE code = 'payment_terms';
SELECT workflow_datasource_id INTO @ds_shipping_method FROM workflow_datasource WHERE code = 'shipping_method';

INSERT INTO workflow_field
    (workflow_block_id, workflow_component_type_id, workflow_datasource_id, code, label, field_type, placeholder, default_value, grid_columns, is_required, is_readonly, is_multiple, status, sort_order, properties_json, created_at)
VALUES
    (@block_order_header, @component_form, NULL, 'order_date', 'Order Date', 'DATE', NULL, NULL, 3, 1, 0, 0, 1, 10, NULL, NOW()),
    (@block_order_header, @component_form, @ds_order_type, 'order_type', 'Order Type', 'SELECT', 'Select order type', 'SALE', 3, 1, 0, 0, 1, 20, NULL, NOW()),
    (@block_order_header, @component_form, @ds_order_priority, 'priority', 'Priority', 'SELECT', 'Select priority', 'NORMAL', 3, 1, 0, 0, 1, 30, NULL, NOW()),
    (@block_order_header, @component_form, @ds_payment_terms, 'payment_term', 'Payment Term', 'SELECT', 'Select payment term', NULL, 3, 1, 0, 0, 1, 40, NULL, NOW()),
    (@block_customer_details, @component_form, NULL, 'customer_name', 'Customer Name', 'TEXT', 'Enter customer name', NULL, 6, 1, 0, 0, 1, 10, NULL, NOW()),
    (@block_customer_details, @component_form, NULL, 'customer_email', 'Customer Email', 'EMAIL', 'name@example.com', NULL, 6, 0, 0, 0, 1, 20, NULL, NOW()),
    (@block_customer_details, @component_form, NULL, 'customer_phone', 'Customer Phone', 'TEXT', 'Enter phone number', NULL, 6, 1, 0, 0, 1, 30, NULL, NOW()),
    (@block_customer_details, @component_form, NULL, 'billing_address', 'Billing Address', 'TEXTAREA', 'Enter billing address', NULL, 12, 1, 0, 0, 1, 40, NULL, NOW()),
    (@block_order_lines, @component_repeater, NULL, 'item_name', 'Item Name', 'TEXT', 'Enter item name', NULL, 4, 1, 0, 0, 1, 10, '{"repeater":"order_lines"}', NOW()),
    (@block_order_lines, @component_repeater, NULL, 'quantity', 'Quantity', 'NUMBER', '0', NULL, 2, 1, 0, 0, 1, 20, '{"repeater":"order_lines","decimal_places":3}', NOW()),
    (@block_order_lines, @component_repeater, NULL, 'unit_rate', 'Unit Rate', 'NUMBER', '0.00', NULL, 2, 1, 0, 0, 1, 30, '{"repeater":"order_lines","decimal_places":2}', NOW()),
    (@block_order_lines, @component_repeater, NULL, 'line_amount', 'Line Amount', 'NUMBER', NULL, NULL, 2, 0, 1, 0, 1, 40, '{"repeater":"order_lines","decimal_places":2}', NOW()),
    (@block_order_lines, @component_repeater, NULL, 'line_note', 'Line Note', 'TEXT', 'Optional note', NULL, 2, 0, 0, 0, 1, 50, '{"repeater":"order_lines"}', NOW()),
    (@block_delivery_details, @component_form, @ds_shipping_method, 'shipping_method', 'Shipping Method', 'SELECT', 'Select shipping method', NULL, 4, 1, 0, 0, 1, 10, NULL, NOW()),
    (@block_delivery_details, @component_form, NULL, 'delivery_date', 'Requested Delivery Date', 'DATE', NULL, NULL, 4, 0, 0, 0, 1, 20, NULL, NOW()),
    (@block_delivery_details, @component_form, NULL, 'shipping_address', 'Shipping Address', 'TEXTAREA', 'Enter shipping address', NULL, 12, 1, 0, 0, 1, 30, NULL, NOW()),
    (@block_order_summary, @component_summary, NULL, 'order_total', 'Order Total', 'NUMBER', NULL, NULL, 4, 0, 1, 0, 1, 10, '{"summary":"sum(order_lines.line_amount)","decimal_places":2}', NOW()),
    (@block_order_summary, @component_summary, NULL, 'special_instructions', 'Special Instructions', 'TEXTAREA', 'Optional instructions for fulfilment', NULL, 8, 0, 0, 0, 1, 20, NULL, NOW())
ON DUPLICATE KEY UPDATE
    workflow_component_type_id = VALUES(workflow_component_type_id), workflow_datasource_id = VALUES(workflow_datasource_id),
    label = VALUES(label), field_type = VALUES(field_type), placeholder = VALUES(placeholder), default_value = VALUES(default_value),
    grid_columns = VALUES(grid_columns), is_required = VALUES(is_required), is_readonly = VALUES(is_readonly),
    is_multiple = VALUES(is_multiple), status = VALUES(status), sort_order = VALUES(sort_order), properties_json = VALUES(properties_json), updated_at = NOW();

SELECT workflow_field_id INTO @field_customer_email FROM workflow_field WHERE workflow_block_id = @block_customer_details AND code = 'customer_email';
SELECT workflow_field_id INTO @field_quantity FROM workflow_field WHERE workflow_block_id = @block_order_lines AND code = 'quantity';
SELECT workflow_field_id INTO @field_unit_rate FROM workflow_field WHERE workflow_block_id = @block_order_lines AND code = 'unit_rate';
SELECT workflow_field_id INTO @field_line_amount FROM workflow_field WHERE workflow_block_id = @block_order_lines AND code = 'line_amount';
SELECT workflow_field_id INTO @field_delivery_date FROM workflow_field WHERE workflow_block_id = @block_delivery_details AND code = 'delivery_date';

INSERT INTO workflow_validation
    (workflow_field_id, validation_type, rule_value, error_message, status, sort_order, created_at)
SELECT @field_customer_email, 'REGEX', '^[^\\s@]+@[^\\s@]+\\.[^\\s@]+$', 'Enter a valid customer email address.', 1, 10, NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM workflow_validation WHERE workflow_field_id = @field_customer_email AND validation_type = 'REGEX'
);

INSERT INTO workflow_validation
    (workflow_field_id, validation_type, rule_value, error_message, status, sort_order, created_at)
SELECT @field_quantity, 'MIN', '0.001', 'Quantity must be greater than zero.', 1, 10, NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM workflow_validation WHERE workflow_field_id = @field_quantity AND validation_type = 'MIN'
);

INSERT INTO workflow_validation
    (workflow_field_id, validation_type, rule_value, error_message, status, sort_order, created_at)
SELECT @field_unit_rate, 'MIN', '0', 'Unit rate cannot be negative.', 1, 10, NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM workflow_validation WHERE workflow_field_id = @field_unit_rate AND validation_type = 'MIN'
);

INSERT INTO workflow_calculation
    (workflow_field_id, calculation_type, expression, trigger_event, status, sort_order, properties_json, created_at)
SELECT @field_line_amount, 'FORMULA', 'quantity * unit_rate', 'CHANGE', 1, 10, '{"depends_on":["quantity","unit_rate"],"scope":"repeater_row"}', NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM workflow_calculation WHERE workflow_field_id = @field_line_amount AND calculation_type = 'FORMULA' AND expression = 'quantity * unit_rate'
);

-- A delivery date is mandatory for either delivery service, but not for customer pickup.
INSERT INTO workflow_condition
    (workflow_version_id, target_type, target_id, action_type, condition_group, logical_operator, source_field_code, comparison_operator, comparison_value, status, sort_order, created_at)
SELECT @workflow_version_id, 'FIELD', @field_delivery_date, 'REQUIRED', 'delivery_schedule', 'OR', 'shipping_method', 'IN', '["STANDARD","EXPRESS"]', 1, 10, NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM workflow_condition
    WHERE workflow_version_id = @workflow_version_id AND target_type = 'FIELD' AND target_id = @field_delivery_date
      AND action_type = 'REQUIRED' AND condition_group = 'delivery_schedule' AND source_field_code = 'shipping_method'
);

COMMIT;