-- ============================================================================
-- Phase 7: Multi-Unit Product Form Configuration
-- Migration: update_product_form_multi_unit.sql
-- Purpose: Update product form to support multi-unit configuration
-- ============================================================================

-- Delete existing product form
DELETE FROM forms WHERE name = 'product_form';

-- Create enhanced product form with multi-unit support
INSERT INTO forms (name, title, description, table_name, fields, submit_label, cancel_label)
VALUES (
    'product_form',
    'Product Form',
    'Create or edit product with multi-unit configuration',
    'products',
    '[
        {
            "name": "name",
            "label": "Product Name",
            "type": "text",
            "required": true,
            "placeholder": "Enter product name",
            "colspan": 2
        },
        {
            "name": "description",
            "label": "Description",
            "type": "textarea",
            "required": false,
            "placeholder": "Product description",
            "colspan": 2,
            "rows": 3
        },
        {
            "name": "barcode",
            "label": "Barcode/SKU",
            "type": "text",
            "required": false,
            "placeholder": "Product barcode or SKU"
        },
        {
            "name": "product_number",
            "label": "Product Number",
            "type": "text",
            "required": false,
            "placeholder": "Internal product number"
        },
        {
            "name": "category_id",
            "label": "Category",
            "type": "select-search",
            "required": false,
            "dataSource": "/api/product_categories",
            "optionLabel": "name",
            "optionValue": "category_id"
        },
        {
            "name": "brand",
            "label": "Brand",
            "type": "text",
            "required": false,
            "placeholder": "Product brand"
        },
        {
            "name": "price",
            "label": "Base Unit Price",
            "type": "number",
            "required": true,
            "placeholder": "0.00",
            "step": "0.01",
            "helpText": "Price per base unit"
        },
        {
            "name": "size",
            "label": "Size/Weight",
            "type": "text",
            "required": false,
            "placeholder": "e.g., 500ml, 1kg"
        },
        {
            "type": "divider"
        },
        {
            "type": "heading",
            "label": "Multi-Unit Configuration",
            "level": 3,
            "helpText": "Configure product unit hierarchy for bulk handling and breaking",
            "colspan": 2
        },
        {
            "name": "base_unit",
            "label": "Base Unit (Smallest)",
            "type": "select",
            "required": true,
            "options": [
                {"label": "Piece", "value": "PIECE"},
                {"label": "Can", "value": "CAN"},
                {"label": "Bottle", "value": "BOTTLE"},
                {"label": "Sachet", "value": "SACHET"},
                {"label": "Pack", "value": "PACK"},
                {"label": "Kilogram (KG)", "value": "KG"},
                {"label": "Gram (G)", "value": "G"},
                {"label": "Litre (L)", "value": "L"},
                {"label": "Millilitre (ML)", "value": "ML"},
                {"label": "Meter (M)", "value": "M"},
                {"label": "Centimeter (CM)", "value": "CM"}
            ],
            "default": "PIECE",
            "helpText": "The smallest unit for selling/tracking (e.g., PIECE, BOTTLE, KG)"
        },
        {
            "name": "master_unit",
            "label": "Master Unit (Level 1 Bulk)",
            "type": "select",
            "required": false,
            "options": [
                {"label": "-- None --", "value": ""},
                {"label": "Carton", "value": "CARTON"},
                {"label": "Box", "value": "BOX"},
                {"label": "Case", "value": "CASE"},
                {"label": "Bag", "value": "BAG"},
                {"label": "Tray", "value": "TRAY"},
                {"label": "Bundle", "value": "BUNDLE"},
                {"label": "Crate", "value": "CRATE"},
                {"label": "Roll", "value": "ROLL"}
            ],
            "helpText": "First level bulk unit (e.g., CARTON, BOX)",
            "default": ""
        },
        {
            "name": "units_per_master",
            "label": "Base Units per Master Unit",
            "type": "number",
            "required": false,
            "placeholder": "e.g., 24",
            "min": "1",
            "step": "1",
            "helpText": "How many base units in one master unit (e.g., 24 bottles per carton)",
            "visible_when": "master_unit"
        },
        {
            "type": "info-box",
            "level": "info",
            "message": "Example: If base_unit=BOTTLE, master_unit=CARTON, units_per_master=24, then 1 CARTON = 24 BOTTLES",
            "visible_when": "master_unit",
            "colspan": 2
        },
        {
            "name": "master_unit_2",
            "label": "Master Unit 2 (Level 2 Bulk)",
            "type": "select",
            "required": false,
            "options": [
                {"label": "-- None --", "value": ""},
                {"label": "Pallet", "value": "PALLET"},
                {"label": "Container", "value": "CONTAINER"},
                {"label": "Skid", "value": "SKID"}
            ],
            "helpText": "Second level bulk unit (e.g., PALLET)",
            "visible_when": "master_unit",
            "default": ""
        },
        {
            "name": "units_per_master_2",
            "label": "Master Units per Master Unit 2",
            "type": "number",
            "required": false,
            "placeholder": "e.g., 48",
            "min": "1",
            "step": "1",
            "helpText": "How many master units in one master unit 2 (e.g., 48 cartons per pallet)",
            "visible_when": "master_unit_2"
        },
        {
            "type": "info-box",
            "level": "success",
            "message": "Complete Example: 1 PALLET = 48 CARTONS = 1,152 BOTTLES",
            "visible_when": "master_unit_2",
            "colspan": 2
        }
    ]'::jsonb,
    'Save Product',
    'Cancel'
);

COMMENT ON TABLE forms IS 'Form configurations for the application';

SELECT 'Product form updated with multi-unit support!' as status;
