-- Migration: Update Case Management module to show payments from invoices
-- Date: 2025-12-17
-- Description: Updates the Payments tab configuration to use case_payment_records view
DELETE FROM modules
WHERE name = 'Case Management';
INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description)
SELECT '0194a8b2-8888-7123-8123-123456789ac7'::uuid,
       'Case Management',
       $json$
{
  "title": "Case Management",
  "header": [
    {
      "type": "filter-select",
      "options": {
        "source_table": "clients",
        "source_column": "client_id",
        "display_column": "name",
        "data_column": "client_name",
        "data_column_id": "selected_client_id",
        "placeholder": "Search by client name...",
        "search_fields": ["name"],
        "max_results": 25,
        "debounce_ms": 250,
        "populateMode": "immediate"
      }
    },
    { "type": "button", "label": "Add Hour", "formName": "Time Entries", "prefillFilter": "case_id", "source_column": "case_id", "prefillRequired": true, "prefillError": "Please select client and case", "condition": "status !== 'closed'", "conditionError": "Cannot add hours to a closed case", "options": { "beforeRender": "validateCaseHasTasks", "onRender": "setupCaseTasksValidation" } },
    { "type": "button", "label": "Add Expense", "formName": "Case Expense", "prefillFilter": "case_id", "prefillRequired": true, "prefillError": "Please select client and case", "condition": "status !== 'closed'", "conditionError": "Cannot add expenses to a closed case" },
    { "type": "button", "label": "Add One Time Cost", "formName": "One Time Cost", "prefillFilter": "case_id", "prefillRequired": true, "prefillError": "Please select client and case", "condition": "status !== 'closed'", "conditionError": "Cannot add costs to a closed case" },
    { "type": "button", "label": "Create Cost Schedule", "onClick": "createCostSchedule", "condition": "status !== 'closed'", "conditionError": "Cannot create schedule for a closed case" },
    { "type": "button", "label": "Create Invoice", "onClick": "createCostSchedule", "condition": "status !== 'closed'", "conditionError": "Cannot create invoice for a closed case" },
    { "type": "button", "label": "Back", "onClick": "backToCases" }
  ],
  "body": [
    {
      "type": "tabs",
      "tabs": [
        {
          "label": "Cases",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "cases",
                "showGlobalSearch": false,
                "addButton": false,
                "columnsToShow": ["client_name", "case_number", "title", "lead_rep_name", "status", "added_by"],
                "filterField": "client_name",
                "listenColumn": "client_name",
                "primaryKeyField": "case_id",
                "rowActions": [
                  { "label": "Select", "onClick": "selectCaseForManagement" }
                ]
              }
            }
          ]
        },
        {
          "label": "Details",
          "elements": [
            { "type": "detail-view", "viewId": 2, "filterField": "case_id", "tableName": "cases" }
          ]
        },
        {
          "label": "Instructions",
          "elements": [
            { "type": "table", "options": { "tableName": "case_instructions", "inlineEdit": true, "showGlobalSearch": false, "addButton": false, "filterField": "case_id" } }
          ]
        },
        {
          "label": "Documents",
          "elements": [
            { 
              "type": "documents", 
              "options": { 
                "filterField": "case_id",
                "listenColumn": "case_id",
                "metadataFields": {
                  "case_number": "case_number",
                  "case_title": "title",
                  "client_id": "client_id",
                  "client_name": "client_name",
                  "matter_type": "matter_type",
                  "status": "status"
                }
              } 
            }
          ]
        },
        {
          "label": "Hours",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "time_entries",
                "inlineEdit": true,
                "ownerEditOnly": true,
                "tableUrl": "../api/time_entries",
                "showGlobalSearch": false,
                "addButton": false,
                "editFormId": 38,
                "columnsToShow": ["Counsel", "date", "hours", "total", "work_type", "description"],
                "filterField": "reference_id",
                "listenColumn": "case_id",
                "multipleSelect": true
              }
            }
          ]
        },
        {
          "label": "Expenses",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "expenses",
                "inlineEdit": true,
                "ownerEditOnly": true,
                "showGlobalSearch": false,
                "addButton": false,
                "editFormId": 48,
                "columnsToShow": ["date", "counsel", "amount", "description", "billable"],
                "filterField": "reference_id",
                "listenColumn": "case_id",
                "multipleSelect": true
              }
            }
          ]
        },
        {
          "label": "One Time Costs",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "one_time_service_costs",
                "inlineEdit": true,
                "ownerEditOnly": true,
                "showGlobalSearch": false,
                "addButton": false,
                "editFormId": 58,
                "columnsToShow": ["date", "counsel", "amount", "description", "billable"],
                "filterField": "reference_id",
                "listenColumn": "case_id",
                "multipleSelect": true
              }
            }
          ]
        },
        {
          "label": "Invoices",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "invoices",
                "inlineEdit": true,
                "showGlobalSearch": false,
                "addButton": false,
                "filterField": "reference_id",
                "listenColumn": "case_id",
                "filters": { "reference_table": "cases" },
                "columnsToShow": ["invoice_date", "sent_at", "invoice_number", "amount", "amount_paid", "balance"],
                "rowActions": [
                  { "label": "Edit", "onClick": "openCaseInvoiceEditForm" },
                  { "label": "Record Payment", "onClick": "recordInvoicePayment" },
                  { "label": "Submit to TaxCore", "onClick": "submitInvoiceToTaxCore", "icon": "fa-paper-plane", "condition": "status == 'approved'" },
                  { "label": "Download", "onClick": "downloadCaseInvoice" },
                  { "label": "Preview", "onClick": "previewCaseInvoice" },
                  { "label": "Create Summary", "onClick": "createInvoiceSummary" }
                ]
              }
            }
          ]
        },
        {
          "label": "Invoice Summaries",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "case_invoice_summaries",
                "inlineEdit": true,
                "showGlobalSearch": false,
                "addButton": false,
                "editFormId": 201,
                "columnsToShow": ["summary_type", "description", "invoice_number", "invoice_amount", "invoice_amount_paid", "invoice_balance"],
                "rowActions": [
                  { "label": "Edit", "onClick": "openEditForm" },
                  { "label": "Download", "onClick": "downloadSummaryPDF" },
                  { "label": "Preview", "onClick": "previewSummaryPDF" },
                  { "label": "Mark Active", "onClick": "markSummaryActive" }
                ]
              }
            }
          ]
        },
        {
          "label": "Payments",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "case_payment_records",
                "inlineEdit": false,
                "showGlobalSearch": false,
                "addButton": false,
                "filterField": "case_id",
                "listenColumn": "case_id",
                "columnsToShow": ["payment_date", "invoice_number", "total_amount", "payment_method", "status", "notes"]
              }
            }
          ]
        }
      ]
    }
  ],
  "footer": [
    { "type": "button", "label": "Close", "onClick": "closeModal" }
  ]
}
$json$::jsonb,
       NULL, NULL,
       'fa-briefcase',
       'Case Management', 'Case Management'
WHERE NOT EXISTS (SELECT 1 FROM modules WHERE name = 'Case Management');

DELETE FROM modules WHERE name = 'Cases';
INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description)
SELECT '0194a8b2-bbbb-7123-8123-123456789aca'::uuid, 'Cases', 
$json$
{
  "title": "Cases",
  "header": [
    {
      "type": "filter-select",
      "options": {
        "source_table": "cases",
        "source_column": "case_id",
        "display_column": "title",
        "data_column": "client_name",
        "data_column_id": "selected_case_id",
        "placeholder": "Search cases by client, number, or title...",
        "search_fields": ["name"],
        "max_results": 25,
        "debounce_ms": 250,
        "populateMode": "immediate"
      }
    },
    { "type": "button", "label": "Add Case", "formName": "Case" },
    { "type": "button", "label": "Detail View", "onClick": "openSelectedDetail" }
  ],
  "body": [
    {
      "type": "table",
      "options": {
        "tableName": "cases",
        "showGlobalSearch": false,
        "addButton": false,
        "rowClick": "selectRecord",
        "columnsToShow": ["client_name", "case_number", "title", "lead_rep_name", "added_by"],
        "filterField": "client_name",
        "listenColumn": "client_name"
      }
    }
  ],
  "footer": [
    { "type": "button", "label": "Close", "onClick": "closeModal" }
  ]
}
$json$::jsonb,
NULL, NULL, 'fa-folder-open', 'Cases', 'Cases'
WHERE NOT EXISTS (SELECT 1 FROM modules WHERE name = 'Cases');