-- Update cases table and form

-- Add new columns if they don't exist
ALTER TABLE cases
    ADD COLUMN IF NOT EXISTS lead_rep_id INTEGER DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS description TEXT DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS initial_instructions TEXT DEFAULT NULL;
CREATE INDEX IF NOT EXISTS cases_rep_idx ON cases (lead_rep_id);

-- Update client field to dropdown
UPDATE form_elements
SET label = 'client',
    type_id = (SELECT id FROM element_types WHERE name = 'dropdown' LIMIT 1),
    options_table = 'clients',
    options_label_column = 'name',
    options_value_column = 'id'
WHERE form_id = 36 AND field_name = 'client_id';

-- Add Assigned Representative dropdown
INSERT INTO form_elements (
    form_id, label, field_name, data_column, type_id,
    options_table, options_label_column, options_value_column, order_index)
SELECT 36, 'assigned_representative', 'lead_rep_id', 'lead_rep_id',
    (SELECT id FROM element_types WHERE name = 'dropdown' LIMIT 1),
    'lawyers', 'full_name', 'id', 6
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 36 AND field_name = 'lead_rep_id'
);

-- Add description textarea
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 36, 'description', 'description', 'description',
    (SELECT id FROM element_types WHERE name = 'textarea' LIMIT 1), 7
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 36 AND field_name = 'description'
);

-- Add initial instructions textarea
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 36, 'initial_instructions', 'initial_instructions', 'initial_instructions',
    (SELECT id FROM element_types WHERE name = 'textarea' LIMIT 1), 8
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 36 AND field_name = 'initial_instructions'
);
