-- Investigator user form and modal

-- Ensure workflow exists
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES ('Investigator Created', 'form.73.updated', '{"conditions":[]}', TRUE)
ON CONFLICT (name) DO NOTHING;

-- Investigator form mapped to users
INSERT INTO forms (id, name, description, mapped_table, on_submit_workflow_id)
VALUES (73, 'Investigator', 'Investigator user', 'users',
    (SELECT id FROM workflows WHERE name='Investigator Created' LIMIT 1));
INSERT INTO form_elements (form_id,label,field_name,data_column,type_id,order_index) VALUES
  (73,'full_name','full_name','full_name',1,1),
  (73,'emails','emails','emails',(SELECT id FROM element_types WHERE name='text-multiple' LIMIT 1),2),
  (73,'phones','phones','phones',(SELECT id FROM element_types WHERE name='text-multiple' LIMIT 1),3),
  (73,'password','password',NULL,(SELECT id FROM element_types WHERE name='text' LIMIT 1),4);
INSERT INTO table_views (form_id,name,columns,sort_by,sort_dir) VALUES
  (73,'Default','["full_name","emails","phones"]','full_name','ASC');
INSERT INTO ui_modals (name,config,tenant_id,branch_id,icon,aria_label,description) VALUES
  ('Investigators','{"title":"Investigators","header":[{"type":"button","label":"Add Investigator","formName":"Investigator"}],"body":[{"type":"table","options":{"tableName":"users","showGlobalSearch":false,"filterField":"groups.name","filterValue":"Investigators"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL,NULL,'fa-user-secret','Investigators','Investigators');

-- Ensure Investigators group exists
INSERT INTO groups (name, description)
SELECT 'Investigators', 'Investigations team'
WHERE NOT EXISTS (SELECT 1 FROM groups WHERE name = 'Investigators');
