-- Migration: Enhanced Finance Forms for Double-Entry System
-- Date: 2025-11-11
-- Description: Update finance forms to support double-entry bookkeeping features

-- Update Journal Entry form with double-entry line items
UPDATE forms 
SET elements = '[
  {
    "label": "Entry Date", 
    "type_id": 3, 
    "required": true, 
    "field_name": "entry_date", 
    "data_column": "entry_date", 
    "order_index": 1,
    "default_value": "today"
  },
  {
    "label": "Reference", 
    "type_id": 1, 
    "required": false, 
    "field_name": "reference", 
    "data_column": "reference", 
    "order_index": 2,
    "placeholder": "Optional reference number"
  },
  {
    "label": "Description", 
    "type_id": 7, 
    "required": true, 
    "field_name": "description", 
    "data_column": "description", 
    "order_index": 3,
    "placeholder": "Journal entry description"
  },
  {
    "label": "Source Module", 
    "type_id": 1, 
    "required": false, 
    "field_name": "source_module", 
    "data_column": "source_module", 
    "order_index": 4,
    "default_value": "finance"
  },
  {
    "label": "Journal Lines", 
    "type_id": 20, 
    "required": true, 
    "field_name": "journal_lines", 
    "data_column": "journal_lines", 
    "order_index": 5,
    "options": {
      "component": "JournalLinesEditor",
      "min_lines": 2,
      "auto_balance": true,
      "accounts_source": "/api/finance/accounts",
      "validation": {
        "debit_credit_balance": true,
        "min_amount": 0.01
      }
    }
  }
]'::jsonb
WHERE id = 202;

-- Update Chart of Accounts form
UPDATE forms 
SET elements = '[
  {
    "label": "Account Code", 
    "type_id": 1, 
    "required": true, 
    "field_name": "code", 
    "data_column": "code", 
    "order_index": 1,
    "validation": {
      "pattern": "^[0-9]{4,6}$",
      "message": "Account code must be 4-6 digits"
    }
  },
  {
    "label": "Account Name", 
    "type_id": 1, 
    "required": true, 
    "field_name": "name", 
    "data_column": "name", 
    "order_index": 2
  },
  {
    "label": "Account Type", 
    "type_id": 4, 
    "required": true, 
    "field_name": "account_type", 
    "data_column": "account_type", 
    "order_index": 3,
    "options": [
      {"value": "asset", "label": "Asset"},
      {"value": "liability", "label": "Liability"},
      {"value": "equity", "label": "Equity"},
      {"value": "revenue", "label": "Revenue"},
      {"value": "expense", "label": "Expense"}
    ]
  },
  {
    "label": "Parent Account", 
    "type_id": 4, 
    "required": false, 
    "field_name": "parent_id", 
    "data_column": "parent_id", 
    "order_index": 4,
    "options": {
      "source": "/api/finance/accounts/hierarchy",
      "value_field": "id",
      "label_field": "name_with_code",
      "allow_empty": true
    }
  },
  {
    "label": "Description", 
    "type_id": 7, 
    "required": false, 
    "field_name": "description", 
    "data_column": "description", 
    "order_index": 5
  },
  {
    "label": "Active", 
    "type_id": 5, 
    "required": false, 
    "field_name": "is_active", 
    "data_column": "is_active", 
    "order_index": 6,
    "default_value": true
  }
]'::jsonb
WHERE id = 201;

-- Update AP Bill form
UPDATE forms 
SET elements = '[
  {
    "label": "Vendor", 
    "type_id": 4, 
    "required": true, 
    "field_name": "vendor_id", 
    "data_column": "vendor_id", 
    "order_index": 1,
    "options": {
      "source": "/api/vendors",
      "value_field": "id",
      "label_field": "name",
      "searchable": true
    }
  },
  {
    "label": "Invoice Number", 
    "type_id": 1, 
    "required": true, 
    "field_name": "invoice_number", 
    "data_column": "invoice_number", 
    "order_index": 2
  },
  {
    "label": "Invoice Date", 
    "type_id": 3, 
    "required": true, 
    "field_name": "invoice_date", 
    "data_column": "invoice_date", 
    "order_index": 3
  },
  {
    "label": "Due Date", 
    "type_id": 3, 
    "required": true, 
    "field_name": "due_date", 
    "data_column": "due_date", 
    "order_index": 4
  },
  {
    "label": "Amount", 
    "type_id": 10, 
    "required": true, 
    "field_name": "amount", 
    "data_column": "amount", 
    "order_index": 5,
    "validation": {
      "min": 0.01
    }
  },
  {
    "label": "Expense Account", 
    "type_id": 4, 
    "required": true, 
    "field_name": "expense_account_id", 
    "data_column": "expense_account_id", 
    "order_index": 6,
    "options": {
      "source": "/api/finance/accounts?type=expense",
      "value_field": "id",
      "label_field": "name_with_code"
    }
  },
  {
    "label": "Description", 
    "type_id": 7, 
    "required": false, 
    "field_name": "description", 
    "data_column": "description", 
    "order_index": 7
  },
  {
    "label": "Auto-post to GL", 
    "type_id": 5, 
    "required": false, 
    "field_name": "auto_post_gl", 
    "data_column": "auto_post_gl", 
    "order_index": 8,
    "default_value": true,
    "help_text": "Automatically create GL entry when bill is saved"
  }
]'::jsonb
WHERE id = 204;

-- Update AP Payment form
UPDATE forms 
SET elements = '[
  {
    "label": "Bill to Pay", 
    "type_id": 4, 
    "required": true, 
    "field_name": "bill_id", 
    "data_column": "bill_id", 
    "order_index": 1,
    "options": {
      "source": "/api/ap-transactions?status=pending",
      "value_field": "id",
      "label_field": "bill_display",
      "searchable": true
    }
  },
  {
    "label": "Payment Date", 
    "type_id": 3, 
    "required": true, 
    "field_name": "payment_date", 
    "data_column": "payment_date", 
    "order_index": 2,
    "default_value": "today"
  },
  {
    "label": "Payment Amount", 
    "type_id": 10, 
    "required": true, 
    "field_name": "payment_amount", 
    "data_column": "payment_amount", 
    "order_index": 3,
    "validation": {
      "min": 0.01
    }
  },
  {
    "label": "Payment Method", 
    "type_id": 4, 
    "required": true, 
    "field_name": "payment_method", 
    "data_column": "payment_method", 
    "order_index": 4,
    "options": [
      {"value": "check", "label": "Check"},
      {"value": "cash", "label": "Cash"},
      {"value": "bank_transfer", "label": "Bank Transfer"},
      {"value": "credit_card", "label": "Credit Card"}
    ]
  },
  {
    "label": "Check Number", 
    "type_id": 1, 
    "required": false, 
    "field_name": "check_number", 
    "data_column": "check_number", 
    "order_index": 5,
    "conditions": {
      "show_if": {"payment_method": "check"}
    }
  },
  {
    "label": "Payment Account", 
    "type_id": 4, 
    "required": true, 
    "field_name": "payment_account_id", 
    "data_column": "payment_account_id", 
    "order_index": 6,
    "options": {
      "source": "/api/finance/accounts?type=asset&subtype=cash",
      "value_field": "id",
      "label_field": "name_with_code"
    }
  },
  {
    "label": "Notes", 
    "type_id": 7, 
    "required": false, 
    "field_name": "notes", 
    "data_column": "notes", 
    "order_index": 7
  }
]'::jsonb
WHERE id = 205;

-- Update AR Invoice form
UPDATE forms 
SET elements = '[
  {
    "label": "Customer", 
    "type_id": 4, 
    "required": true, 
    "field_name": "customer_id", 
    "data_column": "customer_id", 
    "order_index": 1,
    "options": {
      "source": "/api/customers",
      "value_field": "id",
      "label_field": "name",
      "searchable": true
    }
  },
  {
    "label": "Invoice Number", 
    "type_id": 1, 
    "required": false, 
    "field_name": "invoice_number", 
    "data_column": "invoice_number", 
    "order_index": 2,
    "help_text": "Auto-generated if left blank"
  },
  {
    "label": "Invoice Date", 
    "type_id": 3, 
    "required": true, 
    "field_name": "invoice_date", 
    "data_column": "invoice_date", 
    "order_index": 3,
    "default_value": "today"
  },
  {
    "label": "Due Date", 
    "type_id": 3, 
    "required": true, 
    "field_name": "due_date", 
    "data_column": "due_date", 
    "order_index": 4
  },
  {
    "label": "Amount", 
    "type_id": 10, 
    "required": true, 
    "field_name": "amount", 
    "data_column": "amount", 
    "order_index": 5,
    "validation": {
      "min": 0.01
    }
  },
  {
    "label": "Revenue Account", 
    "type_id": 4, 
    "required": true, 
    "field_name": "revenue_account_id", 
    "data_column": "revenue_account_id", 
    "order_index": 6,
    "options": {
      "source": "/api/finance/accounts?type=revenue",
      "value_field": "id",
      "label_field": "name_with_code"
    }
  },
  {
    "label": "Description", 
    "type_id": 7, 
    "required": false, 
    "field_name": "description", 
    "data_column": "description", 
    "order_index": 7
  },
  {
    "label": "Terms", 
    "type_id": 4, 
    "required": false, 
    "field_name": "terms", 
    "data_column": "terms", 
    "order_index": 8,
    "options": [
      {"value": "net_15", "label": "Net 15"},
      {"value": "net_30", "label": "Net 30"},
      {"value": "net_60", "label": "Net 60"},
      {"value": "due_on_receipt", "label": "Due on Receipt"}
    ],
    "default_value": "net_30"
  }
]'::jsonb
WHERE id = 206;

-- Update AR Payment form
UPDATE forms 
SET elements = '[
  {
    "label": "Invoice to Pay", 
    "type_id": 4, 
    "required": true, 
    "field_name": "invoice_id", 
    "data_column": "invoice_id", 
    "order_index": 1,
    "options": {
      "source": "/api/ar-transactions?status=pending",
      "value_field": "id",
      "label_field": "invoice_display",
      "searchable": true
    }
  },
  {
    "label": "Payment Date", 
    "type_id": 3, 
    "required": true, 
    "field_name": "payment_date", 
    "data_column": "payment_date", 
    "order_index": 2,
    "default_value": "today"
  },
  {
    "label": "Payment Amount", 
    "type_id": 10, 
    "required": true, 
    "field_name": "payment_amount", 
    "data_column": "payment_amount", 
    "order_index": 3,
    "validation": {
      "min": 0.01
    }
  },
  {
    "label": "Payment Method", 
    "type_id": 4, 
    "required": true, 
    "field_name": "payment_method", 
    "data_column": "payment_method", 
    "order_index": 4,
    "options": [
      {"value": "cash", "label": "Cash"},
      {"value": "check", "label": "Check"},
      {"value": "credit_card", "label": "Credit Card"},
      {"value": "bank_transfer", "label": "Bank Transfer"}
    ]
  },
  {
    "label": "Deposit Account", 
    "type_id": 4, 
    "required": true, 
    "field_name": "deposit_account_id", 
    "data_column": "deposit_account_id", 
    "order_index": 5,
    "options": {
      "source": "/api/finance/accounts?type=asset&subtype=cash",
      "value_field": "id",
      "label_field": "name_with_code"
    }
  },
  {
    "label": "Reference Number", 
    "type_id": 1, 
    "required": false, 
    "field_name": "reference_number", 
    "data_column": "reference_number", 
    "order_index": 6
  },
  {
    "label": "Notes", 
    "type_id": 7, 
    "required": false, 
    "field_name": "notes", 
    "data_column": "notes", 
    "order_index": 7
  }
]'::jsonb
WHERE id = 207;

-- Insert the missing Journal Entry module
INSERT INTO modules (name, config, icon, description) 
VALUES (
  'Journal Entry',
  '{
    "title": "Journal Entry",
    "body": [
      {
        "type": "tabs",
        "tabs": [
          {
            "label": "Create Entry",
            "elements": [
              {
                "type": "form",
                "options": {
                  "formId": 202,
                  "tableName": "GL_Journal_Entries",
                  "submitEndpoint": "/api/gl-transactions/post-entry",
                  "customComponents": {
                    "journal_lines": "JournalLinesEditor"
                  },
                  "onSuccess": "refreshJournalEntries"
                }
              }
            ]
          },
          {
            "label": "Recent Entries",
            "elements": [
              {
                "type": "table",
                "options": {
                  "tableName": "GL_Journal_Entries",
                  "orderBy": "entry_date DESC, created_at DESC",
                  "limit": 50,
                  "columnsToShow": ["entry_date", "reference", "description", "total_debits", "status"],
                  "rowActions": [
                    {
                      "label": "View Details",
                      "onClick": "viewJournalEntry"
                    },
                    {
                      "label": "Reverse Entry",
                      "onClick": "reverseJournalEntry",
                      "condition": "status == \"posted\"",
                      "confirm": "Are you sure you want to reverse this journal entry?"
                    }
                  ],
                  "filters": {
                    "status": ["draft", "posted", "reversed"]
                  }
                }
              }
            ]
          },
          {
            "label": "Trial Balance",
            "elements": [
              {
                "type": "button",
                "label": "Generate Trial Balance",
                "style": "primary",
                "onClick": "generateTrialBalance"
              },
              {
                "type": "date-picker",
                "id": "trialBalanceDate",
                "label": "As of Date",
                "value": "today"
              },
              {
                "type": "report-viewer",
                "id": "trialBalanceReport",
                "options": {
                  "endpoint": "/api/gl-transactions/trial-balance",
                  "reportType": "trial_balance",
                  "exportFormats": ["pdf", "csv", "excel"]
                }
              }
            ]
          }
        ]
      }
    ],
    "header": [
      {
        "type": "button",
        "label": "New Journal Entry",
        "formId": 202
      },
      {
        "type": "button",
        "label": "Trial Balance",
        "onClick": "showTrialBalance"
      }
    ],
    "footer": [
      {
        "type": "button",
        "label": "Close",
        "onClick": "closeModal"
      }
    ]
  }'::jsonb,
  'fa-book',
  'Create and manage general ledger journal entries'
)
ON CONFLICT DO NOTHING;