-- Idempotent migration: create Vendor Onboarding wizard and steps
-- Safe to run multiple times; will not duplicate rows
DO $$
DECLARE
  v_wizard_id INTEGER;
BEGIN
  -- Insert wizard if not exists (keep minimal columns to be compatible with different schemas)
  -- Select existing wizard id if present; otherwise reserve an unused id and insert with that id
  SELECT id INTO v_wizard_id FROM form_wizards WHERE name = 'Vendor Onboarding Wizard' LIMIT 1;
  IF v_wizard_id IS NULL THEN
    SELECT COALESCE(MAX(id), 0) + 1 INTO v_wizard_id FROM form_wizards;
    INSERT INTO form_wizards (id, name, description)
    VALUES (v_wizard_id, 'Vendor Onboarding Wizard', 'Public vendor onboarding wizard sequencing vendor, contact, address and documents');
    -- advance sequence if present
    BEGIN
      PERFORM setval('form_wizards_id_seq', (SELECT MAX(id) FROM form_wizards));
    EXCEPTION WHEN undefined_function THEN
      NULL;
    END;
  END IF;

  -- Helper to idempotently insert steps for the wizard
  DELETE FROM form_wizard_steps WHERE wizard_id = v_wizard_id AND form_id = 612;

  -- Step order and form_id pairs to insert
  PERFORM (
    WITH vals(step_order, form_id) AS (
      VALUES (1, 110), -- Vendor
        (2, 610), -- Vendor Contact
        (3, 611), -- Vendor Address
        (4, 613), -- Vendor Document (supporting docs)
        (5, 614)  -- Vendor Supporting Document / Certificates
    )
    -- Insert each step individually to avoid bulk insert conflicts
    INSERT INTO form_wizard_steps (wizard_id, step_number, form_id)
    SELECT v_wizard_id, v.step_order, v.form_id
    FROM vals v
    WHERE NOT EXISTS (
      SELECT 1 FROM form_wizard_steps s WHERE s.wizard_id = v_wizard_id AND s.form_id = v.form_id
    );
  );

  UPDATE forms
  SET elements = $json$
  [
    {"label":"Vendor","field_name":"vendor_id","data_column":"vendor_id","type_id":17,"order_index":1,"options_table":"vendors","options_label_column":"vendorname","options_value_column":"vendor_id"},
    {"label":"FNPF Compliance Document","field_name":"fnpf_document","type_id":9,"order_index":2,"doc_type":"fnpf_compliance","doc_field":"file_url","widget_options":{"maxFiles":1}},
    {"label":"FNPF Compliance Expiry Date","field_name":"fnpf_validity","type_id":3,"order_index":3,"doc_type":"fnpf_compliance","doc_field":"validity"},
    {"label":"FRCS VAT Clearance Certificate","field_name":"frcs_vat_document","type_id":9,"order_index":4,"doc_type":"frcs_vat_clearance","doc_field":"file_url","widget_options":{"maxFiles":1}},
    {"label":"FRCS VAT Clearance Expiry Date","field_name":"frcs_vat_validity","type_id":3,"order_index":5,"doc_type":"frcs_vat_clearance","doc_field":"validity"},
    {"label":"FRCS Tax Clearance Certificate","field_name":"frcs_tax_document","type_id":9,"order_index":6,"doc_type":"frcs_tax_clearance","doc_field":"file_url","widget_options":{"maxFiles":1}},
    {"label":"FRCS Tax Clearance Expiry Date","field_name":"frcs_tax_validity","type_id":3,"order_index":7,"doc_type":"frcs_tax_clearance","doc_field":"validity"},
    {"label":"FNU Levy Confirmation","field_name":"fnu_levy_document","type_id":9,"order_index":8,"doc_type":"fnu_levy","doc_field":"file_url","widget_options":{"maxFiles":1}},
    {"label":"FNU Levy Expiry Date","field_name":"fnu_levy_validity","type_id":3,"order_index":9,"doc_type":"fnu_levy","doc_field":"validity"},
    {"label":"Business Registration Document","field_name":"business_registration_document","type_id":9,"order_index":10,"doc_type":"business_registration","doc_field":"file_url","widget_options":{"maxFiles":1}},
    {"label":"Business Registration Expiry Date","field_name":"business_registration_validity","type_id":3,"order_index":11,"doc_type":"business_registration","doc_field":"validity"}
  ]
  $json$
  WHERE id = 613;
END $$;
