-- Migration for defaults.sql changes from CaseWallet to caseB

-- 1. Add select-search element type
INSERT INTO element_types (name, html_type, sql_type) VALUES
  ('select-search', 'select-search', 'VARCHAR(255)');

-- 2. Update Vendor Management module to add module_id
UPDATE modules SET module_id = '0194a8b2-0017-7123-8123-123456789abc'::uuid
WHERE name = 'Vendor Management' AND module_id IS NULL;

-- 3. Update products form elements (id 110) - add VAT and STT fields
UPDATE forms SET elements = '[
  {"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
  {"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":2},
  {"label":"Price","field_name":"price","data_column":"price","type_id":1,"order_index":3},
  {"label":"Quantity","field_name":"quantity","data_column":"quantity","type_id":1,"order_index":4},
  {"label":"Barcode (GTIN)","field_name":"barcode","data_column":"barcode","type_id":1,"order_index":5},
  {"label":"Product Number","field_name":"product_number","data_column":"product_number","type_id":1,"order_index":6},
  {"label":"Brand","field_name":"brand","data_column":"brand","type_id":1,"order_index":7},
  {"label":"Category","field_name":"category_id","data_column":"category_id","type_id":13,"options_table":"product_categories","options_label_column":"name","options_value_column":"id","order_index":8},
  {"label":"VAT","field_name":"vat","json_column":"tax_rates","type_id":4,"order_index":9,"options":[{"display_text":"12.5%","value":"G"},{"display_text":"0%","value":"A"}]},
  {"label":"STT","field_name":"stt","json_column":"tax_rates","type_id":4,"order_index":10,"options":[{"display_text":"Not Applicable","value":""},{"display_text":"6%","value":"E"}]}
]'
WHERE id = 110;

-- 4. Update Manage Restaurant Products form elements
UPDATE forms SET elements = '[
  {"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
  {"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":2},
  {"label":"Quantity","field_name":"quantity","data_column":"quantity","type_id":1,"order_index":3},
  {"label":"Price","field_name":"price","data_column":"price","type_id":1,"order_index":4},
  {"label":"Barcode (GTIN)","field_name":"barcode","data_column":"barcode","type_id":1,"order_index":5},
  {"label":"Product Number","field_name":"product_number","data_column":"product_number","type_id":1,"order_index":6},
  {"label":"Brand","field_name":"brand","data_column":"brand","type_id":1,"order_index":7},
  {"label":"Category","field_name":"category_id","data_column":"category_id","type_id":13,"options_table":"product_categories","options_label_column":"name","options_value_column":"category_id","order_index":8},
  {"label":"VAT","field_name":"vat","json_column":"tax_rates","type_id":4,"order_index":10,"options":[{"display_text":"12.5%","value":"G"},{"display_text":"0%","value":"A"}]},
  {"label":"STT","field_name":"stt","json_column":"tax_rates","type_id":4,"order_index":11,"options":[{"display_text":"Not Applicable","value":""},{"display_text":"6%","value":"E"}]},
  {"label":"Ingredients","field_name":"ingredients","type_id":22,"columns":[{"name":"ingredient_id","label":"Ingredient","type":"select-search","options_table":"ingredients","options_label_column":"name","options_value_column":"id"},{"name":"quantity","label":"Quantity","type":"number"},{"name":"units","label":"Units","type":"select","options":[{"display_text":"Pieces","value":"pieces"},{"display_text":"Kg","value":"kg"},{"display_text":"Grams","value":"grams"},{"display_text":"Liters","value":"liters"},{"display_text":"Ml","value":"ml"}]}],"link_table":"ingredients_to_products","link_columns":{"product_id":"id","ingredient_id":"ingredient_id","quantity":"quantity","units":"unit"},"order_index":9}
]'
WHERE name = 'Manage Restaurant Products';

-- 5. Insert new Ingredients form (id 116)
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id) VALUES (
  116,
  'Ingredients',
  'Manage Ingredients',
  'ingredients',
  '[
    {"label":"Ingredient Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
    {"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":2},
    {"label":"Quantity","field_name":"quantity","data_column":"quantity","type_id":1,"order_index":3},
    {"label":"Units","field_name":"units","data_column":"units","type_id":4,"order_index":4,"options":[{"display_text":"Pieces","value":"pieces"},{"display_text":"Kg","value":"kg"},{"display_text":"Grams","value":"grams"},{"display_text":"Liters","value":"liters"},{"display_text":"Ml","value":"ml"}]},
    {"label":"Price","field_name":"price","data_column":"price","type_id":1,"order_index":5}
  ]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- 6. Update Ingredients module config to use new form ID
UPDATE modules SET config = '{
  "title": "Ingredients",
  "header": [{"type": "button", "label": "Add Ingredient", "formName": "Ingredient"}],
  "body": [
    {
      "type": "table",
      "options": {
        "tableName": "ingredients",
        "editFormId": 116
      }
    }
  ],
  "footer": [{"type": "button", "label": "Close", "onClick": "closeModal"}]
}'
WHERE name = 'Ingredients';
