-- Add document_ids columns and update form definitions

ALTER TABLE case_instructions
    ADD COLUMN IF NOT EXISTS document_ids TEXT;

ALTER TABLE expenses
    DROP COLUMN IF EXISTS document_id,
    ADD COLUMN IF NOT EXISTS document_ids TEXT;
DROP INDEX IF EXISTS expenses_document_idx;

ALTER TABLE payments
    ADD COLUMN IF NOT EXISTS document_ids TEXT;

-- Case Instruction form (id 47)
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 47, 'documents', 'document_ids', 'document_ids',
       (SELECT id FROM element_types WHERE name = 'file' LIMIT 1), 5
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 47 AND field_name = 'document_ids'
);

-- Case Expense form (id 48)
DELETE FROM form_elements WHERE form_id = 48 AND field_name = 'document_id';
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 48, 'documents', 'document_ids', 'document_ids',
       (SELECT id FROM element_types WHERE name = 'file' LIMIT 1), 6
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 48 AND field_name = 'document_ids'
);

-- Case Payment form (id 50)
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 50, 'documents', 'document_ids', 'document_ids',
       (SELECT id FROM element_types WHERE name = 'file' LIMIT 1), 5
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 50 AND field_name = 'document_ids'
);
