-- Update employee forms to use position_id dropdown instead of position text field

-- Update employee_create form: Change position field to dropdown
UPDATE form_fields 
SET 
    field_name = 'position_id',
    field_label = 'Position',
    field_type = 'select',
    options_source = 'query',
    options_query = 'SELECT id as value, CONCAT(title, " (", COALESCE(department, "No Dept"), ")") as label FROM positions WHERE is_active = 1 ORDER BY department, title'
WHERE form_config_id = (SELECT id FROM form_configurations WHERE form_key = 'employee_create')
AND field_name = 'position';

-- Update employee_edit form: Change position field to dropdown
UPDATE form_fields 
SET 
    field_name = 'position_id',
    field_label = 'Position',
    field_type = 'select',
    options_source = 'query',
    options_query = 'SELECT id as value, CONCAT(title, " (", COALESCE(department, "No Dept"), ")") as label FROM positions WHERE is_active = 1 ORDER BY department, title'
WHERE form_config_id = (SELECT id FROM form_configurations WHERE form_key = 'employee_edit')
AND field_name = 'position';