-- Rename file_path column and update JSON references for Safety Incidents

-- PostgreSQL
ALTER TABLE safety_incidents RENAME COLUMN file_path TO document_ids;

UPDATE forms
SET elements = REPLACE(
        REPLACE(elements::text,'"field_name":"file_path"','"field_name":"document_ids"'),
        '"data_column":"file_path"','"data_column":"document_ids"'
    )::jsonb
WHERE id = 97;

UPDATE table_views
SET columns = REPLACE(columns::text, 'file_path','document_ids')::jsonb
WHERE form_id = 97;

UPDATE ui_modals
SET config = jsonb_set(
    config,
    '{body,0,options,columnLabels}',
    '{"document_ids":"Documents"}'::jsonb,
    true
)
WHERE name = 'Safety Incidents';

-- MySQL
ALTER TABLE safety_incidents RENAME COLUMN file_path TO document_ids;

UPDATE forms
SET elements = REPLACE(
        REPLACE(elements, '"field_name":"file_path"', '"field_name":"document_ids"'),
        '"data_column":"file_path"', '"data_column":"document_ids"'
    )
WHERE id = 97;

UPDATE table_views
SET columns = REPLACE(columns, 'file_path','document_ids')
WHERE form_id = 97;

UPDATE ui_modals
SET config = JSON_SET(config, '$.body[0].options.columnLabels', JSON_OBJECT('document_ids','Documents'))
WHERE name = 'Safety Incidents';

-- Ensure modal exists with updated configuration
INSERT INTO ui_modals (name, config, tenant_id, branch_id, icon, aria_label, description)
SELECT 'Safety Incidents', '{"title":"Safety Incidents","header":[{"type":"button","label":"Add Incident","formName":"Safety Incident"}],"body":[{"type":"table","options":{"tableName":"safety_incidents","showGlobalSearch":false,"addButton":false,"editFormId":97,"columnLabels":{"document_ids":"Documents"}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-helmet-safety', 'Safety Incidents', 'Safety Incidents'
WHERE NOT EXISTS (SELECT 1 FROM ui_modals WHERE name = 'Safety Incidents');
