-- Migration: Add One Time Costs functionality
-- Date: 20250905

-- Update case modal config to add "Add One Time Cost" button
UPDATE modules
SET config = (REPLACE(config::text, '{"type":"button","label":"Add Expense","formName":"Case Expense","prefillFilter":"case_id"},', '{"type":"button","label":"Add Expense","formName":"Case Expense","prefillFilter":"case_id"},{"type":"button","label":"Add One Time Cost","formName":"One Time Cost","prefillFilter":"case_id"},') )::jsonb
WHERE name = 'Cases';

-- Add One Time Costs tab to case modal
UPDATE modules
SET config = (REPLACE(config::text, '{"label":"Expenses","elements":[{"type":"button","label":"Add Expense","formName":"Case Expense","prefillFilter":"case_id"},{"type":"table","options":{"tableName":"expenses","inlineEdit":true,"ownerEditOnly":true,"showGlobalSearch":false,"addButton":false,"editFormId":48,"columnsToShow":["cost_date","amount","description","billable"],"filterField":"reference_id","multipleSelect":true}}]}', '{"label":"Expenses","elements":[{"type":"button","label":"Add Expense","formName":"Case Expense","prefillFilter":"case_id"},{"type":"table","options":{"tableName":"expenses","inlineEdit":true,"ownerEditOnly":true,"showGlobalSearch":false,"addButton":false,"editFormId":48,"columnsToShow":["cost_date","amount","description","billable"],"filterField":"reference_id","multipleSelect":true}}]},{"label":"One Time Costs","elements":[{"type":"button","label":"Add One Time Cost","formName":"One Time Cost","prefillFilter":"case_id"},{"type":"table","options":{"tableName":"one_time_service_costs","inlineEdit":true,"ownerEditOnly":true,"showGlobalSearch":false,"addButton":false,"editFormId":58,"columnsToShow":["cost_date","amount","description","billable"],"filterField":"reference_id","multipleSelect":true}}]}') )::jsonb
WHERE name = 'Cases';

-- Add One Time Costs tab to Time Entries & Expenses module
UPDATE modules
SET config = (REPLACE(config::text, '{"label":"Expenses","elements":[{"type":"table","options":{"tableName":"expenses","inlineEdit":true,"filterField":["user_id","status","logged_date"],"columnsToShow":["council","amount","description","billable","approved","approval_comment"]}}]}', '{"label":"Expenses","elements":[{"type":"table","options":{"tableName":"expenses","inlineEdit":true,"filterField":["user_id","status","logged_date"],"columnsToShow":["council","amount","description","billable","approved","approval_comment"]}}]},{"label":"One Time Costs","elements":[{"type":"table","options":{"tableName":"one_time_service_costs","inlineEdit":true,"filterField":["user_id","status","cost_date"],"columnsToShow":["council","amount","description","billable","approved","comments"]}}]}') )::jsonb
WHERE name = 'Time Entries & Expenses';

-- Insert One Time Cost form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 58, 'One Time Cost', 'One time service cost for a case', 'one_time_service_costs',
'[{"label":"Case","field_name":"case_id","data_column":"reference_id","type_id":4,"options_table":"cases","options_label_column":"title","options_value_column":"case_id","order_index":1},
{"label":"Cost Date","field_name":"cost_date","default":"now()","data_column":"cost_date","type_id":3,"order_index":2},
{"label":"Vendor","field_name":"vendor","data_column":"vendor_id","type_id":13,"options_table":"vendors","options_label_column":"vendorName","options_value_column":"vendor_id","order_index":3},
{"label":"Invoice Number","field_name":"invoice_number","data_column":"invoice_number","type_id":1,"order_index":4},
{"label":"Amount","field_name":"amount","data_column":"amount","type_id":2,"order_index":6},
{"label":"Billable","field_name":"billable","data_column":"billable","type_id":5,"order_index":7},
{"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":10},
{"label":"Documents","field_name":"document_ids","data_column":"document_ids","type_id":9,"order_index":14},
{"field_name":"reference_table","data_column":"reference_table","type_id":17,"default_value":"cases","order_index":15}]',
NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 58);
