-- Add case_number field to cases and form configuration

ALTER TABLE cases
    ADD COLUMN IF NOT EXISTS case_number varchar(100);

-- Insert case_number form element before title (order_index 2)
UPDATE form_elements
SET order_index = order_index + 1
WHERE form_id = 36 AND order_index >= 2;

INSERT INTO form_elements (
    form_id, label, field_name, data_column, type_id, order_index
)
SELECT 36, 'case_number', 'case_number', 'case_number',
    (SELECT id FROM element_types WHERE name='text' LIMIT 1), 2
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 36 AND field_name = 'case_number'
);

-- Include case_number in default table view if configured
UPDATE table_views
SET columns = '["case_number","client_id","title","status","opened_at","closed_at"]'
WHERE form_id = 36 AND name = 'Default' AND columns NOT LIKE '%case_number%';
