-- Migration: Fix Quotation Credit Trigger
-- Date: 2025-10-20
-- Description: Fix trigger_update_credit_on_quotation_change to use created_by instead of updated_by
-- Issue: The quotations table has created_by column, not updated_by, which was causing SQL errors

BEGIN;

-- Fix the trigger function to use created_by instead of updated_by
CREATE OR REPLACE FUNCTION trigger_update_credit_on_quotation_change()
RETURNS TRIGGER AS $$
BEGIN
    -- Update credit utilization for the affected customer
    -- Note: quotations table uses created_by instead of updated_by
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        PERFORM update_credit_utilization(NEW.customer_id, NEW.org_id, NEW.created_by);
    ELSIF TG_OP = 'DELETE' THEN
        PERFORM update_credit_utilization(OLD.customer_id, OLD.org_id, OLD.created_by);
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION trigger_update_credit_on_quotation_change IS 
    'Trigger to update credit utilization when quotations change. Uses created_by field.';

COMMIT;

-- Verification
SELECT 'Migration 20251020_fix_quotation_credit_trigger completed successfully' AS status;
