-- Diagnose and fix employee form position field

-- Check current state
SELECT 
    'BEFORE UPDATE' as stage,
    fc.form_key,
    ff.field_name,
    ff.field_type,
    ff.options_source,
    ff.options_query
FROM form_fields ff
JOIN form_configurations fc ON ff.form_config_id = fc.id
WHERE fc.form_key IN ('employee_create', 'employee_edit')
AND ff.field_name IN ('position', 'position_id');

-- Update employee_create form
UPDATE form_fields ff
JOIN form_configurations fc ON ff.form_config_id = fc.id
SET 
    ff.field_name = 'position_id',
    ff.field_label = 'Position',
    ff.field_type = 'select',
    ff.options_source = 'query',
    ff.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 fc.form_key = 'employee_create'
AND ff.field_name IN ('position', 'position_id');

-- Update employee_edit form
UPDATE form_fields ff
JOIN form_configurations fc ON ff.form_config_id = fc.id
SET 
    ff.field_name = 'position_id',
    ff.field_label = 'Position',
    ff.field_type = 'select',
    ff.options_source = 'query',
    ff.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 fc.form_key = 'employee_edit'
AND ff.field_name IN ('position', 'position_id');

-- Check after update
SELECT 
    'AFTER UPDATE' as stage,
    fc.form_key,
    ff.field_name,
    ff.field_type,
    ff.options_source,
    LEFT(ff.options_query, 50) as options_query_preview
FROM form_fields ff
JOIN form_configurations fc ON ff.form_config_id = fc.id
WHERE fc.form_key IN ('employee_create', 'employee_edit')
AND ff.field_name IN ('position', 'position_id');