-- Shipment Customers
CREATE OR REPLACE FUNCTION log_shipment_customer_status_change()
RETURNS TRIGGER AS $$
BEGIN
    -- Only log if shipment_status_id actually changes
    IF NEW.shipment_status_id IS DISTINCT FROM OLD.shipment_status_id THEN
        INSERT INTO shipment_status_history (
            ref_id,
            table_name,
            from_state,
            to_state,
            changed_by,
            company_id,
            status_id
        ) VALUES (
            NEW.id,
            'shipment_customers',
            OLD.shipment_status_id,       
            NEW.shipment_status_id,      
            COALESCE(NEW.updated_by, NEW.created_by),              
            NEW.company_id,              
            1                             
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS log_shipment_customer_status_change_trigger ON shipment_customers;

CREATE TRIGGER log_shipment_customer_status_change_trigger
AFTER UPDATE OF shipment_status_id
ON shipment_customers
FOR EACH ROW
EXECUTE FUNCTION log_shipment_customer_status_change();