-- Add Case Details detail view and update Case Management modal

-- Insert detail view
INSERT INTO detail_views (id, name, config, tenant_id, branch_id)
SELECT 2, 'Case Details', '{"name": "Case Details", "items": [{"type": "html", "id": "caseOverview", "html": "<div class=''''card bg-base-100 shadow mb-4''''><div class=''''card-body space-y-1''''><h2 class=''''card-title''''>Case Overview</h2><p><span id=''''dvCaseNumber''''></span> - <span id=''''dvTitle''''></span></p><p>Status: <span id=''''dvStatus''''></span></p><p>Lead Rep: <span id=''''dvLeadRep''''></span></p><p>Opened: <span id=''''dvOpenedAt''''></span></p><p>Closed: <span id=''''dvClosedAt''''></span></p><div class=''''card-actions''''><button id=''''dvEditCase'''' class=''''btn btn-sm''''>Edit Case</button><button id=''''dvAddEntry'''' class=''''btn btn-sm''''>Add Entry</button><button id=''''dvCloseCase'''' class=''''btn btn-sm''''>Close Case</button></div></div></div>"}, {"type": "html", "id": "clientInfo", "html": "<div class=''''card bg-base-100 shadow mb-4''''><div class=''''card-body space-y-1''''><h2 class=''''card-title''''>Client Information</h2><p><a id=''''dvClientName'''' class=''''link'''' href=''''#''''></a></p><p>Phone: <span id=''''dvClientPhone''''></span></p><p>Email: <span id=''''dvClientEmail''''></span></p><p><span id=''''dvClientAddress''''></span></p><p><span id=''''dvClientCity''''></span>, <span id=''''dvClientCountry''''></span></p><p><span id=''''dvClientNotes''''></span></p><div id=''''dvClientDocs''''></div></div></div>"}, {"type": "heading", "data_column": "title"}, {"type": "paragraph", "data_column": "client_id"}, {"type": "paragraph", "data_column": "status_id"}, {"type": "paragraph", "data_column": "opened_at"}, {"type": "paragraph", "data_column": "closed_at"}, {"type": "paragraph", "data_column": "lead_rep_id"}, {"type": "paragraph", "data_column": "description"}, {"type": "paragraph", "data_column": "initial_instructions"}, {"type": "aggregate", "table": "case_hours", "column": "hours", "function": "sum", "filterField": "case_id"}, {"type": "aggregate", "table": "case_hours", "column": "hours*rate", "function": "sum", "filterField": "case_id"}, {"type": "aggregate", "table": "expenses", "column": "amount", "function": "sum", "filterField": "reference_id"}]}', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM detail_views WHERE id = 2);

-- Update Case Management modal to use new view
UPDATE ui_modals
SET config = '{"title":"Case Management","header":[{"type":"filter-select","options":{"source_table":"cases","source_column":"id","display_column":"title","default_label":"Select Case","data_column":"case_id","scope":"admin_user","userField":"lead_rep_id"}}],"body":[{"type":"tabs","tabs":[{"label":"Details","elements":[{"type":"detail-view","viewId":2,"filterField":"case_id","tableName":"cases"}]},{"label":"Instructions","elements":[{"type":"button","label":"Add Instruction","formName":"Case Instruction","prefillFilter":"case_id"},{"type":"table","options":{"tableName":"case_instructions","showGlobalSearch":false,"addButton":false,"filterField":"case_id"}}]},{"label":"Documents","elements":[{"type":"documents","options":{"filterField":"case_id"}}]},{"label":"Hours","elements":[{"type":"button","label":"Add Hour","formName":"Case Hour","prefillFilter":"case_id"},{"type":"table","options":{"tableName":"case_hours","tableUrl":"../api/case_hours","showGlobalSearch":false,"addButton":false,"editFormId":38,"filterField":"case_id"}}]},{"label":"Expenses","elements":[{"type":"button","label":"Add Expense","formName":"Case Expense","prefillFilter":"case_id"},{"type":"table","options":{"tableName":"expenses","showGlobalSearch":false,"addButton":false,"editFormId":48,"filterField":"case_id"}}]},{"label":"Invoices","elements":[{"type":"button","label":"Create Invoice","onClick":"openCaseInvoiceForm"},{"type":"table","options":{"tableName":"invoices","showGlobalSearch":false,"addButton":false,"filterField":"case_id"}}]},{"label":"Payments","elements":[{"type":"button","label":"Record Payment","formName":"Case Payment"},{"type":"table","options":{"tableName":"payments","showGlobalSearch":false,"addButton":false,"filterField":"case_id"}}]}]}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}'
WHERE name = 'Case Management';

INSERT INTO ui_modals (name, config, tenant_id, branch_id, icon, aria_label, description)
SELECT 'Case Management', '{"title":"Case Management","header":[{"type":"filter-select","options":{"source_table":"cases","source_column":"id","display_column":"title","default_label":"Select Case","data_column":"case_id","scope":"admin_user","userField":"lead_rep_id"}}],"body":[{"type":"tabs","tabs":[{"label":"Details","elements":[{"type":"detail-view","viewId":2,"filterField":"case_id","tableName":"cases"}]},{"label":"Instructions","elements":[{"type":"button","label":"Add Instruction","formName":"Case Instruction","prefillFilter":"case_id"},{"type":"table","options":{"tableName":"case_instructions","showGlobalSearch":false,"addButton":false,"filterField":"case_id"}}]},{"label":"Documents","elements":[{"type":"documents","options":{"filterField":"case_id"}}]},{"label":"Hours","elements":[{"type":"button","label":"Add Hour","formName":"Case Hour","prefillFilter":"case_id"},{"type":"table","options":{"tableName":"case_hours","tableUrl":"../api/case_hours","showGlobalSearch":false,"addButton":false,"editFormId":38,"filterField":"case_id"}}]},{"label":"Expenses","elements":[{"type":"button","label":"Add Expense","formName":"Case Expense","prefillFilter":"case_id"},{"type":"table","options":{"tableName":"expenses","showGlobalSearch":false,"addButton":false,"editFormId":48,"filterField":"case_id"}}]},{"label":"Invoices","elements":[{"type":"button","label":"Create Invoice","onClick":"openCaseInvoiceForm"},{"type":"table","options":{"tableName":"invoices","showGlobalSearch":false,"addButton":false,"filterField":"case_id"}}]},{"label":"Payments","elements":[{"type":"button","label":"Record Payment","formName":"Case Payment"},{"type":"table","options":{"tableName":"payments","showGlobalSearch":false,"addButton":false,"filterField":"case_id"}}]}]}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-briefcase', 'Case Management', 'Case Management'
WHERE NOT EXISTS (SELECT 1 FROM ui_modals WHERE name = 'Case Management');
