-- =====================================================
-- TaxCore Integration for Case Management Module
-- =====================================================
-- This migration adds TaxCore functionality to the Case Management module
-- by adding action buttons and configuration

-- Add TaxCore Settings action to Case Management module
INSERT INTO module_actions (
  module_id,
  action_name,
  action_label,
  action_icon,
  action_function,
  action_type,
  display_order,
  is_active
) VALUES (
  (SELECT module_id FROM modules WHERE name = 'Case Management' LIMIT 1),
  'taxcore_settings',
  'TaxCore Settings',
  'fa-cog',
  'window.openTaxCoreSettings()',
  'toolbar',
  100,
  true
) ON CONFLICT (module_id, action_name) DO UPDATE SET
  action_label = EXCLUDED.action_label,
  action_icon = EXCLUDED.action_icon,
  action_function = EXCLUDED.action_function,
  is_active = EXCLUDED.is_active;

-- Add View Transactions action to Case Management module
INSERT INTO module_actions (
  module_id,
  action_name,
  action_label,
  action_icon,
  action_function,
  action_type,
  display_order,
  is_active
) VALUES (
  (SELECT module_id FROM modules WHERE name = 'Case Management' LIMIT 1),
  'view_transactions',
  'View Transactions',
  'fa-receipt',
  'window.showCaseTransactions()',
  'toolbar',
  101,
  true
) ON CONFLICT (module_id, action_name) DO UPDATE SET
  action_label = EXCLUDED.action_label,
  action_icon = EXCLUDED.action_icon,
  action_function = EXCLUDED.action_function,
  is_active = EXCLUDED.is_active;

-- Add Submit to TaxCore action for invoices (row action)
INSERT INTO module_actions (
  module_id,
  action_name,
  action_label,
  action_icon,
  action_function,
  action_type,
  action_condition,
  display_order,
  is_active
) VALUES (
  (SELECT module_id FROM modules WHERE name = 'Case Management' LIMIT 1),
  'submit_invoice_taxcore',
  'Submit to TaxCore',
  'fa-paper-plane',
  'window.submitInvoiceToTaxCore(record)',
  'row',
  'status == "approved"',
  50,
  true
) ON CONFLICT (module_id, action_name) DO UPDATE SET
  action_label = EXCLUDED.action_label,
  action_icon = EXCLUDED.action_icon,
  action_function = EXCLUDED.action_function,
  action_condition = EXCLUDED.action_condition,
  is_active = EXCLUDED.is_active;

-- Create a helper function for invoice submission
-- This should be added to caseManagementHelpers.js:
/*
window.submitInvoiceToTaxCore = async function(record) {
  try {
    // Fetch full invoice details with line items
    const mgr = await getManager({ tables: { invoices: '/api/invoices' } });
    const invoice = await mgr.fetchOne('invoices', record.invoice_id);
    
    // Prepare data for TaxCore
    const invoiceData = {
      type: 'Normal',
      items: invoice.line_items.map(item => ({
        name: item.description,
        qty: item.quantity,
        price: item.unit_price,
        unit: item.unit || 'EA',
        labels: item.tax_labels || ['G']
      })),
      payments: invoice.payments || [],
      customer: {
        name: invoice.client_name,
        tin: invoice.client_tin
      },
      case_id: invoice.case_id,
      invoice_id: invoice.invoice_id
    };
    
    // Submit to TaxCore
    await window.submitToTaxCore(invoiceData);
    
    // Refresh the table
    window.dispatchEvent(new CustomEvent('refreshTable'));
  } catch (err) {
    console.error('Failed to submit invoice:', err);
    Toast.show('Failed to submit invoice to TaxCore', 'error');
  }
};
*/

-- Add case_id column to sales_receipts table if not exists
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'sales_receipts' AND column_name = 'case_id'
  ) THEN
    ALTER TABLE sales_receipts ADD COLUMN case_id INTEGER REFERENCES cases(case_id);
    CREATE INDEX idx_sales_receipts_case_id ON sales_receipts(case_id);
  END IF;
END $$;

-- Add invoice_id column to sales_receipts table if not exists
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'sales_receipts' AND column_name = 'invoice_id'
  ) THEN
    ALTER TABLE sales_receipts ADD COLUMN invoice_id INTEGER;
    CREATE INDEX idx_sales_receipts_invoice_id ON sales_receipts(invoice_id);
  END IF;
END $$;

-- Add case_id column to lineitems_payments table if not exists
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'lineitems_payments' AND column_name = 'case_id'
  ) THEN
    ALTER TABLE lineitems_payments ADD COLUMN case_id INTEGER REFERENCES cases(case_id);
    CREATE INDEX idx_lineitems_payments_case_id ON lineitems_payments(case_id);
  END IF;
END $$;

COMMENT ON COLUMN sales_receipts.case_id IS 'Links TaxCore transaction to a case in Case Management';
COMMENT ON COLUMN sales_receipts.invoice_id IS 'Links TaxCore transaction to an invoice';
COMMENT ON COLUMN lineitems_payments.case_id IS 'Links line items to a case in Case Management';
