-- ============================================================================
-- Phase 7: Multi-Unit Inventory Transfer Form
-- Migration: update_transfer_form_bulk_breaking.sql
-- Purpose: Update inventory transfer form to support bulk breaking
-- ============================================================================

-- Delete existing inventory transfer form
DELETE FROM forms WHERE name = 'inventory_transfer_form';

-- Create enhanced inventory transfer form with bulk breaking support
INSERT INTO forms (name, title, description, table_name, fields, submit_label, cancel_label)
VALUES (
    'inventory_transfer_form',
    'Inventory Transfer',
    'Transfer inventory between warehouses/branches with optional bulk breaking',
    'inventory_transfers',
    '[
        {
            "name": "from_branch_id",
            "label": "From Warehouse/Branch",
            "type": "select-search",
            "required": true,
            "dataSource": "/api/branches",
            "optionLabel": "name",
            "optionValue": "branch_id",
            "helpText": "Source location"
        },
        {
            "name": "to_branch_id",
            "label": "To Warehouse/Branch",
            "type": "select-search",
            "required": true,
            "dataSource": "/api/branches",
            "optionLabel": "name",
            "optionValue": "branch_id",
            "helpText": "Destination location"
        },
        {
            "name": "transfer_date",
            "label": "Transfer Date",
            "type": "date",
            "required": true,
            "default": "today"
        },
        {
            "name": "reference_number",
            "label": "Reference Number",
            "type": "text",
            "required": false,
            "placeholder": "Optional reference number"
        },
        {
            "type": "divider"
        },
        {
            "type": "heading",
            "label": "Items to Transfer",
            "level": 3,
            "colspan": 2
        },
        {
            "name": "items",
            "label": "Products",
            "type": "shopping-cart",
            "required": true,
            "colspan": 2,
            "helpText": "Add products to transfer. You can break bulk units at destination.",
            "columns": [
                {
                    "name": "product_id",
                    "label": "Product",
                    "type": "select-search",
                    "required": true,
                    "dataSource": "/api/products",
                    "optionLabel": "name",
                    "optionValue": "product_id",
                    "width": "25%"
                },
                {
                    "name": "quantity",
                    "label": "Quantity",
                    "type": "number",
                    "required": true,
                    "min": "0.001",
                    "step": "0.001",
                    "width": "12%",
                    "helpText": "Quantity to transfer"
                },
                {
                    "name": "unit_type",
                    "label": "Send As",
                    "type": "select",
                    "required": true,
                    "options": [
                        {"label": "Piece", "value": "PIECE"},
                        {"label": "Carton", "value": "CARTON"},
                        {"label": "Pallet", "value": "PALLET"},
                        {"label": "Box", "value": "BOX"},
                        {"label": "Case", "value": "CASE"},
                        {"label": "Bag", "value": "BAG"},
                        {"label": "Bottle", "value": "BOTTLE"},
                        {"label": "Can", "value": "CAN"},
                        {"label": "KG", "value": "KG"},
                        {"label": "Litre", "value": "L"}
                    ],
                    "width": "13%",
                    "helpText": "Unit being sent"
                },
                {
                    "name": "break_bulk",
                    "label": "Break?",
                    "type": "checkbox",
                    "helpText": "Break down to smaller units at destination",
                    "width": "10%"
                },
                {
                    "name": "break_to_unit",
                    "label": "Receive As",
                    "type": "select",
                    "required": false,
                    "options": [
                        {"label": "Piece", "value": "PIECE"},
                        {"label": "Carton", "value": "CARTON"},
                        {"label": "Box", "value": "BOX"},
                        {"label": "Bottle", "value": "BOTTLE"},
                        {"label": "Can", "value": "CAN"},
                        {"label": "Sachet", "value": "SACHET"},
                        {"label": "Pack", "value": "PACK"},
                        {"label": "KG", "value": "KG"},
                        {"label": "Gram", "value": "G"}
                    ],
                    "width": "13%",
                    "visible_when": "break_bulk",
                    "helpText": "Unit to receive (must be smaller)"
                },
                {
                    "name": "batch_number",
                    "label": "Batch #",
                    "type": "text",
                    "required": false,
                    "width": "12%",
                    "placeholder": "Optional"
                },
                {
                    "name": "notes",
                    "label": "Notes",
                    "type": "text",
                    "required": false,
                    "width": "15%",
                    "placeholder": "Item notes"
                }
            ]
        },
        {
            "type": "info-box",
            "level": "info",
            "message": "💡 Bulk Breaking: Check ''Break?'' to convert units at destination. Example: Send 2 CARTON, break to PIECE at destination (receives 48 PIECE if 1 carton = 24 pieces)",
            "colspan": 2
        },
        {
            "type": "divider"
        },
        {
            "name": "notes",
            "label": "Transfer Notes",
            "type": "textarea",
            "required": false,
            "placeholder": "Additional notes about this transfer (reason, special handling, etc.)",
            "colspan": 2,
            "rows": 3
        }
    ]'::jsonb,
    'Create Transfer',
    'Cancel'
);

COMMENT ON TABLE inventory_transfers IS 'Tracks inventory transfers between branches/warehouses with support for multi-unit products and bulk breaking';

SELECT 'Inventory transfer form updated with bulk breaking support!' as status;
