-- Migration: Fix form dataSource properties to use options_table pattern
-- This fixes empty dropdowns in transfer, break bulk, and adjustment forms
-- Date: 2025-10-24

-- Update inventory_transfer_form
UPDATE forms 
SET elements = jsonb_set(
    jsonb_set(
        elements,
        '{0}', 
        '{"name": "from_branch_id", "label": "From Warehouse/Branch", "type": "select", "required": true, "options_table": "branches", "options_label_column": "name", "options_value_column": "branch_id"}'::jsonb
    ),
    '{1}',
    '{"name": "to_branch_id", "label": "To Warehouse/Branch", "type": "select", "required": true, "options_table": "branches", "options_label_column": "name", "options_value_column": "branch_id"}'::jsonb
)
WHERE name = 'inventory_transfer_form';

-- Update inventory_break_bulk_form - Complete rewrite to match backend expectations
UPDATE forms 
SET elements = '[
    {
        "name": "product_id",
        "label": "Product",
        "type": "select-search",
        "required": true,
        "options_table": "products",
        "options_label_column": "name",
        "options_value_column": "product_id",
        "help_text": "Select the product to break down into smaller units"
    },
    {
        "name": "branch_id",
        "label": "Warehouse/Branch",
        "type": "select",
        "required": true,
        "options_table": "branches",
        "options_label_column": "name",
        "options_value_column": "branch_id"
    },
    {
        "name": "from_unit",
        "label": "From Unit (Bulk)",
        "type": "select",
        "required": true,
        "options": [
            {"value": "CARTON", "label": "Carton", "display_text": "Carton"},
            {"value": "BOX", "label": "Box", "display_text": "Box"},
            {"value": "PACK", "label": "Pack", "display_text": "Pack"},
            {"value": "MASTER", "label": "Master Unit", "display_text": "Master Unit"}
        ],
        "help_text": "The larger unit you are breaking down"
    },
    {
        "name": "to_unit",
        "label": "To Unit (Individual)",
        "type": "select",
        "required": true,
        "options": [
            {"value": "PIECE", "label": "Piece", "display_text": "Piece"},
            {"value": "PACK", "label": "Pack", "display_text": "Pack"},
            {"value": "UNIT", "label": "Base Unit", "display_text": "Base Unit"}
        ],
        "help_text": "The smaller unit you are creating"
    },
    {
        "name": "quantity",
        "label": "Quantity to Break",
        "type": "number",
        "required": true,
        "min": 0.01,
        "help_text": "How many bulk units to break down"
    },
    {
        "name": "batch_number",
        "label": "Batch Number",
        "type": "text",
        "required": false
    },
    {
        "name": "notes",
        "label": "Notes",
        "type": "textarea",
        "required": false,
        "colspan": 2
    }
]'::jsonb
WHERE name = 'inventory_break_bulk_form';

-- Update inventory_adjustment_form - product_id (element 2)
UPDATE forms 
SET elements = jsonb_set(
    elements,
    '{2}',
    '{"name": "product_id", "label": "Product", "type": "select-search", "required": true, "options_table": "products", "options_label_column": "name", "options_value_column": "product_id", "visible_when": "!inventory_id"}'::jsonb
)
WHERE name = 'inventory_adjustment_form';

-- Update inventory_adjustment_form - branch_id (element 6)
UPDATE forms 
SET elements = jsonb_set(
    elements,
    '{6}',
    '{"name": "branch_id", "label": "Warehouse/Branch", "type": "select", "required": true, "options_table": "branches", "options_label_column": "name", "options_value_column": "branch_id"}'::jsonb
)
WHERE name = 'inventory_adjustment_form';

-- Verify updates
SELECT name, 
       jsonb_array_length(elements) as element_count,
       (elements->0->>'options_table') as first_field_options_table,
       (elements->0->>'dataSource') as first_field_datasource
FROM forms 
WHERE name IN ('inventory_transfer_form', 'inventory_break_bulk_form', 'inventory_adjustment_form')
ORDER BY name;
