-- ============================================================================
-- Workflow Event Triggers for Quotations and Invoices
-- ============================================================================
-- These triggers connect table operations (INSERT/UPDATE) to workflow events
-- so that the WorkflowEngine can automatically execute workflows when records
-- are created or updated.
-- ============================================================================

-- Quotations Table Trigger
-- Fires the 'quotations.updated' workflow event when quotations are inserted or updated
INSERT INTO workflow_event_triggers (
    event_name,
    table_name,
    trigger_type,
    operations,
    field_map,
    isdeleted
) 
SELECT 
    'quotations.updated',
    'quotations',
    'table',
    '["insert", "update"]'::json,
    '{"quotation_id": "quotation_id", "table": "=quotations"}'::json,
    false
WHERE NOT EXISTS (
    SELECT 1 FROM workflow_event_triggers 
    WHERE event_name = 'quotations.updated' AND table_name = 'quotations'
);

-- Invoices Table Trigger (legacy invoices table)
-- Fires the 'invoices.updated' workflow event when invoices are inserted or updated
INSERT INTO workflow_event_triggers (
    event_name,
    table_name,
    trigger_type,
    operations,
    field_map,
    isdeleted
)
SELECT 
    'invoices.updated',
    'invoices',
    'table',
    '["insert", "update"]'::json,
    '{"invoice_id": "invoice_id", "table": "=invoices"}'::json,
    false
WHERE NOT EXISTS (
    SELECT 1 FROM workflow_event_triggers 
    WHERE event_name = 'invoices.updated' AND table_name = 'invoices'
);

-- Generic Invoices Table Trigger (current invoices table)
-- Fires the 'invoices.updated' workflow event when generic_invoices are inserted or updated
INSERT INTO workflow_event_triggers (
    event_name,
    table_name,
    trigger_type,
    operations,
    field_map,
    isdeleted
)
SELECT 
    'invoices.updated',
    'generic_invoices',
    'table',
    '["insert", "update"]'::json,
    '{"invoice_id": "invoice_id", "table": "=generic_invoices"}'::json,
    false
WHERE NOT EXISTS (
    SELECT 1 FROM workflow_event_triggers 
    WHERE event_name = 'invoices.updated' AND table_name = 'generic_invoices'
);

-- ============================================================================
-- Verification Queries
-- ============================================================================

-- Verify the triggers were created
SELECT 
    id, 
    event_name, 
    table_name, 
    trigger_type,
    operations,
    field_map
FROM workflow_event_triggers 
WHERE table_name IN ('quotations', 'invoices', 'generic_invoices')
ORDER BY table_name;

-- ============================================================================
-- Notes
-- ============================================================================
-- 1. The field_map uses "=quotations", "=invoices", "=generic_invoices" syntax
--    The "=" prefix indicates a literal value (not a column name)
-- 2. This tells the workflow context which table triggered the event
-- 3. The QuotationsProcessor reads formData['table'] to determine processing logic
-- 4. These triggers work with:
--    - "Process Quotation Submission" workflow (quotations.updated)
--    - "Process Invoice Submission" workflow (invoices.updated)
-- 5. The triggers fire on both INSERT and UPDATE operations
-- ============================================================================

COMMENT ON TABLE workflow_event_triggers IS 'Workflow event triggers added for quotations and invoices tables';
